Start Expression wrapped with ANY() is failing

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

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

LIST(ANY (…))

2 Likes

Hi @Suvrutt_Gurjar

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

<<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… :flushed:

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

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

2 Likes

Hi @Aleksi,

Thanks for coming in… :slight_smile:

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

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

1 Like

Hi @Suvrutt_Gurjar,

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

Or just TOP(..., 1).

2 Likes

Hi @Steve,

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

Missing comma:

image

1 Like

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"}))))))>>
3 Likes

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?

2 Likes

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

3 Likes

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,

3 Likes

Yep!

1 Like

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]…? :thinking:

1 Like

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"}))))))>>

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…

1 Like

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.

1 Like

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