Use Database Query to Populate Dropdown List

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.)

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:


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!!!

Hi @simonhuber, welcome to the community!

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

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:

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:


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.

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!