Working with ENUMs in Forms and Update / Insert Queries

ENUMs are a confounding data type when used in data entry / edit forms and update / insert queries:

  • If I try to insert or update Retool DB table with a GUI mode Insert or Update and provide the object, I get an error that the ENUM value from the form (the .value property of a Select component) is not in a valid format. So, I have to use the key / value pair method.

  • This appears to be required because a Select .value property sometimes is an array and sometimes a string, which seems weird, but I've watched it happen.

Is the above expected behavior? Am I using ENUMs incorrectly by populating a Select with the ENUMs (as both .value and .name)?


Hi @haj,

Thanks for reaching out. I'm not 100% sure if I'm following, so please correct me if this isn't helpful!

The select component's .value property will not ever be an array since you can only select one item. The select component's .values property will always be an array since it's the full list of possible options. Note that multiselect's .value property (and .values property) will always be an array since you can pick more than one option

It can sometimes be tricky to update data when using different values and labels. Here, I have a form that updates a record in my table. The form has a select component which has values (id #s) and labels (the string value that the user will recognize). I run into an issue where my form data only has access to the value (2), but I want to update my projects table with the label (On Hold):

I have to either make the value of the select component the same as the label ({{item.project_status}}) or use Javascript inside my update query to map the value in the form back to the label:

It sounds like your case might require some extra JS to make sure the data is formatted properly. Happy to take a look if you can share some screenshots of the case where you find yourself needing to use the key value pair method

I don't think the problem is what you describe. I think the problem is the data type of the .value property versus the data type that Postgres wants to receive for an ENUM. The .value data type is a string, but Postgres doesn't seem to like a string as the data type for an ENUM.

Ah okay! In that case, I'd recommend adding some Javascript inside the {{}} within the Postgres query :slightly_smiling_face:

Here's an example where I'm changing project status from a String to an Array:

Thank you. I will investigate this alternative.