Re: Report Expressions for Grouping and Sorting Da...

AMR_Reception · 04-16-2020 12:31 AM

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!

Steve

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>>

Koichi_Tsuji

This is super awesome trick and finding Steve!

Daryl_Zernick

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

Steve

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:

Daryl_Zernick

Thank you Steve! You are the bestest!!

James_McFarlane

@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

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

Steve

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.

AMR_Reception

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!

Steve

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

AMR_Reception

Excuse the formatting mess

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.

AMR_Reception

Here is a screenshot of the information within the app

James_McFarlane

@Steve,

You rock! That did the trick thanks.

Cristian_O

@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

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...

Steve

You appear to be using ORDERBY() incorrectly.

ORDERBY() - AppSheet Help

Cristian_O

@Steve

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

Report Expressions for Grouping and Sorting Data

Call History: