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 :slight_smile: ) 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.

12 Likes

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! :smile:

1 Like

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:

1 Like

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

2 Likes

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 )

1 Like

how do I check the data for ref rows?

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

image

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.

1 Like

That’s it, it works :grinning: :grinning:

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.