Fields won't save as ENUM when using a form?

AppSheet AppSheet Q&A
, ,
1

App setup

I have an app to manage inventory gear with a table called All Gear and another called Gear Categories.

The table All Gear includes the following columns:

  • Department
  • Category
  • Sub-category
  • Item
  • Category-Sub-category – a virtual column: CONCATENATE([Category],“-”,[Sub-category])
  • Facility (initial value is the user’s facility, references a different table)

The table Gear Categories includes the following columns:

  • Category
  • Sub-category
  • Departments (Enum List for which departments use that gear)

I wrote this expression so when the department is selected, the category field only shows gear used in that department:

*UNIQUE(*SELECT(Gear Categories[Category], CONTAINS([Departments],[_THISROW].[Department])))

When the category is selected, the sub-category field only contains gear in that category:

UNIQUE(SELECT(Gear Categories[Sub-category], AND(CONTAINS([Departments],[_THISROW].[Department]),[Category]=[_THISROW].[Category])))

The item field is self-referencing to the table and only shows items used in that department,

UNIQUE(SELECT(All Gear[Item], AND([Department]=[_THISROW].[Department],[Facility]=[_THISROW].[Facility],[Category-Sub-category]=[_THISROW].[Category-Sub-category]), TRUE))

All these expressions work properly and show the correct values.

The problem

When I try and add fields to this table I get the error message:

Unable to add row to table ‘All Gear’. → Value ‘Ropes’ in field ‘Category’ cannot be converted to type ‘Enum’.

I can do the following to technically solve the problem and allow the form to save but these options don’t work for our processes and needs:

  • Changing the field type on all columns from Enum to Text, BUT then I can’t use the Enum options as search filters, which I need to do, so this isn’t an option
  • Selecting the checkmark to “Allow other values”, BUT then the filters don’t work and the field Category will show ALL categories (not just department specific), sub-category will show ALL sub-categories for ALL gear, etc. This doesn’t work because it’s messy and people might enter gear with mis-matching or incorrect category-subcategory, which will cause a host of other problems for virtual columns based on this value and referencing other tables

I really don’t understand why this isn’t working because I have a similar setup on other tables (of fields, suggested values, and expressions) and they work, so I’m at a loss.

I talked to customer service and their suggestions didn’t work to solve this.

Can anyone please help??

2

Did you un-select the “auto-complete other values” option?

Are those expressions you mentioned in valid_if?

1 Like
3

I only selected “Allow other values” but didn’t un-select the
“auto-complete other values” option…but just tried it and it worked!!!
Thank you SO MUCH.

Can you tell me why that works? Maybe I don’t understand what the
“auto-complete other values” option actually does.

The expressions are in the suggested values, not valid if. If I put it in
valid if, will it only allow these values and not allow users to add other
values not on the list of suggested values?

4

“auto complete other values” adds all existing values in that column, from all other records in the table, to the dropdown. So basically it assumes that all existing values are also valid options.

5

Oh that makes sense.

Thank you again so much!!