Form using JSON Schema with dropdown values from Retool Database

App Building
,
1

I want to create a form using JSON schema (Lets say: Add banking details of employee). This schema references to another JSON schema (eg. employee) for which there is another form/app. On creating employee via Retool app an enry is created in Retool Database.

My requirement is that my form to enter Banking Details should let me choose Employee ID from the retool Database.

I could not find how to do this, but I can think of 2 ways:

  1. After form load, I run a JS which fetches employees ID data from Retool table and then updates the dropdown values list in the form. I tried doing it but could not figure out which method would changed dropdown data.
  2. I create JSON schema for via Javascript and it defines dropdown as enum. This enum is created after fetching employees ID values from Retool Table.

Would really appreciate any help.

PS: It's my first few hours with Retool, I might be asking something that's quite straight forward.

2

Hi Shruti,

I think what you're trying to do can be done with a combination of Select drop down with a SQL list created based on your entry.

For example, let's say in the form you select Bank A, and Bank A has a list of employees you want to be able to select the specific employee from. You create two Select drop downs:

Select Dropdown #1: Bank List <-- datasource: Retooldb query (getBanks) "Select * From banks" (returns bank_id, bank_name). Set the value of the dropdown to be bank_id

Select Dropdown #2: Employees List <-- datasource: Retooldb query getBankEmployees -> SQL "Select * From employees where bank_id = {{select1.value}}.

Then you can set the getBankEmployees SQL query to run every time the Select 1 changes.

In short, I don't think you need a Javascript function. Just set your select drop downs to be Mapped Values based on those queries. I know this example isn't exactly what you're looking for, but I think this is what you're going for.

Let me know if you'd like any help with this. :slight_smile:

3

Thanks @Nicholas_Canfield
This is what I am looking for but I do not have form component. I am instead using JSON Schema form. In JSON schema form, I could not find any way to update the list of possible values in drop down.

Example JSON Schema (just sharing relevant part of it):
"Key": {
"type": "array",
"items": {
"type": "string",
"enum": [ "val1", "val2" ] // This actually needs be populated dynamically from the query which hits Retool DB
}

4

Try to do a query and set the enum to your column in the query results like this:

"enum": {{ query1.data.account_name }}

Here's what it looks like:

image
image1177×629 70.3 KB

{
"title": "A registration form",
"description": "A simple form example.",
"type": "object",
"required": [
"username",
"password"
],
"properties": {
"username": {
"type": "string",
"title": "Username"
},
"password": {
"type": "string",
"title": "Password",
"minLength": 3
},
"birthday": {
"type": "string",
"title": "Birthday",
},
"names":{
"type": "array",
"items":
{
"type": "string",
"enum": {{ query1.data.account_name }}
}
}
}
}

5

But to be honest, I MUCH prefer using the other form type instead of JSON form schema. You can always turn a form data object into an array by formatDataAsArray(query.data).

6

It was that simple. :slight_smile:
Thank you so much! I prefer JSON schema form as my forms are a bit complex and this way it becomes easier and quicker.

1 Like
7

Hi Shruti,

Of course! If you need some more help, you can contact me at my lowcode tools automation company, Tropic Flare (https://tropicflare.com).