Help with Workflow Template Expression

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

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

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

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.

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

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 !

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

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)

[MauriceWhelan]

Thank you @Suvrutt_Gurjar, worked perfectly.

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

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[?

[MauriceWhelan]

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.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

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

[MauriceWhelan]

Of course @Suvrutt_Gurjar no problem. Can you share your preferred email address?

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

If it is possible, you may share the text or image of template expression in this post thread itself. Or You may wish tomessage me on this platform. My email is also in my profile.

Ifit is feasible to share in this post thread, other community members may also give their useful inputs.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

I have attached the Top and Bottom half of my template. Hope this is ok.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Thank you.

For the expression at the bottom of the report can you please write an expression like

<<Start:[Related Operations_Master_Checks] >>

instead of
<<Start:Select(Operations Master Check By Charge NumberRelated Operations_Master_Checks] >>

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Yes I have looked back through the Audit Log and I can confirm it was throwing an error with that expression too.

ERROR:
Expression ‘[Related Operations_Master_Checks]’ is invalid due to: Unable to find column ‘Related Operations_Master_Checks’.

[Aleksi]

Are you working with scheduled report with the option “ForEntiryTable”?

[MauriceWhelan]

No @Aleksi. It is just a standard Workflow Email Alert.

[Suvrutt_Gurjar]

Just to be doubly sure, could you also please reconfirm the column name exactly matches that exists in the table?

[MauriceWhelan]

@Suvrutt_Gurjar

I have both typed and copied & pasted the column name into the template. Still no joy.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Presume you see the grouped related records in the app at least , right?

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Do you mean using the ‘Test’ option in the App Formula to see if it returns any records?

If so, yes.

I am happy share app access if that is suitable.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

I mean the grouped related records as below (image from sample app)

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

You may wish to share the app at my email ID mentioned in my profile.

I hope it is OK, if I take a look at the app tomorrow.

[MauriceWhelan]

Hi Suvrutt

I have a query on this piece of functionality that I am hoping you could help me with. I have it working where I have the records grouped correctly in the template but what I want to incorporate is to generate the template based on [Date From] and [Date To] values of a simple search form for called Generate_Production_Plan (images below). So instead of generating an automated Report I want to generate the template when a record is added to to the Generate_Production_Plan table and for the records in the template just to be those in between the [Date From] and [Date To] values. I cannot seem to work out how best to incorporate this date range into the Slice Filter controlling the records being returned in the template.

I have also attached a screenshot of the resultant template and you can see some of the records are out of the range of the dates in my Search Form.

I hope this make sense. If so perhaps you could advise a potential solution for this issue.

Many thanks.

[Suvrutt_Gurjar]

Hi @MauriceWhelan ,

In general, I believe there will be a need to further qualify the slice used in the expression template with the date range to include only the applicable records.

Could you please share your current report expressions template?

You may wish to DM me if you would so prefer.

[MauriceWhelan]

Thank you Suvrutt. I will DM you.

[Aleksi]

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.

[Suvrutt_Gurjar]

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

[MauriceWhelan]

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?

[Suvrutt_Gurjar]

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.

[Aleksi]

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

[Suvrutt_Gurjar]

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

[MauriceWhelan]

Thank you @Aleksi @Suvrutt_Gurjar

[MauriceWhelan]

@Suvrutt_Gurjar

Apologies I think I put that SELECT piece in after the original expression I had in didn’t work.

I will restest just to be sure however without the SELECT expression.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

Happy to share and absolutely no problem to view tomorrow. Just to note my setup is slightly different to yours. I don’t actually have a detail view setup yet for each record in the Operations_Master_Check table. (no requirement in live versionat this stage)

I will work on putting this together this evening and it should be visible whenever you get a free minute tomorrow.

Also I have clicked on your profile but can’t seem to locate your email address. Could you just advise on the links to click to see it.

Many thanks again for all your help.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

I have a details view created called ‘ESB Operational Record Detail’ with the [Related Operations_Master_Checks] column added.

If you follow these steps you can view a list of related products
Select ‘Reports’
Select ‘ESB Pole Review’
Enter Product Code EG7646 and ‘Save’
The Related Products should be visible.

If you can direct me to your email in your profile I will share app accordingly.

Many thanks.

[MauriceWhelan · Accepted solution]

Hi @Suvrutt_Gurjar

Again many thanks with your help on this issue and ultimately with your amendment to the Workflow Template that sorted the issue. For the benefit of the community I have attached a before and after screenshot (of the template) of the expression in question. It appears that inserting the Start: & End tags inside the table did the trick.