Retool

Prerequisites#

• Basic SQL
• Contact IT Support to grant you an access to TSP’s Retool.

Preparation#

1. Retool database

• Navigate to the Database tab.

There are two available tables: users & users_login. We are going to use users table for our exercises

Learn More: Retool Database quickstart

2. Exercises folder and app name.

1. Click the Retool logo in the top left to go back to the Apps tab.

2. Go to Exercises folder.

3. Create your apps (for exercises) in this folder.

4. Your app name should follow this format: [Your Name Test App] App Name.

   For example [Hatta Test App] Users Management

Create App#

1. Retool apps quickstart
2. Build and generate input forms
3. Building Your First App: Retool Product Walkthrough (2022)

Exercise I: Users Management#

1. Create an app

Click the Retool logo in the top left to go back to the Apps tab. Go to Exercises folder. Click Create new > App to create an app. Name the app and then click Create app.

2. Customize layout.

• Once the app window appears, Click Customize Layout in the top left. Click Horizontal Split to place your query editor to the bottom.

3. Display data in a table.

• By default, Retool adds a sample query to your app, query1, for you to get started. Select the query and change the Resource to Retool Database. In the query editor, change the default query to select * from users; and then click Save & Run.

• The data in the table will be automatically changed. It reflects the data from users table.
• Rename the query1 to getUsers.

4. Add search to the app.

• Drag a Text Input component onto the canvas. Rename it to userFilter and change its Label to Search users. Then, update getUsers with the code below. Make sure to save and run the query again.

select * from users WHERE name ILIKE {{'%' + userFilter.value + '%'}} ORDER BY name;

This uses the same base query but adds some filtering based on the text in the search bar and can support wildcards. When you type something in the search bar, the table updates automatically.

• Click Toggle preview mode in the top right to preview and test your app.

• Drag a form component onto the canvas.

• Click Generate form. Uncheck Required checkbox for id and is_enabled.Change input type for role to Select.Exclude is_deleted field.

• Disable text input id. As id is primary key, so it cannot be changed.

• Restrict the value of Role to Admin, Editor, and Vcusiewer.

• Create a new Resource Query to update the user. Set the name of the query to updateUser. The query as below.

UPDATE users 
SET name={{form1.data.name}}, 
   email={{form1.data.email}}, 
   signup_date={{form1.data.signup_date}}, 
   role={{form1.data.role}}, 
   is_enabled={{form1.data.is_enabled}} 
WHERE id={{form1.data.id}};

• Refresh the table after submission.

  • Add a new event handler for a Success event.
  • Select Trigger query as the action to perform.
  • Select getUsers.
  
  

• Add event handler to Submit button. The updateUser query will only be run if any record is selected.

• Test your app by clicking Toggle preview mode in the top right. Navigate to Database tab to make sure your changes has been successfully applied to the database.

• Create a new Resource Query to add new user. Set the name of the query to addUser. The query as below.

INSERT INTO users (id, name, email, signup_date, role, is_enabled, is_deleted)
VALUES ((SELECT MAX(id)+1 FROM users), {{form1.data.name}}, {{form1.data.email}}, {{form1.data.signup_date}}, {{form1.data.role}}, {{form1.data.is_enabled}}, FALSE);

• Refresh the table after submission.
  • Add a new event handler for a Success event.
  • Select Trigger query as the action to perform.
  • Select getUsers.

• Add event handler to Submit button. The addUser query will only be run if there is no record selected. If any record is selected, it means the form is in the Edit mode.

• Create new button to clear the table’s row selection. It also will clear the form.

• Test your app by clicking Toggle preview mode in the top right. Navigate to Database tab to make sure that the new record has been successfully inserted to the database.

8. Exercise: Delete User

1. This delete functionality is not for deleting the selected record, but only mark is_deleted field to TRUE.

2. Create new query to update the value of is_deleted to TRUE. Set the name of the query to deleteUser.This query only update the selected record.

3. Create new button next to Add New User button. Set the text to Delete User. Change the background color to red. Add event handler to run deleteUser query.

4. Update the query of getUsers to select only the record that the value of is_deleted column equals to FALSE. So, the table only shows undeleted users.

5. Once the delete button is clicked, the record should be disappeared from the table.