Help with Workflow Template Expression

AppSheet AppSheet Q&A
1

Hi.

I have a workflow template that I really need help to format in a certain way but can’t get the expressions correct.

So the SELECT expression in the template extracts each row for [Product Code] which is the key value. With my current expression the template is being replicated for each product code but there are duplicate values associated with a [Product Code] that I only want to extract once. e.g. of my dataset
ROW1=[Product Code 1]=A1234, [Retention]=145, [Charge Number]=41795
ROW2=[Product Code 2]=B1234, [Retention]=145, [Charge Number]=41795
ROW1=[Product Code 3]=C1234, [Retention]=145, [Charge Number]=41795

There could be 50 different [Product Codes] associated with the same [Charge Number] and [Retention] value (among others).

When I generate the template I would like to display the [Charge Number] and [Retention] values etc… once and the list of associated [Product Code]'s with that [Charge Number]/[Retention] etc…

Example of intended template:
Charge Number, Retention etc…
Product Code 1
Product Code 2
Product Code 3

Instead of
Charge Number
Retention
Product Code 1

Same Charge Number
Same Retention Value
Product Code 2

Same Charge Number
Same Retention Value
Product Code 3

The template itself is being generated from a Search Form where the user enters a [Date] and the expression in the template matches ALL [Charge Number]'s that have that corresponding date so my template will contain multiple different charge numbers which is fine but I would like to present them as per above in order of Charge Number.

I have attached an image of my template file and a working example hoping that might explain my request a little better.

Happy to share the .doc file in question if that would help with solving the issue.

Thank you so much.

2X_3_3cc9d0a3ddbc407852e447644c18bfaf49fb4967.png
2X_3_3cc9d0a3ddbc407852e447644c18bfaf49fb4967.png1366×768 89 KB

2X_d_d454e2e092af9986c5ca6d6d4d9a7c3d426bc24d.png
2X_d_d454e2e092af9986c5ca6d6d4d9a7c3d426bc24d.png1366×768 91.3 KB

2

Hi @MauriceWhelan,

I have put together a sample app based on column names etc that you have mentioned for ease of browsing. I believe the test app achieves the “Group By” functionality in report that you are looking at.

Please take a look at the three VCs, two slices and the report template in workflow pane created in the app to achieve the functionality. The report is run by tapping the action button “Send Report” in any record’sdetail view. Hope this helps.

Please copy the app " GroupBy Report" in the portfolio at
SG Portfolio

The reports produced by the sample app look like below

2X_7_72d3e2f431c65ac81865c4ca512d408a0741b067.png
2X_7_72d3e2f431c65ac81865c4ca512d408a0741b067.png620×648 10.6 KB

5 Likes
3

Hi @Suvrutt_Gurjar. Thank you so much for such a response. I have reviewed your sample app and it looks very close to what I need. If possible could I just ask for a brieft explanation of how you put it together. I am still relatively new to AppSheet platform and am eager to understand as much as I can so I can use that to build out more complex functionality.

Thanks again.

4

Hi @MauriceWhelan,

Thank you. Good to know that the sample app is on lines with your requirements. I will be glad to add the description. It is as follows

VC [RetentionCharge] : Concatentation of columns[Retention] and [Charge Numbers]. We need to group the [Product Code] by [Retention] and[Charge Numbers]. Hence this concatenation.

VC [CommonProdIDs] : This VC selects and groups the the records that have [RetentionCharge] in common. It thus uses the first VC [RetentionCharge] to get the desired grouping.

VC [SelffRef] : We have now grouped the records by [RetentionCharge] combination with column[CommonProdIDs] having IDs of grouped records. However we need to display the so called master record for this grouping only once per group. This [SelffRef] VC creates a self reference to the table and selects only the first row of each grouping to display as master record and as a reference to rest of the records in teh group. Please look at the user of INDEX () expression to select the first record of each group. The [SelffRef] uses “Product Codes” slice as a “source” table that is nothing but replica of main table “ProdReport”

VC [Related ProdRecords] : It is a system created reverse reference VC.This VC gets created as we created [SelffRef] reference VC for grouped records. Essentally, this [SelffRef] ref columns helps is creating a pseudo child table on the same table.

Slice ProductCodes Report : It selects only the first row in any grouping. Please observe the use of MINROW()and [RetentionCharge] .[RetentionCharge] creates groups and MINROW() selects the first row within that group to display in report as we need to display only one master record having [Retention] and [Charge Number] in any group.The (pesudo) child records having "Product Code"s of the group are anyway created by the pseudo child table concept described above.

Report Template: The outer SELECT() loop that uses ProductCodes Report slice, uses the only first of the records in each group as master record as described in slice description above. Inner loop displays pseudo child records 9basically all the [Product Code] records in that group)
Since much of the logic processing is done through VCs discussed above, the report template has become simpler.

Hope this helps.

4 Likes
5

Hi @Suvrutt_Gurjar.

Thank you again for such detail. It is a great help to users like me. I will begin incorporating this logic into my existing application.

1 Like
6

Hi @MauriceWhelan,

Thank you and you are welcome. I just mentioned one approach of how the desired functionality can be acchived based on the details you gave. I am sure you will evaluate fitment for your requirement in all respects before full scale implementaion. I am also sure you may come up with still a better aproach. All the best !

2 Likes
7

We would probably need to find a way how to group tables with a template. Then this would be much easier and we could group the report dynamically.

2 Likes
8

Hi @Aleksi,

I agree with you. I am sure an expert like you will suggest a much more efficient approach.

I tried to achieve it in template but I could not. I got stuck up while grouping (As per my understanding, [_THISROW] in a report template is not possible).

9

Hi @Suvrutt_Gurjar @Aleksi

Just as a related follow up question to this. I am trying a couple of small tweaks with the expression in my Workflow template. The mail is not triggering so I am presuming there is a problem with the expression but the Audit Log does not seem to be updating straight away. I had the same issue yesterday evening and was only able to review the error in the Audit Log this morning.

Does the audit log only update at certain intervals?

10

Yes it normally takes few minutes (sometimes even more) before you are able to see the result.

11

Hi @MauriceWhelan,

Today, I also do not seem to get audit log updates during my testing.

However I am unsure of exact update on this. Some one else or some one from AppSheet team may update you.

12

Thank you @Aleksi. Sorry that I posted without refershing the page or looking at your guidance.

13

Thank you @Aleksi @Suvrutt_Gurjar

1 Like
14

Hi @Suvrutt_Gurjar

My MINROW() expression is
[Product ID]=MINROW(“Operations_Master_Check”,“Product ID”,[RetentionCharge]=[_THISROW].[RetentionCharge])

but I am getting an error.
‘The inputs for function MIN must be a list of numeric values’

My [Product ID] is a Text type because it contains alpha numeric characters.

Could you advise on this one?

Thank you.

15

Hi @MauriceWhelan,

Got it.

Could you please instead try

[Product ID]=ANY(SELECT(Operations_Master_Check[Product ID],[RetentionCharge]=[_THISROW].[RetentionCharge]))

OR
[Product ID]= INDEX(SELECT(Operations_Master_Check[Product ID],[RetentionCharge]=[_THISROW].[RetentionCharge]), 1)

1 Like
16

Thank you @Suvrutt_Gurjar, worked perfectly.

1 Like
17

Hi @Suvrutt_Gurjar

Not quite there yet so I am hoping you might be able to help further. I have used a combination of my existing setup and your example above. To explain better I will try to outline where I am in similar fashion to your example.

The first main difference is that I have 2 tables involved i.e. Operations_Master_Check which stores all the [Charge Number], [Retention] etc… data and Generate_Transfer_Template which is a simple search form with 2 fields [Date] & [Charge Number]. The [Date] value dictates the options for [Charge Number] and my Workflow template triggers once a record is added to the Generate_Transfer_Template table.

VC [RetentionCharge] : I have bypassed this piece as I actually only need to group my [Product Code]'s by [Charge Number]

VC [ProductCodesByChargeNumber] (Your Example [CommonProdIDs]): My app formula here is SELECT(Operations_Master_Check[Product Code],[Charge Number]=[_THISROW].[Charge Number])

VC [SelfReftoPCBCN] (Your Example [SelffRef]): My app formula here is IFS(IN([Product Code], [ProductCodesByChargeNumber]), INDEX([ProductCodesByChargeNumber],1)). The [SelfReftoPCBCN] uses “Operations Master Check By Charge Number” slice as a “source” table that is nothing but replica of main table “Operations_Master_Check”.
The slice has a row filter ‘[Product Code]=ANY(SELECT(Operations_Master_Check[Product Code],[Charge Number]=[_THISROW].[Charge Number]))’

VC [Related Operations_Master_Checks] (Your Example [Related ProdRecords]) :
App Formula REF_ROWS(“Operations_Master_Check”, “SelfReftoPCBCN”)

Slice Operations Master Check by Charge Number (Your Example ProductCodes Report) : As per App Formula in VC [SelfReftoPCBCN]

Report Template: Start expressions are

Start:Select(Operations Master Check By Charge Number[Product Code],[Charge Number]=[_THISROW].[Charge Number])

Start:[Related Operations_Master_Checks]

After putting all that in place I am getting an Unable to find column ‘Related Operations_Master_Checks’ error.

Could you advise (1) if I am on the right track and (2) why that error might be triggering

P.S. Hope it is ok to keep checking my progress with you.

Many thanks.

18

Hi @MauriceWhelan,

Could you please update where you get that error Unable to find column ‘Related Operations_Master_Checks’ Do you get it in the report or in the app?

Do you see the VC [Related Operations_Master_Checks] with relevant ref IDs in the app in the table Operations Master Check By Charge Number[?

19

Hi @Suvrutt_Gurjar

The error is happening in the report. After attempting to trigger the workflow I reviewed the audit log file and could see error there.

Yes I can see the VC[Related Operations_Master_Checks] in the Operations Master Check by Charge number slice.

20

Hi @MauriceWhelan,

Thank you. Is it possible that you can share the report template?