Basic error (Valid If) Action Failed!

[Jake_Naude]

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…

[Steve]

What is the action doing?

[Jake_Naude]

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?

[Steve]

What does this action do?

[Jake_Naude]

[Steve]

I would guess one of the rows doesn’t have [final_survey] = "Yes".

[Jake_Naude]

Steve:

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]

[Steve]

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?

[Jake_Naude]

Steve:

The STATUS: Change to Completed (ROW) is setting survey_status for all related survey rows, yes?

Correct.

Steve:

Do all of them have final_survey set to Yes , as required to allow survey_status to be set to Completed ?

No… It depends on status is not “On Hold”

[Jake_Naude]

Steve:

Do all of them have final_survey set to Yes , as required to allow survey_status to be set to Completed ?

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]

[Steve]

Jake_Naude:

I think I’m OK with the action itself as it is - not referring to [final_survey]

Are you?

[Jake_Naude]

Good point…

[Jake_Naude]

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?

[Jake_Naude]

Jake_Naude:

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…

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?

I’ll sleep on it and report back tomorrow… Thanks @Steve…

[Jake_Naude]

Steve:

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 ?

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]

Well done!