How to update a field linked to an option set in the database

jpayne · 2023-08-16T00:54:28.589Z

I have a table with a field “AddressCountry” that is set to an Option Set of “Country Codes”. Option Set values are text:
Display Values Actual Value
United States US
Canada CA
Using the SQL Database Connecter I am reading a record in that also has the “CountryCode” Field that is formatted as Text and contains the value “US”. When I try to update the Bubble table “AddressCountry” with the SQL Table value of “US” it doesn’t allow me to update the field (I assume because the types don’t match TEXT vs Option Set Country Codes). Not sure how to update a field defined as an Option Set value. The expression just stays red…

boston85719 · 2023-08-16T07:08:02.172Z

jpayne:

I assume because the types don’t match TEXT vs Option Set Country Codes)

Yes

jpayne:

Not sure how to update a field defined as an Option Set value.

use an option value when updating it

jpayne:

Using the SQL Database Connecter I am reading a record in that also has the “CountryCode” Field that is formatted as Text and contains the value “US”.

Add attribute to your option set that is of type text. Name it country code. Add the appropriate country code value you to it.

Then for the option set to match the value from the SQL connector, use ‘get option’ then all options of the type you want then use filter and constrain by this options country code is equal to the SQL connector value (I think your step 10) and choose first item. Just make sure you don’t create more than one option with the same country code.

That way you are matching text against text in the filter to arrive at the option value needed for the field that is option value.