Grouping in Reports

[Nirmal_Giri]

@Suvrutt_Gurjar

I am working on generating quotation for customer for multiple equipment.
I have a parent table ‘Customer Details’ and child table ‘Quotation Details’.A customer can have multiple quotation details. Each row in Quotation details has columns such as [Part Code],[Equipment_Name],[List Price],[Qty] etc.

I am trying to generate a report for a customer with items from child table grouped by [Equipment_Name]. Something like this:

Expected Report Format

I read your post on Group By Reports and tried the same on my tables with changes. Your sample app works with one table and slice. Here, I am trying to generate report from parent table (Customer Details) and want to group the records by [Equipment_Name] from child table (Quotation Details). As suggested I have added the following VC to child table and slice:
VC [_RefEquipmentName]
INDEX((SELECT(Quotation Details[Row ID], [Equipment_Name]=[_THISROW].[Equipment_Name])),1)

VC[Related Equipment]
REF_ROWS(“Quotation Details”, “_RefEquipmentName”)

[SliceByEquipmentName]
IN([Row ID], SELECT(Quotation Details[Row ID], COUNT([Related Equipment])>0))

When I use the slice for outer loop, my entire table is repeated for as many times I have unique equipment name. Below is the image:

Report generated

Report Template

Could you help me out?

Thank you

[Suvrutt_Gurjar]

@Nirmal_Giri,

Could you please update what is the key column in Quotation Details Table?

[Nirmal_Giri]

The key column for Quotation Details is [Row ID].

[Suvrutt_Gurjar]

Could you elaborate the below please? Is it repeating for all customers?

Nirmal_Giri:

When I use the slice for outer loop, my entire table is repeated for as many times I have unique equipment name.

[Steve]

Replace your first <<Start>> tag with:

<<Start: FILTER("Quotation Details", ([_ROWNUMBER] = MIN(SELECT(Quotation Details[_ROWNMUMBER], ([_THISROW-1].[Equipment_Name] = [Equipment_Name])))))>>

And your second <<Start>> tag with:

<<Start: FILTER("Quotation Details", ([_THISROW-1].[Equipment_Name] = [Equipment_Name]))>>

[Nirmal_Giri]

Steve:

And your second <<Start>> tag with:

<<Start: FILTER("Quotation Details", ([_THISROW-1].[Equipment_Name] = [Equipment_Name]))>>

Could you please clarify what does the [_THISROW-1] refer to?

[Steve]

https://help.appsheet.com/en/?q=_thisrow-1

[Nirmal_Giri]

@Steve Thank you for replying.

The parent table does not have [Equipment_Name] column. Hence, the first Start expression will throw an error.

[Steve]

It doesn’t refer to the parent table, it refers to the outer FILTER() expression.

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

[Nirmal_Giri]

Thank you for the clarification. I had read the article and completely misunderstood it.
I will attempt the Start expression again.

[Nirmal_Giri]

@Steve I am getting this error:

Failed: Action not performed because 2 errors are present. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘FILTER(“Quotation Details”, ([_RowNumber] = MIN(SELECT(Quotation Details[_RowNumber],([_THISROW].[Equipment_Name] = [Equipment_Name])))))’ is invalid due to: Error in expression ‘[Row ID].[Equipment_Name]’ : Unable to find column ‘Equipment_Name’. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Start expression ‘FILTER(“Quotation Details”, ([_RowNumber] = MIN(SELECT(Quotation Details[_RowNumber],([_THISROW].[Equipment_Name] = [Equipment_Name])))))’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table…

[Steve]

Whoops! There should be a -1 in there, too:

<<Start: FILTER("Quotation Details", ([_ROWNUMBER] = MIN(SELECT(Quotation Details[_ROWNMUMBER], ([_THISROW-1].[Equipment_Name] = [Equipment_Name])))))>>

I’ve also updated the snippet in my original suggestion, too.

[Nirmal_Giri]

Got another error

Failed: Action not performed because 2 errors are present. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘End’ is invalid due to: Expression refers to undefined field. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘Start: FILTER(“Quotation Details”, ([_THISROW-1].[Equipment_Name] = [_THISROW].[Equipment_Name]))’ is invalid due to: Error in expression ‘[Row ID].[Equipment_Name]’ : Unable to find column ‘Equipment_Name’…

Where do I place the End expression for Outer loop. This is current template:

[Steve]

Actually I think the first <<Start>> tag goes inside the Part Code cell.

[Nirmal_Giri]

I am getting error messages stating that - cannot find column Equipment_Name.
When I run the same expression of the test editor, i get results but the workflow throws error.

Failed: Action not performed because 3 errors are present. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘[Equipment_Name]’ is invalid due to: Unable to find column ‘Equipment_Name’. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘FILTER(“Quotation Details”, ([_THISROW-1].[Equipment_Name] = [Equipment_Name]))’ is invalid due to: Error in expression ‘[Row ID].[Equipment_Name]’ : Unable to find column ‘Equipment_Name’. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Start expression ‘FILTER(“Quotation Details”, ([_THISROW-1].[Equipment_Name] = [Equipment_Name]))’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table…

[Steve]

Lets try to narrow down which FILTER() expression is the problem. For testing, please replace the second <<Start>> tag with:

<<Start: FILTER("Quotation Details", TRUE)>>

[Nirmal_Giri]

I replaced the second <> expression and got the following error message:

Failed: Action not performed because 1 errors are present. Error: Workflow rule ‘Create draft quotation (pdf)’ action ‘Action 1’ Attachment template. Expression ‘[Equipment_Name]’ is invalid due to: Unable to find column ‘Equipment_Name’…

For the below, did you refer to Part Code header cell or Part Code value cell?

Steve:

Actually I think the first <<Start>> tag goes inside the Part Code cell.

[Steve]

Please provide a screenshot of the entire column list of the Quotation Details table.

[Nirmal_Giri]

Column list:

Quotation Details table

[Row ID] is the key column. [Qtn_No] references Customer Details table.

Customer Details Table

[potiporaupl]

Hi Steve,
Following your example I was able to group the data... but I would also like to filter by period ([Start_Date] and [End_Date]).
Is this possible? And what would the expression be?

Thanks in advance for your help!

[Marc_Dillon · Accepted solution]

This may help.

Single category headers in workflow templates for multiple records of same category Tips & Tricks 💡

Say you have data like this: [image] With “Group by” selection in a Table view, you can easily display this in the app like so: [image] But what if you want to do so in a PDF file generated via a START expression in a workflow? Use the following general model: [image] Which basically says “if this is the first item in this category, display a header”. The tricky part is when using a subset of values in a table, like child values of the parent record you’re working with. Then for the seco…

[Suvrutt_Gurjar]

A nice, compact solution @Marc_Dillon.

@Nirmal_Giri: I believe @Marc_Dillon’s solution is much easier to implement. You may wish to explore that instead of the approach mentioned in my sample app.

[Nirmal_Giri]

I got the report working.
Thank you for help. @Suvrutt_Gurjar @Steve

[Suvrutt_Gurjar]

Hi @Nirmal_Giri,

Thanks for the update. Great to know that you got it working with @Steve’s guidance. Frankly I did not add any contribution in your query in this entire post thread.

I may however request you to share your final working template if possible so that anyone having a similar requirement in future can utilize that. Of course, please hide senstive field names, data values etc. as you have already done in previously shared images.

[Nirmal_Giri]

I realized too late on the data values and field names… Thank you for reminding.
I will share the final template and how it looks like, and edit the earlier images too.

[Nirmal_Giri]

Had to delete the previous posts due the security reasons.
Reposting the final working report template on how to group data in reports. Thank you @Marc_Dillon

Image on left is the template and that on right is the output.

[Marc_Dillon]

[Teoishiguro]

Hi,

I’ve been reading this thread for a few days now and got my table to group correctly but the way it is setup results to the columns not being aligned. If I put the start and end tags in the table it produces an error where it cant read the column names i put in the cells.

would appreciate any help on this…

Thanks!

[Steve]

Teoishiguro:

If I put the start and end tags in the table it produces an error where it cant read the column names i put in the cells.

Please post screenshots of both the error and your template.

[Teoishiguro]

hi Steve,

This is the section of the template I am working on:

[Teoishiguro]

here is the error I get:

[Marc_Dillon]

Is the template you posted the one where the START is inside of the table, and returns that error? What does the other template look like, and what does the outputted file look like?

[Teoishiguro]

Hi Marc,

Yes, the one I posted is when the START is inside the table…

If I use the template where the start is outside the table, it looks like this:

[Marc_Dillon]

Yep, looks familiar. I don’t believe there is any way to get the columns from table to table to line up exactly, but play around with manually setting a value for each column’s width. I’ve also gotten around it before by getting rid of the cell borders so the “jitter” is not so obvious.

[Teoishiguro]

Hi @Marc_Dillon,

I would assume that the column width there applies to all columns currently on the table?

It is unfortunate to know that it does not align correctly… but appreciate all the help! Thanks

[Marc_Dillon]

You should specify a column width for EACH column, by right clicking from any cell in that column.

[Teoishiguro]

Hi @Marc_Dillon will try that! Thanks for your help