Report Expressions for Grouping and Sorting Data

Hi!

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

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!

0 16 2,551
16 REPLIES 16

Steve
Participant V

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: ...by agent...>>
<<Start: ...by property type...>>
...property...
<<End>>
<<End>>

This is super awesome trick and finding Steve!

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,

Daryl

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:

Thank you Steve! You are the bestest!!

@Steve , @Aleksi

I need to group inventory items by cubicules in a report. I tried the suggested formulas but do not get the desired result. Have an inventory with the following structure:

With the following template:

I get this result. I have to split the table in the template

With this template:

I get this result. Only get one item per cubicule and it should be several.

With this template this template the row header repeat for each item
3X_8_9_891d15712d778fd02a18f36c4bd3d3012a413930.png

Get this result row header repeat for each row for items that are in the same Cubicule:

The inner <<Start>> tag should occur in the first data (non-header) cell of the table; the <<End>> tag should occur in the last cell of that same row of the table.

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

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!

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

Excuse the formatting mess

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.

Here is a screenshot of the information within the app

@Steve,

You rock! That did the trick thanks.

@Steve  Buenas tarde, estuve revisando el post aplique varios opciones con ORDERBY Sin tener resultado por esto es que requiero de tu ayuda.

Tengo un reporte pdf, encuesta y esa encuesta(tabla padre) tiene varias exhibiciones(tabla hijo) y en tabla Exhibiciones , tengo columna ID_Proveedor(REF) a proveedores,  la cual quiero ordenar por proveedor y no consigo resultado

orderby.jpg

Utilice:

<<START: ORDERBY(FILTER("EXHIBICIONES", [ID_ENCUESTA]=[_THISROW].[ID_ENCUESTA]), True)>><<[ID_Proveedor].[Nombre Proveedor]>><<END>>

Trae todos los proveedor de esa encuesta en misma columna de cada columna de exhibicion que se agrega y no separado y ordenados. por columna

que estoy haciendo mal, espero haber explicado bien...

You appear to be using ORDERBY() incorrectly.

ORDERBY() - AppSheet Help

@Steve 

despues de tanto, lo logre cambie el Start de la 1er columna a la 2da de porveedor y ordene por proveedor. Gracias!!

Top Labels in this Space