Add filled EnumList (type Ref) field via API

AppSheet AppSheet Q&A
1

I have a table “Articles” with an EnumList field “scope” that contains only values from another table “Scopes”.

In the Type Details area:

  • there are no added values
  • the “Allow other values” option is checked (enforced by the app)
  • the “Auto-complete other values” option is unchecked

In the Base Details area i selected Stack as the input mode. Item separator is just the comma.
I also defined a Valid If condition in order to prevent users from adding values.

FILTER(Scopes, AND([audit] = [_THISROW].[audit], [id_checklist] = [_THISROW].[id_checklist]))

I add data to these tables via API and I need the field “scope” to be filled (to obtain a default selected value behaviour).

Suppose there are two possible values for “scope”, say “Quality” and “Environment”. If I add a new row to Articles and the value sent for that field is just one of the possible values, all works fine. But if I try to send both values it fails with

“Message”: “REST API invoke request failed - Error: Row having key ‘33 : 236 : -1 : 7946’ in table ‘Articles’ containing value ‘33: 236: 114 , 33:236: 65’ in field ‘scope’ failed ‘Valid_If’ condition.”

Additional info

  • I the request, a row is like this: {“audit”:33, “id_checklist”:236, “id_section”:-1, “id_article”:7946, “text”:“…”, “scope”:“33: 236: 114,33:236: 65”}
  • If I send one value (or none) in the “scope”, go the app, select both possible options and save, the “scope” is filled with: 33: 236: 114,33: 236: 65

I wonder what am I doing wrong.
Thank you.

EDIT:
I tried another way:

  • not send the “scope” field in the request
  • configure the initial value to

FILTER(Scopes, AND([audit] = [_THISROW].[audit], [id_checklist] = [_THISROW].[id_checklist]))

I’ve got the same message error.

2

I don’t have any practical experience with API call, web hooks, or even JSON, so I can only speculate.

The target column, scope, is an EnumList of Ref. Your API call is sending a single value for scope, the single value being 33: 236: 114 , 33:236: 65. That single value does not match any of the valid values given by your Valid If expression.

It appears you’re trying to populate the scope column’s EnumList with two values. My impression of JSON is that JSON expects multiple list values presented as a JSON list; you should not be using the format of the stored value in the spreadsheet. So I’d expect your call should look more like this (formatted for clarity):

{
  “audit”:33,
  “id_checklist”:236,
  “id_section”:-1,
  “id_article”:7946,
  “text”:"…",
  “scope”:[
    “33: 236: 114",
    "33:236: 65”
  ]
}

Again, I have no practical experience with JSON, so the above may be totally wrong.

1 Like
3

Thank you @Steve

It makes sense but doesn’t work.
This is the request response

{> “Message”: “An error has occurred.”,> “ExceptionMessage”: “The reason phrase must not contain new-line characters.”,> “ExceptionType”: “System.FormatException”,> “StackTrace”: " …"> }

I also tried with the “Initial value” configuration.

  • In the app, I configured an “Initial value” in the field “scope”. Configuration is the same expression as in “Valid If”:

FILTER(Scopes, AND([audit] = [_THISROW].[audit], [id_checklist] = [_THISROW].[id_checklist]))

As I mentioned before, sending the API request without the “scope” field I get the same error message:

“Message”: “REST API invoke request failed - Error: Row having key ‘33 : 236 : -1 : 7946’ in table ‘Articles’ containing value ‘33: 236: 114 , 33: 236: 65’ in field ‘scope’ failed ‘Valid_If’ condition.”

Adding another row to table Articles via the App, it works.

But I must add the rows via API.

4

This is the result of a shortcoming in the server expression evaluation system.

See topic “Add or Update of an EnumList Field Reports ‘failed Valid_If condition’” in this article.

3 Likes
5

Thank you for the explanation and pointer. Has there been any progress on this in the past three years?

6

As a workaround, you might try (untested!) moving LIST(“A”, “B”, “C”, “D”) to Suggested values and setting Valid if to ISBLANK([_THIS] - LIST(“A”, “B”, “C”, “D”)).