Automation failing when adding enumlist to enumlist due to VALID_IF

[tjr2484]

Hi,

I have Table Class with an enumlist column type called Subjects. There is a Valid_IF constraint on this enumlist column which is “Table Lookup[subject_list]”.

So, when a user opens a form in Table Class, the Subjects column will display a list of subject_list, one value per row, which they can multiple select. Simple.

However, I have an automation that is attempting to copy values from the Subjects column from an existing row, which has a value like “Subject 1 , Subject 2 , Subject 3” and writing this value to a new row in Table Class, specifically to the same enumlist column Subjects.

The automation is set like below. The error I receive in the automation monitor states that “Subject 1 , Subject 2 , Subject 3” is not valid, which I assume AppSheet is treating that as a single value string, rather than three separate values. I tried using SPLIT(), and also changing the way the list looks. If I remove the Valid_IF, it works as expected (editing the row and selecting the enumlist dropdown has the three values pre-selected).

Allow other values and auto-compute other values is on.

Any pointers here?

[1minManager]

Its hard to say without further information. Screenshots of the various columns, bots and actions would help.

But my hunch is that the error is because ENUMLIST is a List type column and where your copying to is not expecting a list. Try wraping it in ANY() to convert it from a list to a single item. Or something like INDEX([…],1) to just pull in the first item.

[tjr2484]

Hi @1minManager ,

Thanks for your response.

I’ve created a dummy app to make the question less confusing. I feel I’m missing something super straightforward. Here are accompanying screenshots.

I have two tables, one of which is ‘Subjects selected’ that has a subject name enumlist with a valid_if that gets a list of subjects from the subject lists table:

Here is what the Subject list data from the subject list table looks like:

key	subjects
1	subject 1
2	subject 2
3	subject 3
4	subject 4
5	subject 5
6	subject 6
7	subject 7
8	subject 8
9	subject 9
10	subject 10

Naturally, I have a form that displays the subject name enumlist, enforcing the use of the Subjects list[subjects] valid_if, like so:

Now, here’s where the automation begins. I have an action that simply updates the current row’s [copy_from_class] with a unique id.

I then have a bot event which watches this [copy_from_class] for updates:

And finally I have the action of that bot to copy the input/current row’s values into a new row.

The bot results in an error, and displays the following error message.

It’s confusing because it seems like the bot is treating ‘subject 1 , subject 3 , subject 4 , subject 7 , subject 8’ as a text string rather than 5 list items, even though the list originally came from the same column where the bot is writing to. If the bot is using ‘subject 3’, it succeeds. This is also the case for your suggesions of ANY() or INDEX(), however I need the entire list. I’ve also tried using…

SELECT(Subject list[subjects],IN([subjects], [_THISROW].[subject name]))

instead of just [subjects] and had no luck.

Note: I understand the workarounds (e.g. action to open a form and edit the copy), or just create a new row from an action. Those are viable for me in my actual app’s setup as I’m using various refs and multiple tables.

I’d love to hear your ideas, or anyone else’s, as I’ve been stuck on this for a few days now. Thanks.

[tjr2484]

Found the problem in support docs… "When the server expression system evaluates the IN expression, it fails to detect that the EnumList field may contain a list of values. It mistakenly treats the value in the EnumList as a single value which it compares to each value in the LIST. For example, if the EnumList field contains “A , C”, it compares “A , C” to the LIST values “A”, then “B”, then “C”, and finally “D”. The EnumList value “A , C” does not match any of these values, therefore the Valid_If condition fails. "

Is there any workaround here?

[1minManager]

Not quite sure. But here’s what I’d suggest

1. Rather than just a VALID_IF, use Subject list[subjects] as the suggested list
2. Set the valid_if as

OR(
IN([_This],Subject list[subjects]),
CONTEXT("Host")="Server"
)

What this does it present the same list to pick from, provides a Valid_IF for the user, but gives the bot a ‘get out clause’ where the valid_if is bascially ignored.

https://support.google.com/appsheet/answer/10107885?hl=en&sjid=14411707833006030885-EU

[tjr2484]

Thanks, I’ve since changed a few things but I’ll try out the context host method.