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:
Thanks…
Solved! Go to 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:
What is the action doing?
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?
What does this action do?
I would guess one of the rows doesn’t have [final_survey] = "Yes"
.
Correct… Of n surveys, only the final one will have [final_survey] = “Yes”
That explains why it works with just a list and no reference to [final_survey]
The STATUS: Change to Completed (ROW) is setting survey_status for all related survey rows, yes? Do all of them have final_survey set to Yes, as required to allow survey_status to be set to Completed?
Correct.
No… It depends on status is not “On Hold”
I think I see where you’re going with this… I’m using [final_survey] as a prompt and active confirmation from the surveyor before they proceed and fire the action.
I think I’m OK with the action itself as it is - not referring to [final_survey]
Are you?
Good point…
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:
Well done!
My intention with introducing [final_survey] was to have a way to hide the “Completed” option from the surveyor until they actively say “Yes” this is the final survey. This should reduce the possibility of accidentally firing the action?
Should I look at changing how I present the options to the surveyor?
@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…
User | Count |
---|---|
44 | |
28 | |
28 | |
21 | |
13 |