[Filter content] Only send and get the data from a...

Iggy_Villanueva · 08-05-2021 07:45 PM

Hi,

We are trying to send a weekly email report only for the entry of each requestor/recipient of the mail,

right now we are stuck as the whole summary of the table were being sent.

Thanks for the inputs.

WillowMobileSys

The “Start:” statement within your template table has a FILTER() function but it’s not filtering anything. The second value of “TRUE” in the function is basically saying that ALL rows should be included.

We need to know how your automation has been implemented. Since you are sending a “weekly email report”, I’ll assume you are using a Scheduled automation bot. Is this running “For each row in the table” or not?

For your filtering, its not clear to me how you get from your shown data to the statement above. That might partly be due to the text is cutoff on the right-hand side of the included image. Also, is the example Assessment Status a bad example or the expected result?

Oh, with some records last updated in 1899, kudos to you for having an app around long before its time.

Steve

I suggest a FILTER() expression like that in my examples when I don’t know the key column’s name (or even if I do). It could well be their intention to include all rows.

WillowMobileSys

In this case, based on the above op’s statement, it seems the complaint is that all rows from the table are bing included, implying thats not what they want.

Steve

Sorry, didn’t read the OP.

Iggy_Villanueva

Hi, Guys!

Thank you. Please don’t mind the last date updated, this was a late column addition and we haven’t updated all rows haha!

anyway, im playing around this formula, it works just fine but not consistent, from 5 test run it returns the desired result 2/5.

Also, the intention is to send this every friday, but we have the “To” function for testing only, plan to replace it with the “initiator” column come prod

formula update:
<<Start: ORDERBY(Filter(“P3 Demands”, (USEREMAIL() = [Initiator])), true)>><<[Demand ID]>>

Iggy_Villanueva

Update: Been able to filter per Useremail() from the Initiator column

<<Start: (Filter(“P3 Demands”, ([Initiator] = USEREMAIL())), true)>><<[Demand ID]>>

But the notif workflow only works when we click the “Test” icon, it returns blank on the “Run” and on the scheduled reporting.

“Test” - only returns the data of the useremail() who execute the flow

I hope you have further recommendations, Thank you!

WillowMobileSys

I think what you are now running into is how (i.e. under which user) the automation runs on the server side. Automation processes ALWAYS run under the App Creator account. This means that you can’t test emails against the USEREMAIL() function.

If you think about it for a second it makes sense - especially for Scheduled processes.

This brings me back to the FILTER() expression and trying to understand the relationships between the records to understand how you really need to filter. Using USEREMAIL() doesn’t make sense and simply won’t work for a Scheduled automation process.

So let’s back up a bit.

When the process runs every Friday, what records do you expect to show up on the report?

Try to describe that based on your Raw Data File shown in the original post and WITHOUT the usage of the USEREMAIL() function.

Iggy_Villanueva

Hi, Thank you for the insights. Taking a step back a bit. 1. a table named P3Demands has column of initiator corresponding to the email of the owner of that entry 2. The data on the table is updated as necessary (status, remarks, etc) 2. On weekly report every friday, we want to send an update to each of the initiator column 3. Using Filter and Useremail: We are trying to customize so as the entry of the recipient will be the one to be showed in the report. 4. So everyone gets a weekly report but it varies as they will only see their own entry and not see update of other initiators
I hope this helps. I hope this helps.

It is like, making a bot to send update and filter data based on the recipient’s entry

WillowMobileSys

Just to paraphrase…You wish to generate a report each Friday to each Initiator individually, showing that initiator their specific updates - and only their updates - over the past week. Am I close?

IMPORTANT - when a Scheduled bot runs, it runs on the server. There is no logged in user. The bot ALWAYS runs under the App Creator account. So you cannot use the USEREMAIL() function in this context.

Because you want to generate a separate email to each Initiator, you must use the “ForEachRowInTable” switch. This will cause the bot to run the task of generating an email for each row sent through the bot.

The biggest hurdle is setting the bot Filter Criteria to get rows with the unique list of Initiators with updates over the last week to run through the bot. This would be easiest if you had a Users table that you iterated over. Then your Filter expression could choose those Users listed as an Initiator over the past week.

Do you have a Users table in your app?

Otherwise, the expression will need to pull some single row for each of the Initiators out of the P3Demand table to use a representative row sent to the template. This will be more complex and I am not exactly certain of the implementation, or even if it can be built at all in this context. I would need to try it out on some example data first.

Maybe others have ideas to help?

Iggy_Villanueva

Hi,

Thank you for confirming the useremail() function will not work for this application.

Do you have a Users table in your app?

Are u refering to this tab “Account” as shown below? this is actually used for User account management and Yes, the Email from “Initiator” column here are the one who do the actual entry to have data on the “P3 demand” sheet.

We are thinking to have a slice for each row,

e.g. 30 rows or 30 initiators = 30 slice but this is not ideal especially if we try to add more users in the future.

this sounds like this idea

“Because you want to generate a separate email to each Initiator, you must use the “ForEachRowInTable” switch. This will cause the bot to run the task of generating an email for each row sent through the bot.”

WillowMobileSys

Yes!! This is perfect and should be the table you use to identify WHO gets the emails.

I would recommend a bot that runs against your Account table. The Filter Condition for the bot would be:

AND(
         [Role] = "Initiator", 
         IN([Email], SELECT(P3 Demands[Initiator], [Updated Date] > TODAY() -7, TRUE)
)

The above expression will filter the Account user rows to those Initiators that had made an update over the past week. (I think that’s what you wanted - updates over last week). The “ForEachRowInTable” switch will then send each Account row - i.e. each Initiator row who had changes the past week - to the rest of your bot that generates the email.

In your email template, you can now adjust the report table START expression to this (not certain which date column to use but you can adjust as needed):

<<Start: FILTER(“P3 Demands”, 
                           AND(
                                    [Initiator] = [_THISROW].[Email], 
                                    [Updated Date] > TODAY() - 7
                            )
               )>><<[Demand ID]>>

Note that any reference to [_THISROW] will refer to the Account row that is passed to the template.
Also note, since you will running on every Friday we can deduce the past Friday by TODAY() - 7.

I hope this helps!

Iggy_Villanueva

We are about to explore your recommendations. Thank you.

Just a quick one, would this work even if there are no changes or editing of fields or no updates have been done.

The bot would be designed to send weekly status report, meaning a summary of all entry from each of the initiator in case there would be movement for 1 month, the idea is to receive the same table/report for 4 weeks.

Anyway, thank you so much and we will update, today.

WillowMobileSys

Yes. the suggestion I made was to identify Initiators that had changes over the past week. But the Bot Filter criteria can be adjusted for whatever you need - Maybe you want Initiators with changes over the past month, OR maybe you want to send an email report to ALL Initiators regardless if they had changes or not. Which Initiators you send the report to would be selected by the bot Filter criteria.

Once you have selected the list of Initiators, they will be sent one-by-one to the template to generate the report that is sent by email. In this template, you would adjust the Start Filter expression to select which rows to report on for the individual Initiator.

Many times the bot Filter Criteria and the Start expression in the template will be very similar…but not always.

Give it a try and post if you have more questions.

Iggy_Villanueva

Hi,

We tested this one and still received an unfiltered table, you may see the first column.

WillowMobileSys

Start expression looks good. To help further we need to see 2 things:

How the bot event is configured.
Examples of the raw data you are expecting to show up on the report.

[Filter content] Only send and get the data from a specified email of a column