Consider the following scenario: a user is presented with a dropdown where they can select from a predefined list of database resources (all on different DB hosts, however all of them are MS SQL Server). For a given selection, I would like the app to display the list of schemas available in that database. From a SQL standpoint, this can be easily accomplished by something like this:
SELECT name as schema_name
FROM sys.schemas;
However, how can I set up my query so that the Resource part of the query configuration is passed in dynamically based on the value selected in the dropdown in the app's main screen?
Hi @weard Thanks for reaching out and welcome to the community! We have a beta feature for this called decoupled queries if you want to check it out! I've just enabled it on your license key ending in 154923a. It will be available when you restart your containers. If you are using a different license key, let me know.
In short, it adds a little fx button next to the resource name in the query editor, and provides an input where you can define an expression that controls which resource to use.
Getting your resource IDs is a little clunky, but if you select a resource and switch to a decoupled query (by clicking the fx), Retool will populate the resource ID as a placeholder, so you can copy it for each resource and then write your expression:
That worked. However, it seems that I am expected to enter the GUID of the resource:
while in your example you seem to be referencing them by name, which is much handier. If there is an API that I can use to retrieve the ID of the resource by its name, could you please help me find it?
Hi @weard Thanks for pointing that out. Unfortunately, mine only looks like that because these resources are very old. We have since switched to GUIDs, which is now the only option. I will share that feedback internally though!
Thanks, @Tess! So -- to confirm -- no way to get the GUID from name? I only ask because the tip (see screenshot above) says "Retrieve the resource ID ... programmatically with the Retool API". So I am wondering which API is that.
That particular field requires the id, rather than the name of the resource. However, if you want to use the resource names within your app and use some logic to convert to the ids for this field, you might consider adding your adding your on-prem Retool instances backing database as a resource within your organization, as described in this Retool community post. You could then query the resources table within your apps as needed.
Our use case is similar, in that we have same databases in different AWS datacenters and want to parameterize the resource ID in order to select the resource from each datacenter.
Would it be possible to enable the beta for us as well?
Hi @fmalotaux Yes, I can add it for you! I'm having trouble locating your account though. Can you share the email of the Retool billing admin (either here or via direct message)? Thanks!
