Use Database Query to Populate Dropdown List

Queries and Resources
,
1

Hi Retool Community,

I am trying to use data in a database to populate dropdown lists on a form I am creating and I have a couple of issues.

When I convert the DB query from an object to an array using the FormatDataAsArray function I end up with an array that consists of a key-value pair.

The key-value pair is as follows

[{"customer_info_data":"Head Office"},{"customer_info_data":"Remote Office"},{"customer_info_data":"Data Centre"},{"customer_info_data":"Other"}]

My first thought was to use JS to further manipulate the data and while I have this working on a standard HTML page I cannot work out how to use that in Retool, I've included a screenshot of my JS and also how I am trying to retrieve that in Retool (see the red circle in the image.)

retool 2
retool 22306×1317 189 KB

My next thought was to move the project forward and just explicitly call each value from the key-value pair, so in the Values box I added the following:

[{{Location_Type.data[0].customer_info_data}},{{Location_Type.data[1].customer_info_data}},{{Location_Type.data[2].customer_info_data}},{{Location_Type.data[3].customer_info_data}}]

While that looks OK when I test the page and try to use those values in a SQL insert query it seems extra apostrophes (') and whitespace are added. Below is the insert query from the SQL logs.

insert into customers (cust_name, cust_loc_type, cust_loc_seats, cust_loc_util) values ( 'Test Customer' , ' 'Head Office' ', ' '500-999' ', 'Med-High')

I'm at a bit of a loss on how to move forward now so any help with this would be greatly appreciated. Also, assume no prior knowledge of JS, Retool and generally the whole thing!!!

2

Hi @simonhuber, welcome to the community!

With the first one, Query11 is: SELECT DISTINCT name FROM events.

selectInputList
selectInputList769×409 22.3 KB

This way, you won't need that JS code. If data is an array of object, check your left panel (CMD+B) and look at rawData property of your query. rawData is usually object.array format.

As for the extra apostrophes, I've encountered that one inserting data to bigquery datasets. I did the cleaning in bigquery since I was stuck on this end on retool. :pray:

3

Hey Jocen,

Thanks very much for your feedback. Adding DISTINCT to the SQL query didn't actually fix the problem but something else you pointed to do. I changed my VALUE query to:

{{Location_Type.rawData.customer_info_data}}

and that fixed the problem. I'm not sure if that's the most elegant way of doing it and hopefully, it won't cause issues down the road.

Unfortunately, I still get the SQL error when trying to submit the form data to the database.

insert into customers (cust_name, cust_loc_type, cust_loc_seats, cust_loc_util) values ( 'Test Customer 2' , ' 'Head Office' ', ' '500-999' ', 'Med-High')

I'm not sure Big Query is going to be able to help me here as this is all hosted locally as I am just trying to build a proof of concept right now.

Thanks so much for your help.

4

I just figured out the extra ' and whitespace was coming from the insert query I had made. It still had the ' around the {{ }} statement so was adding extras in.

All sorted!