Semi-controlled list of values with Appsmith
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):