Grouping in Reports

@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

@Nirmal_Giri,

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

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

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

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]))>>
2 Likes

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

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

2 Likes

@Steve Thank you for replying.

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

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

2 Likes

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

1 Like

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

1 Like

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.

1 Like

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:

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

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…

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

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?

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

Column list:

Quotation Details table

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

Customer Details Table

This may help.

4 Likes