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โฆ
.
.
Solved! Go to 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"}))))))>>
Please try to wrap LIST() around ANY(), something like
LIST(ANY (โฆ))
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โฆ?
Remove the LIST() and ANY() and you should be fine. You need to generate a list of key values with the Start: & End expression.
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โฆ?
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]))>>
Hi @Suvrutt_Gurjar,
That looks greatโฆ Makes senseโฆ Iโll give it a try and will report backโฆ Many thanks as alwaysโฆ
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"}))))))>>
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?
Yep! But in a <<Start>>
expressions, performance really isnโt an issue as itโs done entirely server-side.
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,
Yep!
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]โฆ?
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โฆ
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.
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โฆ?
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โฆ
The Northern Line isnโt the most efficient lineโฆ Hope thatโs not why you leftโฆ
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.
Thanks for the detail @tsuji_koichiโฆ I think I get itโฆ Iโll take this and see what I can doโฆ Cheersโฆ
Yeah, try it out with your case.
Once your hands get more dirty, you would solve the problem! Good luck, mate.
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โฆ
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โฆ?
Yes
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โ))))))>>
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โฆ
Got it. Thank you.
If I may add, based on @Steveโs guidance and his shared expression, I believe you could try following.
[_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โ}))))
<<START : SELECT(SurveyReportSlice [ID], TRUE)>>
Hi @Suvrutt_Gurjar,
Thanks for sticking aroundโฆ That sounds like the ticketโฆ Iโll give it a go right awayโฆ Cheersโฆ
.
.
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
.
Lovely.
@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โฆ
Or just TOP(..., 1)
.
Hi @Steve,
Thanksโฆ Have to pop out for an hourโฆ Back on it ASAPโฆ
Missing comma:
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |