LinkedIn
Twitter
Evening all,
Iโm having some finger trouble with a Valid If expression in a [survey_status] Enum (Text) column which refers to another column [final_survey].
The [survey_status] Valid If says:
IFS(
[final_survey] = "Yes",
LIST("In Progress", "On Hold", "Aborted", "Completed"),
[final_survey] = "No",
LIST("In Progress", "On Hold", "Aborted")
)
.
The survey form correctly displays [final_survey] = โNoโ with 3 [survey_status] options.
.
.
and correctly displays [final_survey] = โYesโ with 4 [survey_status] options.
.
.
But if I select โYesโ + โCompletedโ and save, I get this error:
.
.
I know Iโm doing something simple wrong but canโt find what it is?
Thanksโฆ 
Solved
1 ACCEPTED SOLUTION
Hi @Steve,
Thanksโฆ The penny finally droppedโฆ 
I can see now that the action was failing because (other rows in the table - not the survey form row) did not have a โYesโ in [final_survey] and that is what the [survey_status] Valid If was looking forโฆ 
Iโve resolved the issue by keeping [final_survey] in play with [survey_status] Valid If:
SWITCH(
[final_survey],
"Yes", LIST("In Progress", "On Hold", "Aborted", "Completed"),
"No", LIST("In Progress", "On Hold", "Aborted"),
LIST("In Progress", "On Hold", "Aborted")
)
.
.
and updating the action:
.
.
Many thanks for the guidanceโฆ Another light has come onโฆ!!!
Hi @Steve,
Thanks for coming inโฆ
This is the action:
And this is itโs reference rows expression:
IFS(
AND(
LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_type] = "Cable",
[LCS] = [lcs],
[select_cabinet_to_survey] = [select_cabinet_to_survey],
[select_asset_type] = [select_asset_type],
[_THISROW].[asset_name] = [asset_name]
))),
"survey", "_ROWNUMBER", "survey_status"
) = "Completed",
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[_THISROW].[survey_type] = "Cable",
[_THISROW].[LCS] = [lcs],
[_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey],
[_THISROW].[select_asset_type] = [select_asset_type],
[_THISROW].[asset_name] = [asset_name],
[_THISROW].[survey_status] = "On Hold"
))) = 0
),
SELECT(
survey[id],
AND(
[survey_type] = "Cable",
[LCS] = [lcs],
[select_cabinet_to_survey] = [select_cabinet_to_survey],
[select_asset_type] = [select_asset_type],
[_THISROW].[asset_name] = [asset_name],
ISBLANK([branch_info]),
OR(
[survey_status] = "Completed",
[survey_status] = "In Progress"
)))
)
I suspect that the problem is not with the action because it works OK if I simplify the Valid If to just a basic list that does not include the [final_survey] column. So this works:
LIST("In Progress", "On Hold", "Aborted", "Completed")
Does that help?
Correct.
Noโฆ It depends on status is not โOn Holdโ
Hi @Steve,
Thanksโฆ The penny finally droppedโฆ
I can see now that the action was failing because (other rows in the table - not the survey form row) did not have a โYesโ in [final_survey] and that is what the [survey_status] Valid If was looking forโฆ
Iโve resolved the issue by keeping [final_survey] in play with [survey_status] Valid If:
SWITCH(
[final_survey],
"Yes", LIST("In Progress", "On Hold", "Aborted", "Completed"),
"No", LIST("In Progress", "On Hold", "Aborted"),
LIST("In Progress", "On Hold", "Aborted")
)
.
.
and updating the action:
.
.
Many thanks for the guidanceโฆ Another light has come onโฆ!!!
@Steveโฆ Youโve made me think of something that Iโm going to tryโฆ
Iโll revert to a simple list for [survey_status] that has all four options in it and have no reference to [final_survey] in the expression.
LIST("In Progress", "On Hold", "Aborted", "Completed")
Iโll move the [final_survey] reference into the action: STATUS: Change to Completed (ROWS) insteadโฆ?
ActuallyโฆI donโt think thatโs going to work becauseโฆ
Iโll sleep on it and report back tomorrowโฆ Thanks @Steveโฆ
