Nested Expression Assistance - Grouping Data in Spreadsheet Template

AppSheet AppSheet Q&A
,
1

I have an automation that generates a report weekly, and it gathers the past 7 days of records from the “Seen by Provider” table. The current start expression of the template is

<<Start: Filter(Seen by Provider, [Seen Timestamp]>=(Today()-7))>>

this works but i am hoping to automatically sort the spreadsheet so that all records containing the same [_patientIdentifier] are grouped, whereas currently, they are just arranged by date automatically, it would seem.

I created a virtual column on the “Seen by Provider” table just to be able to use the expression assistant to craft some expressions and test to see if they yielded the results i want, but i can’t seem to get it right. I came up with

ORDERBY(
FILTER(
Seen by Provider,[Seen Timestamp]>=(TODAY()-7)
),
[_patientIdentifier]
)

I have not actually tried this with the automation, but the test results inside AppSheet are returning dates that are well beyond the 7 days, for some reason, even with the original expression that works just fine in the spreadsheet. i suppose i should try modifying the template and see what happens, but does my second expression look like it should order the results on the spreadsheet so that the patient encounters are grouped, mean that all “Seen by Provider” records for a single patient should be together?

2

Without further testing, try wraping your table name with "
I remember I did this but can’t recall when, I’ll take a look

1 Like
3

Another thing apart from good input by @SkrOYC on quotes you could do is wrap Timestamp with date() function such as DATE([Seen Tlmestamp])

1 Like
4 
<<Start: SELECT(Seen by Provider[_patientIdentifier], (DATE([Seen Timestamp]) >= (TODAY() - 7)), TRUE)>>
...patient info...
<<Start: FILTER("Seen by Provider", AND(([_THISROW-1].[_patientIdentifier] = [_patientIdentifier]), (DATE([Seen Timestamp]) >= (TODAY() - 7))))>>
...seen-by-provider info...
<<End>>
<<End>>
1 Like
5

Let’s talk through this.

The first start statement will return a list of _patientIdentifier values from the Seen by Provider table, where the Seen Timestamp is greater than or equal to today - 7 days.

The nested Start statement will filter the previous list from the Seen By Provider table (the _THISROW-1 means that it is looking at the previous context? still working on understanding that one), but i think i am getting confused here.

I “think” SELECT() is only returning the list of _patientIdentifiers, and the subsequent FILTER() is accessing the data contained in the rows referenced in the previous list, however I am not understanding how any ordering is coming from this.

You mind explaining this a bit further, please and thank you?

6

Maybe this helps

1 Like
7

UPDATE: I took a copy of my current template, then edited the start statement with this:

<<Start: 
ORDERBY(
FILTER(
Seen by Provider,Date([Seen Timestamp])>=(TODAY()-7)
),
[Patient Name],
FALSE)>>

and it worked as i was hoping.

2 Likes
8

And of course now they are asking for multiple sorts, so today i suppose i will find out if I can nest ORDERBY() statements to sort by time, name, and location…

1 Like
9

ORDERBY() - AppSheet Help

1 Like