Start Expression wrapped with ANY() is failing

[Jake_Naude]

Hi There,

I’ve tested this expression in a VC and it yields the result I’m after.

However, when used in a workflow action start expression the ANY() wrapper causes it to fail:

<<Start:ANY(SELECT(survey[id], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch") OR([survey_status]="Completed", [survey_status]="In Progress"))))>>
.
.
I get this error:

Failed: Action not performed because 1 errors are present. Error: Workflow rule 'Survey Completion Report (Location)' action 'Action 1' Attachment template. Start expression 'ANY(SELECT(survey[id], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch") OR([survey_status]="Completed", [survey_status]="In Progress"))))' 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..
When I remove the ANY() wrapper from the start expression, the expression fires but it obviously does not yield the correct result.
.
.
My survey table has many rows of the same [survey_number] (left column) and I’m trying to get a start expression to show just one of each of the [survey_number] rows.
.
.
Thanks in advance…
.
.

[Suvrutt_Gurjar]

Please try to wrap LIST() around ANY(), something like

LIST(ANY (…))

[Jake_Naude]

Hi @Suvrutt_Gurjar…

Thanks for that. I did as suggested and the start expression is now firing…

<<Start:LIST(ANY(SELECT(survey[id], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch") OR([survey_status]="Completed", [survey_status]="In Progress")))))>>
.
.
I was wrong about ANY() producing the correct result in the VC expression…

Please could you help me correct my start expression as the current version returns a result of just one row.

The result I’m trying to achieve is a list of rows from the survey table that shows just one row from each different [survey_number] number…?

Hope that makes sense…?

[Aleksi]

Remove the LIST() and ANY() and you should be fine. You need to generate a list of key values with the Start: & End expression.

[Jake_Naude]

Hi @Aleksi,

Thanks for coming in…

I have removed LIST() and ANY() and that gives me almost the result I’m after. It’s a good list but it shows every [survey_number] row.

The result I’m trying to achieve is a list of rows from the survey table that shows just one row from each different [survey_number] number…?

Something like this please…?

[Suvrutt_Gurjar]

I am sure @Aleksi will guide with more optimized solution.

As per my understanding, I believe you could do it in two stages. Please create a VC called say [UnicSurveyNos] with an expression like

UNIQUE(SELECT(survey[survey_number], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], “Cable” = [_THISROW].[survey_type], NOT([branch_info] = “new branch”) OR([survey_status]=“Completed”, [survey_status]=“In Progress”))))

Then the START expression could be something like

<<Start: SELECT(survey[id], IN([survey_number], [UnicSurveyNos]))>>

[Jake_Naude]

Hi @Suvrutt_Gurjar,

That looks great… Makes sense… I’ll give it a try and will report back… Many thanks as always…

[Steve · Accepted solution]

Try:

<<Start: FILTER("survey", ([ROWNUMBER] = MIN(SELECT(survey[_ROWNUMBER], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch"), IN([survey_status], {"Completed", "In Progress"}))))))>>

[Suvrutt_Gurjar]

Hi @Steve,

Just wanted to request your guidance on this part.

Whenever there are two or more multirow functions ( SELECT(), FILTER() ETC.) in one combined expression, does it help in any way in terms of sync time performance to process them separately in stages?

Does the outer multirow expression take into account the “processed” inner multirow expression if processed separately in different columns?

Or is it that, whenever logic needs multirow expressions, it does not help in terms of performance anyway whether processed jointly or separately?

[Steve]

Suvrutt_Gurjar:

does it help in any way in terms of sync time performance to process them separately in stages?

Yep! But in a <<Start>> expressions, performance really isn’t an issue as it’s done entirely server-side.

[Suvrutt_Gurjar]

Got it. Thank you @Steve

So my takeaway from your guidance is if within app expressions , better to split multiple multirow functions into separate processing through say VCs.

For reports / workflows it really does not matter. Moreover, I believe reports/ workflows typically are not fired at the high frequency as say in app formulas, so I believe a complex expression should not matter for that reason as well,

[Steve]

Yep!

[Jake_Naude]

Hi @Steve,

I’m getting a result of one row from:

<<Start: FILTER("survey", ([_ROWNUMBER] = MIN(SELECT(survey[_ROWNUMBER], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch"), IN([survey_status], {"Completed", "In Progress"}))))))>>
.
Is it possible to do something similar and return a list of each MIN ROW for [survey_number]…?

[Steve]

Sure! Just take out whichever conditions limit the search. For instance, here I removed [survey_number] = [_THISROW].[survey_number]:

<<Start: FILTER("survey", ([_ROWNUMBER] = MIN(SELECT(survey[_ROWNUMBER], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], "Cable" = [_THISROW].[survey_type], NOT([branch_info] = "new branch"), IN([survey_status], {"Completed", "In Progress"}))))))>>

[Jake_Naude]

Hi @Steve,

I think I get that and when I run an Expression Assistant test the results make sense to me but when I see the final result in the PDF attachment generated by an action in a Workflow I only see one row of data…?

I’m trying to produce a PDF attachment that has many rows in a ‘report’ i.e. each MIN ROW for each set of [survey_number] but the current output is a single row of data i.e.

Ideally, the ‘report’ should have one snapshot like this from each set/block of [survey_number]…?

I’m really stuck…

[Koichi_Tsuji]

Probably you have to wrap by another start expression by and per inspection/survey to do it.

By the way, London tube station! still not clean enough. haha.

[Jake_Naude]

tsuji_koichi:

Probably you have to wrap by another start expression by and per inspection/survey to do it.

Hi @tsuji_koichi,

That sounds tricky… I was hoping the answer would be as easy as:

Obviously not that easy…

I’d be grateful if you could point me in the right direction…?

[Koichi_Tsuji]

Outside your table in workflow template, you add start/end expression which will return the each servey uniquely.

I mean you probaly need to nest the original expression by another start/end expression to yeild per survey result, which show only one row per survery.

P.S.
I used to be living in city on Norther Line for almost 10 year…

[Jake_Naude]

tsuji_koichi:

I used to be living in city on Norther Line for almost 10 year…

The Northern Line isn’t the most efficient line… Hope that’s not why you left…

[Koichi_Tsuji]

haha. yes, it was indeed. I

Back to your question.

Your expression wrapped by ANY() should always yield one row, which is quite natural.

Let me simply your story.

This is what i mean.

Wrap by another start/end expression which yield the result per unique survey, and push your original expression you discussed here within table.

[Jake_Naude]

Thanks for the detail @tsuji_koichi… I think I get it… I’ll take this and see what I can do… Cheers…

[Koichi_Tsuji]

Yeah, try it out with your case.
Once your hands get more dirty, you would solve the problem! Good luck, mate.

[Jake_Naude]

tsuji_koichi:

Outside your table in workflow template, you add start/end expression which will return the each servey uniquely.

I mean you probaly need to nest the original expression by another start/end expression to yeild per survey result, which show only one row per survery.

I have my current start expression in a workflow template and will try as you’ve suggested above… I’ll see if I can get my head around the concept… Cheers…

[Jake_Naude]

Steve:

Sure! Just take out whichever conditions limit the search. For instance, here I removed

Hi @Steve,

I’m not sure if it makes a material difference but I didn’t mention earlier that the start expression is housed in a workflow template…? Are expressions calculated the same there as in tables…?

[Steve]

Jake_Naude:

Are expressions calculated the same there as in tables…?

Yes

[Suvrutt_Gurjar]

Hi @Jake_Naude,

Just in case you are working on this, you may wish to give the following expression a try.

Based on @Steve’s guidance, I have combined the two stage expressions into one. So the following expression does not require an additional VC. It can be tried in the template expression.

<<Start: SELECT(survey[id],
IN([survey_number], UNIQUE(SELECT(survey[survey_number], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], “Cable” = [_THISROW].[survey_type], NOT([branch_info] = “new branch”) OR([survey_status]=“Completed”, [survey_status]=“In Progress”))))))>>

[Jake_Naude]

Suvrutt_Gurjar:

Based on @Steve’s guidance, I have combined the two stage expressions into one. So the following expression does not require an additional VC. It can be tried in the template expression.

Hi @Suvrutt_Gurjar,

Thanks for that… Yes… I’m still very much working on this…

Your expression produces multiple pages which is great!

I’ve got an extract of it below to highlight the issue I’d like to work on…

This section has seven rows of data for [survey_number] SS26_N123.

Ideally, I’d only like to show one row (the first?) for each [survey_number] ending up with a report that has many rows but only one for each [survey_number].

I’ll keep working with this and see if I can combine it with the MIN() example that came from @Steve.

I’ll let you know how I get on.

Thanks again…

[Suvrutt_Gurjar]

Got it. Thank you.

If I may add, based on @Steve’s guidance and his shared expression, I believe you could try following.

1. First create a slice called say SurveyReportSlice on the table survey with following expression

[_ROWNUMBER] = MIN(SELECT(survey[_ROWNUMBER], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], “Cable” = [_THISROW].[survey_type], NOT([branch_info] = “new branch”), IN([survey_status], {“Completed”, “In Progress”}))))

2. You could then base your template expression based on that slice instead of the table, something like below

<<START : SELECT(SurveyReportSlice [ID], TRUE)>>

[Jake_Naude]

Hi @Suvrutt_Gurjar,

Thanks for sticking around… That sounds like the ticket… I’ll give it a go right away… Cheers…

[Jake_Naude]

Suvrutt_Gurjar:

Got it. Thank you.

If I may add, based on @Steve’s guidance and his shared expression, I believe you could try following.

1. First create a slice called say SurveyReportSlice on the table survey with following expression

[_ROWNUMBER] = MIN(SELECT(survey[_ROWNUMBER], AND([LCS] = [_THISROW].[LCS], [select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey], [branch_name] = [_THISROW].[branch_name], [survey_number] = [_THISROW].[survey_number], “Cable” = [_THISROW].[survey_type], NOT([branch_info] = “new branch”), IN([survey_status], {“Completed”, “In Progress”}))))

2. You could then base your template expression based on that slice instead of the table, something like below

<<START : SELECT(SurveyReportSlice [ID], TRUE)>>

.

.
Too beautiful for words… Works perfectly…!!!
.
My app would be a dull and simple thing without you guys…!!!
.
Thank you so much…!!!
.
@Suvrutt_Gurjar, @Steve, @Aleksi & @tsuji_koichi
.

[Koichi_Tsuji]

Lovely.

[Suvrutt_Gurjar]

@Jake_Naude
Nice to know it works the way you want. I request that you mark @Steve’ s expression as solution, as we just tweaked the manner of using it.

Your requirement and issue posting is also always detailed. So that always helps.

Yes, you are right. We keep learning on daily basis from insightful posts of AppSheet experts like @Aleksi, @Steve, @tsuji_koichi , other experts and this great community…

[Steve]

Suvrutt_Gurjar:

Please try to wrap LIST() around ANY(), […]

Or just TOP(..., 1).

help.appsheet.com

TOP()

Initial list items

[Jake_Naude]

Hi @Steve,

Thanks… Have to pop out for an hour… Back on it ASAP…

[Steve]

Missing comma: