Creating forms

Forms are one of the most common aspects of internal tools — they’re typically how your end-users will create or edit data. In this guide, we’ll cover how to build a simple form, insert data into a database, add the ability to edit existing data, and validate form inputs.

📘

Form or JSON Schema Form?

This page covers building a form using the Form component. You can also use the JSON Schema Form component to create a form in Retool. While the functionality of these two components overlaps quite a bit, there are pros and cons to using each. If you’re not sure which form component to use, see Choosing a form component.


1. Building the form

Start by dragging a Form component from the right-hand side component library onto the canvas:

You’ll notice that the form is empty — it doesn't come with any inputs by default. Thankfully, Retool can automatically generate the fields for you based on the schema of a SQL database.

🚧

Building forms for non-SQL resources

The form generator functionality currently only works for a subset of SQL databases (PostgreSQL, mySQL, and MS SQL), as they allow Retool to easily detect the table schema. If you're using a different resource type (like an API or NoSQL database), you'll need to drag individual fields (text inputs, dropdowns, multiselects, etc.) into the Form component, then create a write query to run on form submission. See Integrations overview for more detailed information on how to write to different resources.

With the Form element selected, click on the “Generate fields from a database” link in the right-hand side inspector and you’ll see a modal window appear:

In this modal, select your SQL database resource and a table from the dropdown menus. You'll then see a list of the available columns in the table, the form input you'd like to map each column to, and the type of validation to use on that input. You can also press the × symbol to exclude the column from appearing in your form, or drag the rows to re-arrange their order.

In this example, we'll put the first_name field before the last_name field and change the validation type for the email column:

Click the Generate form button and you'll see the correct fields appear in your Form container:

2. Inserting a new record into the database

You'll also notice that the form generator automatically created an insert query, in this case called form1_insert_into_customers. This query has a changeset with all of the individual form inputs mapped to their respective columns in the database:

At this point, you can fill out your form, press the submit button, and a new record will be inserted in your database. But let's make one change to this example before you do that.

Instead of requiring the user fill in the id field, let's remove it and increment it automatically. (Note: your SQL table may be configured to auto-increment IDs already!)

Select the textinput1 element in the form—the id input—and press delete, then re-arrange the other elements a bit:

In the query editor panel at the bottom of the screen, select the form1_insert_into_customers query and modify the changeset value for id from {{textinput1.value}} to {{ Math.max(…query1.data.id) + 1 }}.

Notice that we’re now referencing another query, query1, in the id value. That’s a simple SQL query which selects all of the rows in our table. To auto-increment the id when adding a new row via this form, we’re using the query1.data.id value to return all of the ids in the table. We then find the maximum value in that array and add 1 to it.

To ensure that you always have the most up to date list of ids from query1, change the form's On Form Submit property to run query1 first:

Then set query1 to trigger form1_insert_into_customers on success:

Now try submitting some data via the form. Success!

3. Clearing form on submit

You may have noticed that while the form submission was successful, the data entered remains in the form. On a successful submit, the form should probably be cleared so the salesperson can enter another customer. To do this, you can simply enable the “Clear after submission” toggle in the JSON Schema Form properties:

4. Pre-populating the form and updating records

We now have a working form, but it’d be nice if the user could also see all of the existing customers. It would help them confirm when they’ve successfully added a new customer and provide the ability to make changes to existing customer data.

You can make this happen with some small changes to the existing form. Drag in a table component and hook it up to a new query which selects all of the rows in the customer table:

The next step is to use the selected row of the table to populate the data for the form. Select each of the form inputs and set the Default value property in the right hand inspector to the corresponding entry in the {{table1.selectedRow.data}} object. You can use a ternary here to pass the selected row data only when present.

As an example, for first_name you would enter {{table1.selectedRow.data ? table1.selectedRow.data.first_name : null}}:

Now the form is automatically populated with the values from the currently selected row in the table. If you submit the form in this state, however, you'll be inserting a new record, not updating the existing selected row. To fix this, create a new query of a different action type: Bulk upsert via primary key.

This query uses id as the primary key column. If the id matches an existing row, the data in that row will be updated. If the id doesn’t match, a new customer record will be inserted.

Instead of manually building out the changeset as we did earlier, with a bulk query we can simply pass in {{ [form1.data] }}, which is an array that contains all of the form’s data. You'll notice that the input names (e.g. textinput2) don't match the column names in the database, however. Rename the individual inputs in the form to match the database columns...

...and then re-enter the {{ [form1.data] }} value in the query's Array of records to update property and the evaluation should show like this:

Next, change the form component's On Form Submit property to use this new query:

There’s one more thing you’ll need to setup before running this query. You’ll remember that we previously created a little hack to auto-increment the id when submitting the form. Now, you'll need to either pass the selected table row’s id or auto-increment a new id, depending on whether the user is adding a new customer or modifying an existing one.

Add back in an id input to the form schema and set its Hide when true property to {{ true}}, so it’s not visible to the end-user:

Next, you'll want to add a ternary operator to the id input's Default value field to check if there’s a selected row in the table. If so, we’ll use all of that row’s id to populate the input. If not, we’ll populate the hidden id field with the incrementing expression:

A couple small things before we wrap up. Enable the “Clear selection” toggle on the Table component, so the user can deselect a row and clear the form when they want to enter a new customer. And you'll want to configure query3 to run again after the write query finishes, so the table updates to show the latest data after the form submission.

Now, you can try editing an existing customer and inserting a new one. Success!

5. Validation

Last but not least, let’s look at how you can enable some basic validation on your form. When you originally generated the form, you set the email field's validation type. You may have noticed that the field already throws a validation error when you type in an incorrect email address:

That's a good start, but all of the fields in this form should also be required, so the database doesn't end up with incomplete customer records.

To do that, you'll just need to click on each form field and toggle the Required field property and double-check that the Form container's Validate form on submit property is toggled on:

That's it! You've now got a fully working form for adding and updating rows in your database.


Did this page help you?