Semi-controlled list of values with Appsmith

Luc Martinon
2 min readJan 17, 2023

--

I recently stumbled upon this article about dynamic filters with Appsmith, which reminded me that I wanted to write this piece.

For a project for a small NGOs we need to register user’s place of birth, country and city. In the DB, both fields are text, but putting a text field in the UI would lead to duplicated entries, like “Ivory coast”, “Ivory Coast” and “Cote d’Ivoire”. So we needed a drop-down list. Finding a list of countries if of course very easy, but finding a list of cities for each country is impossible, as it would never be complete. We need the capacity to add an entry, but only after the user had checked if there wasn’t already an corresponding entry in the other users. Here is my solution:

My solution here is to have an “OTHER” option in the list drop down, that triggers the visibility of another text field, below with the blue arrow.

1: select_country
2: select_city
3: text_city

select_country is populated with a fix list of values. select_city however, is populated from an SQL query called select_cities:

SELECT distinct 
birth_city as label, birth_city as value
FROM users
WHERE birth_country = {{select_country.selectedOptionValue}}

UNION select 'OTHER' as label, 'OTHER' as value

order by 1 desc

This allows the list of cities to be filtered by the country chosen, plus the “OTHER” value is added. The action select_cities is triggered on the onOptionChange of select_country.

The selects have their Selected option set to fetch_user.data[0].birth_country and fetch_user.data[0].birth_country, fetch_user is an SQL query that returns the details of the user.

The visibility of text_city is set to: {{select_city.selectedOptionValue == “OTHER”}} , and its default text is set to: {{select_city.selectedOptionValue == “OTHER” ? null : select_city.selectedOptionValue}}.

This allows that no matter if a city is selected in the select or in the text field, the text field’s value will be the correct one. This allows our save SQL query update_user to be very simple:

UPDATE users SET 
pays_naissance = {{ select_country.selectedOptionValue}},
commune_naissance = {{ text_city.text }},
WHERE id = {{ appsmith.store.user.id}};

The cherry on the cake is that upon saving, reloading the SQL query select_cities will now return the new city. The js triggered by the save button:

{{
function(){
update_user.run(async () => {
if (select_city.selectedOptionValue === "OTHER"){
select_cities.run();
}
await fetch_user.run();
showAlert('User updated!','success');
}, () => {}) ;
}()
}}

After saving, I trigger select_cities if the selected value is “OTHER”. I also reload the user.

The result as a gif (AUTRE means OTHER):

--

--