Trying to generate a list on a slice with distinct values

[Rhage]

I have a table which holds a lot of data. I'd like to create a list of distinct values for my bot, however bot only allows tables/slices. 

So to shorten things up I'd like to filter my entire table into a slice with this 2 conditions:
1. status is equals to pending
2. responsiblepersonid is unique (this id is a reference to the users list as to why it can be used on multiple items on the table)

my end goal is to have a slice of unique responsiblepersonid of those who are still on pending for me to use on a reminder bot

[Suvrutt_Gurjar]

Based on your description, it sounds that 

1. You could create a slice on the table with [Status]="Pending"

2. Whatever records the slice will generate,  you could group them by the responsible person ID using the group by technique tip by @AleksiAlkio 

Grouped list from one table - Google Cloud Community

[Rhage]

Hi @Suvrutt_Gurjar I get the idea of the technique by @AleksiAlkio, however what I really am after is to just a list down the responsiblepersonid so that I can use it as a foreachrowintable for my email reminders. The email is not a send to all email, they are personalized per each responsiblepersonid. 

Is there any other way to do a second filter after I have filtered the slice? 

[Suvrutt_Gurjar · Accepted solution]

Yes, in general you should be able to proceed by some experimentation based on the brief details you have provided and based on  that what we have suggested. 

If you are looking for more precise inputs , please share more precise details. Please share the table structure you have ( only relevant columns) and what the email should look like out of those columns. Please ensure to use pseudo data while showing the example of table columns and email template content. Also please mention if you are looking for a scheduled or data change bot.

Edit: 

---

---

@Rhage wrote:

Is there any other way to do a second filter after I have filtered the slice? 

---

In general, for the second condition of one row per each responsible person, you could try a slice expression of 

[Key column]=MINROW("Table Name","_RowNumber", AND([responsiblepersonid]=[_THISROW].[responsiblepersonid],[Status]="Pending"))

This filter expression should take care of both conditions -pending status and one record per each responsiblepersonid. 

MINROW() - AppSheet Help

[AleksiAlkio]

Sorry Suvrutt, I didn't see your reply before I wrote mine. The good thing is that the reply was the same.. kind of 🙂

[Suvrutt_Gurjar]

Hi @AleksiAlkio ,

I initially asked @Rhage for more details because I wanted to know if he wants to include all the rows also for the individual users in his email. Later I thought let me at least share the slice expression.

I think our replies crossed each other as I think we both replied around the same time. 

You of course need not be sorry at all. I feel great that our replies are on similar lines. Whenever our approach is on similar lines to yours or @Steve 's , we are ensured that we are on right track.👍

There is always so much to learn from you and @Steve on precise , compact solutions.

[AleksiAlkio]

If you would have a table for users, this would be simple. But it sounds you would like to do that directly from your table filtering it in a way so it would have just one row for one user where the status is pending. For this purpose you would need to have one virtual column. The formula would be something like..
[KeyColumn]=MAXROW("Table","Timestamp",AND([Status]="Pending",[User]=[_THISROW].[User]))

With this result, you can add a condition rule to your Bot/Event like [Reminder]=TRUE and it will trigger the Bot only from one row per user.. and no slices are needed. Though if you have a lot of rows, it will help with the calculation when the Bot is triggered.

[Rhage]

I am trying to figure out the slice to have an output like this sample query

[Suvrutt_Gurjar]

Yes, the approach suggested by us will give you those records- one record per user or per responsiblepersonid.

From that slice, you can select the desired columns you wish to include in your email template.

[AleksiAlkio]

The formula I showed, should work with the slice's condition as well because the result is Y/N. Then you could trigger the Bot from the slice without any condition.

[Rhage]

Thanks @Suvrutt_Gurjar and @AleksiAlkio it worked! 

[AleksiAlkio]

Excellent!