Set dropdown menu based on query result

Hi, I'm a complete beginner when it comes to Retool and Javascript so I'm in a little deep here.

I've managed to create a small application that has a query based on the users input. It then accesses an API and pulls the data I need.

I then want to create a form (made from drop down menus) that is filled out based on the data query - the user then will be able to confirm the information is correct and if not change it to the correct option.

I don't know:

a) how to set up a drop down menu to have a variable default value

b) How to link the collected data to a key as there are a lot of variables that will point to the same drop down menu for example 1a,1b,1c,1d = Green, 2a,2b,2c,2d = Red, 3a,3b,3c,3d = Blue... Would this be something along the lines of creating a Key Index?

Thanks in advance for any help - not really been sure what to google with this problem.

1 Like

Welcome.

For point a, you can set a default value in the select component. Just set that default based on a query, or as in the case of this screenshot based on what is selected in a table.

Here is a working example application you can import:
dropdownBasedOnQuery.json (16.8 KB)

As for point b, I'm not sure I follow. It would be helpful if you could elaborate a bit more on how your data is structured.

Thanks for your help in regards to point 1, I suppose making a table of potential answers for the drop down helps this rather than manually inputting them.

Screenshot 2022-12-11 at 01.41.25

This is the data I am collecting when a user runs a query.

I would then like to run this data into a form with drop down menus for confirmation, however there are multiple options within the data for what can be one drop down menu for us to collect instead. For example there are over 30 different entries for saying Loft insulation where I need about 4 options being : Limited Insulation, No Insulation, Full Insulation and another property above.
Screenshot 2022-12-11 at 01.41.37

Hope that make it a bit clearer on what I'm doing.

Could you share a few actual examples?

I think I kinda get what you're after, but not totally certain. It sounds like you might need a transformer between your query and setting a default for the dropdown menu. That way you can condense your 30 input options to 4 output options.

Updated Test App:
dropdownBasedOnQuery.json (54.5 KB)

In this test app, I'm using table1.selectedRow.data.userId as a proxy for your "30 options". Based on what that option is I used a switch statement to pick one of 3 menu options. Then I set the output of the transformer as the default value for the select input.

I think it might be that! I've just had a look at your Test App and I think it could work.

There are lots of options so I've not managed to get them into a retool table as of yet, just on excel -

ROOF_DESCRIPTION Roof Selection on App Options
Pitched, 75 mm loft insulation 100mm or less 100mm or less
Pitched, 200 mm loft insulation 200mm 200mm
Pitched, no insulation (assumed) 100mm or less 300mm
Flat, limited insulation (assumed) Flat Roof Insulated Another property above
(another dwelling above) Another property above Room in Roof (Insulated)
Average thermal transmittance 0.19 W/m²K Flat Roof Insulated
Pitched, 150 mm loft insulation 200mm Flat Roof No Insulation
Roof room(s), insulated Room in Roof (Insulated)
Pitched, 150mm loft insulation 200mm
Pitched, 250 mm loft insulation 300mm
Pitched, 50 mm loft insulation 100mm or less
Pitched, 100 mm loft insulation 100mm or less
Pitched, 100mm loft insulation 100mm or less
Pitched, 300+ mm loft insulation 300mm
Pitched, no insulation 100mm or less
Pitched, 270 mm loft insulation 300mm
Average thermal transmittance 0.11 W/m²K
(other premises above) Another property above

Hmm not too sure where I've gone wrong with this..

I've inputted some of the options that will be generated by users inputs:

However unfortunately each time its returning null, where I can see the option is set to one of the return options
Screenshot 2022-12-11 at 03.17.44

For reference - table of options, wondering if there is a way to make use of tables for the data points to reference?
Screenshot 2022-12-11 at 03.32.59

Really appreciate your help, will have a look again tomorrow morning to see if I can make sense of all this

Got it working in the end! Saw I was doing wrong there! Thank you so much for your help

1 Like

No problem, glad you sorted it out!

Just wondering if there is a way to map data to the options? Would make it easier to add options on the query at a later date and also a cleaner code

I was thinking something like this -

Blockquote
switch({{text7.value}}) {
case {{keyValue2.data.ExternalInsulation}}:
return "External Insulation"
break;
case {{keyValue2.data.FilledCavity}}:
return "Filled Cavity"
break;
case {{keyValue2.data.InternalInsulation}}:
return "Internal Insulation"
break;
case {{keyValue2.data.NoInsulation}}:
return "No Insulation"
break;
case {{keyValue2.data.PartialInsulation}}:
return "Partial Insulation"
break;
default:
return null;
}

However this doesn't work, I was hoping it would scan all the options with the same key.
I did try using table5.columns'[1]' but it only gives me the columns title

I'm not certain exactly what you're trying to accomplish.


Instead of having the case fixed in the code is it possible to reference a table that has the different options? I've got 200 different value to be checking against and they might change at some point so it would be good if I could use a table that could be changed as and when instead of having a direct reference.

Screenshot 2022-12-12 at 19.24.10

So, in your query you want to select all values for "roof-description"?
What does that query result look like?
And are you storing "cavity" values in another table in the database?
There is definitely a way this can be done by getting your data all at once and not having to run so much logic on the front-end

roof-description in this case is just one value that is generated about the property. The query result is usually one row with about 25 different data points.

Yeah I have a spreadsheet of the different options with stated end point

It's a little difficult for me to clearly understand what you want this app to do...but I'll take a stab

A user is entering information about a property. Based on the property you would populate the preset information you already have in your database.
So, for example select all options for the roof, cavity, heating, etc... each of these would need to be a separate query to populate the dropdowns so that the user could make the change if what you have assumed doesn't match what they have. Your assumption for each dropdown would be in the Default value for the dropdown.
The mapped value for each dropdown would be the name and id of each of the options for let's say roof...
select * from roof where property = 'Clementine Beach' this would give you group of values that contain the ID in the db and the description ("Pitched, 75 mm loft insulation")
You would get all of the ids for that record and its description and that is what would populate the description dropdown.
You would do the same from all dropdowns based on the property....
Or maybe I am way off and just don't understand the architecture you're looking for....but happy to continue to help.

I think you're pretty on the money! - There are approximately 200 values that need to be associated to approximately 20 IDs

You would do the same from all drop downs based on the property.... - Yeah each property will have about 6-7 values for the user to confirm

I'm pretty much trying to recreate this: https://switchedonportsmouth.co.uk/switchedonhomes/?address=92,%20NICKLEBY%20HOUSE,%20ALL%20SAINTS%20ROAD,%20PORTSMOUTH,%20PO1%204EN

OK makes sense. I would just get the data into a DB first and make sure that you have all the data you need related to the appropriate tables and then work on one query at a time. And slowly build out the form so that the data is simply populated in all of the drop downs. Then start building queries where your assumptions can be added to each default value of the drop down itself.
Eventually you'll add queries to insert the customer changes to another table or update to your "assumption" table....
I have found that by just focusing on the laying the groundwork (Pun sort of intended) it makes it easier to go back and start making more complex queries and chaining queries together as well. It also makes for a more challenging but fun learning experience. Let me know if there is anything else I can do to assist. Or just keep on posting as you need to as the people in this forum are always willing to lend a hand...best of luck and keep plugging away; you will get there.

I've got a similar problem where I'm simply trying to display the actual "Label" value in the drop-down from a query (see final screenshot). In other words, the value on the database is a 2, so I would like to show "2 Interviewers" in the drop-down that the user would see. On the screen that has the drop-down, I run my query on the "Visible" event:

image

If I run this query right now it shows:

image

image

The NoOfInterviewers as 2. For the default value of the drop-down I have put this code in:

image

I then tried adding the .ToString method and now it says:

image

toString();

1 Like

Duh, thanks!!!

1 Like

It happens to all of us! Glad it worked!