Dropdown list not populating results in list box input

App Building
1

I'm stuck and can't figure out how to get a SQL Query from the Retool DB to display results in a listbox dropdown. At one point I had it displaying, but could not get it to pass the value into the current state.

I continually get an empty array error, so I've tried transforming the results, but the query stops returning any results.

Screenshot 2024-02-22 at 11.51.01 AM
Screenshot 2024-02-22 at 11.51.01 AM1920×1123 162 KB

Here is the query

SELECT 
    'cu' || id AS record_id, -- Prepends 'cu' to the credit union ID
    credit_union_name AS institution_name, 
    'Credit Union' AS institution_type
FROM 
    "credit_unions"
WHERE 
    credit_union_name LIKE '%' || {{IssuerSelect.value}} || '%'

UNION ALL

SELECT 
    'bt' || id AS record_id, -- Prepends 'bt' to the banks-thrifts ID
    name AS institution_name, 
    'Bank/Thrift' AS institution_type
FROM 
    "banks-thrifts"
WHERE 
    name LIKE '%' || {{IssuerSelect.value}}|| '%';

Here is one of the transform results I've tried, which I get no error

tconst transformedResults = queryResults.map(result => {
    return {
        record_id: result.record_id, // Assuming `record_id` is already formatted as 'cuID' or 'btID'
        institution_name: result.institution_name, // Direct mapping
        institution_type: result.institution_type // Direct mapping
    };
});

return transformedResults;

If anyone has any suggestions I would appreciate it.
Thanks, Mike

2

It looks like you're using the component name instead of the query for the options list?

3

Thanks, I'm blind sometimes! :grinning:

1 Like
4

We've all been there at one point or another. :grin:

1 Like