Basic error (Valid If) Action Failed!

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.
image
.
.
and correctly displays [final_survey] = “Yes” with 4 [survey_status] options.
image
.
.
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… :slight_smile:

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?

image

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?

1 Like

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?

image

Good point… :flushed:

:wink:

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

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

1 Like

Hi @Steve,

Thanks… The penny finally dropped… :crazy_face:

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

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

:+1:

2 Likes

Well done!

1 Like