Sample App for "Group By A Field" in Reports

At times, one needs to group the records in the report by a specific field. I have created a small sample app to create a 'group by a field" report.

Here is the app. Group By Reports

The app uses the data from the already available sample app called “Histogram”

While browsing the app in the editor, please take a look at following relevant details

  1. The VC “RefSize” .This Ref type VC classifies each of the records by assigning it the " group by field category" to which the record belongs to. In the sample app , the group by field is “Size” and the categories are “Small” ,“Medium”, “Big”
  1. This “RefSize” ref type field automatically creates a “related records” reverse reference list type field with all the grouped records being grouped in only one “related records” VC for each category of size (“Small” ,“Medium”, “Big”). Basically this rev reference columns creates grouped “child records” (within the same table ) for each category.

  2. There is a slice called “SizeGroup” . It selects only those records where the corresponding reverse reference column described in 2) above is non empty. In short, the slice selects only one record per size category with all associated grouped records for that category being “child records” in the reverese reference column.

  3. The report template in Behavior → Workflow options has

A) One outer loop based on the slice described in 3) above that prints only one “parent” record for each group category and works as a kind of “group title”

B) An inner loop created with all “child records” from the reverse reference column described above in 2) that print all group records for that particular size category.

  1. There is an action “Send GroupBy Report” in each record of the table that can be used to fire the report once you download the app.

The sample report looks like below where “Group by Size: Big” is the group by category outer loop and table is inner loop of records belonging to that group.

I had participated in a similar post thread mentioned below and thought that a simpler sample app based on that discussion could be useful and so the above post. Hope this is useful and helps someone.

19 39 5,167
39 REPLIES 39

This is ultra smart solutions!! I m impressed.
Thank you very much for sharing useful tricks, will add to my existing and incoming new apps!

For some use cases, we have separate Master (ref) table which is going to be used for GROUPING.
In that case, how would you recommend that we twist your sample apps? For instance, we create another table on your sample app, where we store sizes, “Smarll,Medium,Big”.

Hi @tsuji_koichi,

Thank you. You have a very valid and practical point. Do you have some example in mind for different table master? I was thinking orders and order details. But in that case, when an order is complete, all chidren have also complete status.Also in such cases , I believe the workflow template lists child records in its default expression creations?

Sure, hang on and give me bits, will revert after testing my thoughts etc.

Sure, thank you.

Hi @Suvrutt_Gurjar , would you have a screenshot of the Template expressions please?

@Martina,

Here is the report template expression.

It sounds that you were unable to browse the report attachement template because I had set the incorrect share permissions for the report template.I have set the share permissions now. I hope you will be able to browse the template now. Please let me know.

Great, thank you

Hi @Suvrutt_Gurjar, I tries to follow your instructions but I’m getting an error. Would you mind looking at it for me?

“Errors”: “Error: Workflow rule ‘Owned Equipment Inspection Report’ action ‘Action 1’ Attachment template. Expression ‘Equipment List[Related Equipment]<><<[Item Id]’ is invalid due to: Cannot compare List with Text in (EQUIPMENT LIST[Related Equipment] < [Item Id]). Error: Workflow rule ‘Owned Equipment Inspection Report’ action ‘Action 1’ Attachment template. Start expression ‘Equipment List[Related Equipment]<><<[Item Id]’ 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.”,

I have a table Equipment List
Item Id - Key
Description
Current Location
Last Inspected Date
… other fields

I want to create a report grouped by Current Location

I have a table called Inspection report
Id - Key
Date

Action button sets Date to now(). This is the trigger for the workflow so workflow is based on Inspection Report table.

I have added VC’s

(upload://1OVSQco1qQMoq38KAtf2N0brFGp.png)

and created a slice

Word Template:

@Martina,

At the first , could you please try to remove a syntactical error in the template expression and try running the workflow?

You have existing expression as
<<Start: Equipment List[Related Equipment]<><<[Item Id]>>
Could you please change it to
<<Start: Equipment List[Related Equipment]>><<[Item Id]>>

I believe the error

“Error: Workflow rule ‘Owned Equipment Inspection Report’ action ‘Action 1’ Attachment template. Expression ‘Equipment List[Related Equipment]<><<[Item Id]’ is invalid due to: Cannot compare List with Text in (EQUIPMENT LIST[Related Equipment] < [Item Id]).

is because of that syntactical error.

There may be more changes required and we may request more details from you , if above syntactical correcion still does not produce the desired results.

@Steve : May I request you to suitably change this recent thread of this post, based on queries by @Martina to “questions” section if feasible.

sorry, I have fixed the syntax errors. Now getting error:

“Errors”: “Error: Workflow rule ‘Owned Equipment Inspection Report’ action ‘Action 1’ Attachment template. Start expression ‘Equipment List[Related Equipment]’ 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.”,

Could you please verify if the column [Related Equipment] is containing a list of values of [Item IDs] which is key column in the editor’s test page?

This is how they are set up.

Maybe the issue is that Current Location is a Ref to Location.
Location table is a list of Locations to provide a drop down

Thank you. I believe location being itself a ref column may not be a problem,

As requested, were you able to check if you are getting list of key values in the column [Related Equipment] as in below example. Please do not paste pictures as you could have real data but could you confirm the REV_REF column and Slice have data existing on the pattern as shown below?

Also are you able to get some 'Y" in the slice expression? ( Typically, if there are 5 locations where equipment is there, you should get 5 Y values )

how do I check the data for ref rows?

If I view data for Equipment List following shows - test data so ok

2X_6_651d9e65450e1a921284fbe477fc1c121975b0b2.png

so Item ID ACC-001 is in Location 1 and Related Equipment shows the Item Id for all the other items in Location 1

Could you please try a start expression as follows

Instead of

<<Start: Equipment List[Related Equipment] >> <<[Item Id]>>

please try following

<<Start: [Related Equipment] >> <<[Item Id]>>

I will try that but I thought I needed Equipment List as the workflow is based on the Inspection Report table and not the Equipment List table

Sure, since your entire template expression is based on Equipment List table, I suggest you to please try it.

You have of course mentioned workflow is based on other table. If the above suggestion to use just [Equipment List] does not work , I believe we will look into aspect of the other table.

That’s it, it works

Thank you for such a great solution.

I hope the VC’s aren’t too taxing on the sync time when the App grows.

One more wee question. The groups are in Z-A order. How would I get them in A-Z order.

Good to know, finally the workflow runs as desired.

My responses-

Yes , that is possible. Any use of VCs over multiple rows could impact sync time. However in general reports are not supposed to be very long. I believe you may need to further optimize expressions so that VCs and reports run against smaller data in future.

Please try
<< Start: SORT((SELECT(Location Group [Item Id], TRUE))>>

This doesn’t work. I think it sorts the Item Id overall and then within each group.

I need <> at the end otherwise the output is just Location:

Location : <<Start: SORT((SELECT(Location Group[Item Id],TRUE))>><>

Trying to sort the Current location A-Z

Yes got it. Does following expression work

ORDERBY(SELECT(Location Group[Item Id],TRUE), [Current Location], FALSE) in place of

SELECT(Location Group[Item Id],TRUE)

Location : <<Start: ORDERBY(SELECT(Location Group[Item Id],TRUE),[Current Location],FALSE)>> This doesn’t print the Current Location.
Output is Location:
It does however sort A-Z

Thank you for all your help @Suvrutt_Gurjar.
The final template that now works:

Good to know the workflow works as desired.

Thank you.

Please have expression as follows

<<Start: ORDERBY(SELECT(Location Group[Item Id],TRUE),[Current Location],FALSE)>>
Location : <<[Current Location]>>
Then the template table follows as before.

In the sample app, if you observe, there is Size: [Size] above the template table and below the start expression
Edit: Added brackets around [Current Location] expression

Ok Thanks

Hi,

in the Virtual column u have added expression as below

INDEX((SELECT(Capybara[Key], [Size]=[_THISROW].[Size])),1)

i want to add one more condition in this expression, where i want to check and take data, when a particular column is blank,

how do i modify the above expression for this…

pls help @Suvrutt_Gurjar

Hi @DPM_reports,

Could you please share an expression that you might have worked out with little more description of your requirement?

i have not yet worked out any expression,

in your expression u have stated that when size = that row size, fire a report for all data where that stands true…

what i want is, in addition to this, i also want to include only that data where in my sheet a particular column is blank, if it has data the slice must not include it

So I believe you will need to include an AND() subexpression with that column which is blank along with size column.

Something like

INDEX((SELECT(Capybara[Key], AND([Size]=[_THISROW].[Size], ISBLANK([YourNewColumnName]) ))),1)

Edit: One parenthesis was missing in the expression.

This looks great @Suvrutt_Gurjar. Thank you for sharing.
I tried having a look at the sample app, but it wasn´t found. Did you put it down for some reason?
Thanks!

Hi @Nicolas_Feldman,

Yes, in my recent sample apps cleanup , the app has got removed from my portfolio.

However there has been a great tip to achieve similar results in much efficient way by @Marc_Dillon

Please explore

Pro_Nature
Participant I

The app is no longer visible…

Gowtham
Participant III

@Suvrutt_Gurjar Could you please share the App, it would be a great feature and I am planning to incorporate. The Application URL on this thread is not working.

Hi @Gowtham,

I will definitely reshare it. I request you that you please take a look at the approach mentioned by @Marc_Dillon in the tips and tricks section as mentioned in the post above.

I felt that his described approach is much straighforward than mine. If you still feel, I need to share, I will be glad to do the needful.

Please update the example link again.

Thanks

App The error message was not found. Please share the app stub again.
Thank you

@dohuuduy1985 , As mentioned in a previous post in the thread my sample app got removed in my app cleanup a few years ago. However there are similar and more efficient tips in the below tips to group report by a field. Please refer those.

Grouped list from one table - Google Cloud Community

 

Top Labels in this Space