SCHEDULED BOT EMAIL REPORT - ALL PARENT RECORDS WITH THEIR RELATED CHILDREN

[Aadam]

THE FOLLOWING IS A SCHEDULED BOT WHICH IS SUPPOSE TO GIVE ME ALL THE PATIENTS FOR THE WEEK, BUT ALSO I WANT ALL THE PATIENTS RELATED DAILY STATS

“PATIENTS TABLE” (PARENT TABLE - KEY = [UNIQUE ID], IT ALSO HAS A REF TO THE “STATS” TABLE which = [STATUS DATE HELP]

“STATS” (CHILD TABLE - KEY = [STATS UNIQUE ID]), it has a REF to the PATIENTS TABLE which = [PATIENT ID]

I have not selected “FOR EACH ROW IN TABLE” in Bot because it gives me repetitive emails, I just want one email with the list of patients and their related stats, I am struggling to tie the related stats to the Patient with a scheduled bot.

I am struggling to get all of the related child records (stats) to the parent (patient), if I could use a _THISROW I would have it working, but because its a schedule I cant seem to group them together…

Attached screenshots explains a bit better

[Suvrutt_Gurjar]

You may want to refer the following sample app to get ideas. You may not get the exact set tabular setup as you have shown but you will get ideas in creating outer > loop with the parent table records. Inside it will be the child table > loop.

Please take a look at the template of the bot “CustomerUpdateRule”

The outer > loop highlighted in yellow is on the parent table Orders and the inner loop on the child table Order Details is highlighted in red.

Now you could have a tabular structure for the child table records as the bot template of “OrderUpdateRule” shows.

Order Capture How-to - AppSheet

In general, the report templates are a bit tricky to create and you may need some experimentation, taking a look at several posts and tips and tricks section in the community. You may still not get exactly what you are looking for but may come close to your intended format.

[Aadam]

Thank you @Suvrutt_Gurjar that was the solution. I have spent hours trial and error changing expressions, then setting the time, save, wait the minute and see if it fires.

Here is the working solution.

How can I add a between dates filter;

Patients from the last seven days (week), and their related stats for the past 7 days;

I’ve tried the following;

<<<START:SELECT(PATIENTS[Related STATSs],AND([DATE]=<TODAY(),[DATE]>=(TODAY()-7)))>>

<<<Start:AND([Related STATSs],[DATE]=<TODAY(),[DATE]>=(TODAY()-7)>>

<<<Start:ORDERBY(SELECT(PATIENTS[UNIQUE ID],AND([STATUS DATE HELP].[DATE]=<TODAY(),[STATUS DATE HELP].[DATE]>=(TODAY()-7))),[NAME],FALSE)>>

I try and work out the formula in a virtual column first to see if the formula is working before copying it over to the template.

[LegoLow7]

This is super helpful, thanks for sharing!

[Aadam]

@Suvrutt_Gurjar I also looked at this link

https://www.googlecloudcommunity.com/gc/forums/v5/forumtopicpage.inlinemessageediteditor:rerendermessageview?t:ac=board-id/appsheet-questions/thread-id/261550&t:cp=messages/contributions/messageeditorscontributionpage

But because this is a scheduled automation I don’t see how to incorporate _THISROW as the automation is retrieving from the whole table without a reference row.

[Suvrutt_Gurjar]

For the patients in the last 7 days, please create a slice called say “Patients_LastWeek” in the parent Patients table with a slice filter expression something like

[Date Column in the Patients Table]>= TODAY() -7

Please use this slice in the outer <> expression in place of existing expression, something like

<<ORDERBY(Patients_LastWeek[UNIQUE ID],[Name])>>

For the related records , please try a <> expression something like

<<SELECT([Related STATSs][STATS Table Key column], [Date]>=TODAY()-7)>>

[Aadam]

THANK YOU SO MUCH FOR YOU INPUT @Suvrutt_Gurjar , THIS HELPS ALOT!!!

[Suvrutt_Gurjar]

You are welcome.