Report Expressions for Grouping and Sorting Data


I am trying to create a report which sorts the data by certain columns, I am happy to share my app with anyone who is willing to help but I have been working on this for days and am going a bit :peanuts:

It is an app to be used to collect information from real estate agents about their upcoming, current and future properties. I have set the app up so it is all in one table, the agent makes a new entry for each property and the type selected jumps them to the relevant questions. Would this be better set up in a different way?

With the report I want to group and sort the data first by agent, then property type (e.g. upcoming, current, sold, future).

I have been experimenting with the start and select expressions but just can’t figure it out!

Looking forward to getting some answers!

I’d posted a reply yesterday (since deleted) saying:

As I understand it (I don’t work with reports much), there’s no automatic way to group data in reports: you’d have to do it manually.

But as I though about it, I couldn’t get passed the feeling there is a way to do it. After some tinkering, I found it! It’s not trivial–in fact, I’d call it expert-level stuff.

This is the <<Start>> tag to group by agent:

<<Start: ORDERBY(FILTER("All Data", ([Row Key] = MAXROW("All Data", "_ROWNUMBER", ([_THISROW-1].[Agent] = [Agent])))), [Agent])>>

This is the <<Start>> tag to group by property type:

<<Start: ORDERBY(FILTER("All Data", AND(([_THISROW-1].[Agent] = [Agent]), ([Row Key] = MAXROW("All Data", "_ROWNUMBER", AND(([_THISROW-1].[Property Type] = [Property Type]), ([_THISROW-1].[Agent] = [Agent])))))), [Property Type])>>

In each, replace All Data with the name of your table, Row Key with the name of the table’s key column, and Agent and Property Type with the column names you use for them.

Very roughly, your template will look something like this:

<<Start: agent...>>
<<Start: property type...>>

Steve, you are a legend! Thank you!
I also feel better that this is expert level stuff :wink:

I have entered the code in and this is the result, on the right track but not 100% there as yet:

As you can see some entries are doubling up and some are not being shown at all. I have tried fiddling with the above expressions, once again to no avail!

Thank you again for your help and I look forward to hearing your thoughts!

1 Like

Here is a screenshot of the information within the app :slight_smile:

1 Like

Please post a screenshot of your template showing your version of the expression I gave you.

Excuse the formatting mess :wink:

This is super awesome trick and finding Steve!


Your second <<Start>> tag doesn’t appear to match the format I gave. Please review what I offered and make sure you use something similar.

Hello, Steve,

I hope you can help. My company is on the frontline of COVID-19 testing and monitoring. One of the services we are providing is monitoring patients at home and then making return to work decisions for them based on several factors. On a nightly basis we are sending a summary report of our interactions back to the client. I would like to have this report sorted alphabetically by last name then by first name. There are two tables that I am utilizing, one for top level ID information (IDKey) and another ref table for the daily calls (Master). I am curious if you could tell how to do the sorting. Below is how the report is currently formatted:

<<Start: Select(IDKey[ID], TRUE)>>

Employee: <<[LastName]>>, <<[FirstName]>> Recommended to isolate until: <<[Isolation Date]>>

Isolation details: <<[Isolation Time]>>

RTW Decision: <<[RTW Decision]>> Date of RTW Decision: <<[RTW Decision Date]>>

Birthdate: <<[Birthdate]>> Gender: <<[Gender]>> Phone: <<[Phone]>> Email: <<[Email]>>

Job Title: <<[Job Title]>> Location: <<[Location]>> Supervisor: <<[Supervisor]>>

Work schedule: <<[Work schedule]>> Last day worked: <<[Last day worked]>>

Was mask worn entire shift of last day?: <<[Was mask worn entire shift of last day?]>>

High health risk conditions: <<[High health risk conditions]>>

Date of first exposure: <<[Date of first exposure]>> Date of onset of symptoms: <<[Date of onset of symptoms]>> Date Quarantined: <<[Date Quarantined]>>

COVID test date: <<[COVID Test Date]>> COVID test result: <<[COVID test result]>>

Call History:

Timestamp Exposure Symptoms Referred
<<Start: [Related Masters [ID]]>><<[Timestamp]>>, <<[Call Status]>> <<[Exposure]>>, <<[Date of Exposure]>>, <<[Explain Exposure]>> <<[Symptoms]>> <<[Referred to PCP]>><>


Your help is very graciously appreciated!!!

Warm regards,


Replace this:

<<Start: Select(IDKey[ID], TRUE)>>

with this:

<<Start: ORDERBY(IDKey[ID], [LastName], FALSE, [FirstName], FALSE)>>

And replace this:

<<Start: [Related Masters [ID]]>>

with this:

<<Start: ORDERBY([Related Masters [ID]], [Timestamp], FALSE)>>

See also:

1 Like

Thank you Steve! You are the bestest!!

1 Like