ENUM LIST type column causing VALID IF error in automation action

[ProProjects]

Hello, I have a similar issue with my Enum list with SELECT() in the valid if causing errors. I have tried adding the space comma space for the item separator and it still produces the error.

I have an action that will not run on save/sync because it returns an error that the column entry does not pass the valid if. 

Here is the error from the app monitor...

Any help is appreciated. For now, the action will run if I remove the staff column.

[WillowMobileSys]

In your Valid if condition, you need to insert quotes around the word "Active".  In some places AppSheet will deduce the quotes but in an expression it is required to insert them. 

EDITED:  I did test the UI after my post above and the dropdown list does reflect the correct entries even though no quotes were supplied around the text.  However, I am not sure how this same expression behaves on the server side.  Remember that validation is applied to any row add or update even when done on the server in automation.   I am certain I had a similar issue recently with missing quotes but maybe it was a different use case.

If you find that inserting quotes doesn't help, then please verify that all 3 ID's noted in the error message are still present in the Staff table AND that those rows have [Status] = "Active". 

It is possible one of those Employee rows were removed or changed their status making that item(s) invalid.  It wouldn't be noticed unless there was an edit to that Project Schedule row OR, like what is happening here, that column is copied forcing validation on the new row.

[ProProjects]

Thank you for your insight. I tried adding the quotes on "Active", and readding staff column to the first action. (and refreshed) But still same error occurred. 

And correct, 4 and 9 are both active status employees.

[WillowMobileSys]

I was able to re-create the issue in a little test app.  I think this is  a bug with copying an EnumList when there is more than one item selected.  I found if I selected a single item, the row copied fine.

A workaround is to move the Valid_If expression into the Suggested_Values property.  This will still give the dropdown list but it won't provide the stringent checking of the list values such as when you are editing a row.  Maybe it'll work for you?  See second image.

Recreated Error

Workaround - move expression to Suggested Values

[ProProjects]

---

@WillowMobileSys wrote:

a little test app. 

---

Moved to suggested values, but new error occurs.

Column Name 'Staff' in Schema 'Project Schedule_Schema' of Column Type 'EnumList' has invalid 'Suggested Values' of '=Select(Staff[employee_id], [status]="Active" )'. The type of the Suggested Values does not match the column type.

Do you have anything for Type Details > value?

Here are my other settings: 

Thanks for working through this with me! We'll overcome this bug.

[ProProjects]

Also FYI- Employee ID column type in the source Staff table is the type: Number . Not sure if that matters. 

[WillowMobileSys]

Number is perfectly ok as the data type.

[PolaEla]

This is the old post, but I've been struggling with the same error for a while and finally figured it out. Because I was using a legacy spreadsheet my IDs were Numbers, and for some reason, AppSheet does not compute Suggested Values in EnumList of Ref type, if it's a list of numbers. I changed the type of my IDs to Text and it worked. It's pretty annoying, but not hopeless. 

Leaving it here for future users, because I didn't find a closer related topic.

[WillowMobileSys]

You are correct!   And it does seem to be a bug.  However, if you move the expression from Suggested Values to Valid If, it will perform the same. 

Since you are dealing with a REF column (meaning the values MUST be a Row Key), it doesn't really make sense to allow "optional" Row Keys to be inserted as allowed by a Suggested Values list.  So moving it to Valid If makes sense.

However, the same issue occurs even if the type is NOT a REF column but just a list of plain numbers.  It is a bug and should be reported to AppSheet.

NOTE:  Same issue occurs if it is a Number or a Decimal type.  I would image any other numeric types - Percent, Price, etc - have the same issue as well.

[WillowMobileSys]

Is Staff with Roll (slice) derived from the Staff table?  If so then there should be no mis-match.  I'll check this setup on my end. 

You will want to change the datasource used in the SELECT to be "Staff with Role (slice)" as well

For the datasource of the column, you will best served to use the original table. It will likely be needed for editing of rows later.  

[eshann]

nice post

[Steve]

---

@WillowMobileSys wrote:

In some places AppSheet will deduce the quotes but in an expression it is required to insert them. 

---

Technically speaking, quotes are mostly not required in expressions, but they are in some places, and there are some places where their presence or absence may change the interpretation of the expression.

It is always safe to put quotes around values that are to be interpreted as text, but omitting quotes may cause problems. For that reason, I always use quotes, and tried to include quotes consistently in the help docs I wrote.

For instance, true and false are interpreted as Yes/No values, but "true" and "false" are Text values. The bare word Date will cause an error because the expression interpreter thinks it's a reserved word with special meaning, but "Date" will be seen as a Text value.

[haneeff]

this list values such as when you are editing a rows