How to map a value in a Table?

💬 App Building
1

Hi there,

I have to sql tables.

Table A has a column order_types (integer)
Table B has id (integer) and name (text)

When rendering table A in Retool, I want the values of order_types shown as the corresponding name of table B where id = order_types of Table A

What is the most efficient way to accomplish this in Retool?

Thank you very much in advance, I'm new to Retool and I'm still having lots of problems understanding it's basic principles :slight_smile:

2

Hey @Robert84 ,

You can achieve this in Retool using a SQL JOIN to retrieve data from both tables. Here’s an example:

SELECT A.*, B.name AS order_type_name
FROM TableA A
LEFT JOIN TableB B ON A.order_types = B.id

This query links TableA and TableB based on the order_types column, ensuring you fetch the corresponding order_type_name.

3 Likes
3

Hi @Robert84

Let me share a proper method to do this. To future-proof your app for potential CRUD functionality on TableA, follow these steps:

  1. Write a query to fetch TableA data: Create an SQL query (getA) that selects all rows from TableA. Do not join with TableB.
  2. Write a query to fetch TableB data: Similarly, write an SQL query (getB) that selects all rows from TableB.
  3. Add a Table component: In your Retool app, add a Table component and set its data source to getA (table1).
  4. Configure the 'order_types' column: Open the inspector for table1, select the 'order_types' column, and configure it.
  5. Format as "Tag": In the column settings, change the format of 'order_types' to "Tag". This will automatically add an "Add on" called "Options List."
  6. Configure the "Options List": Open the "Options List" settings.
  7. Set data source for mapping: Under the "Content" section, select "Mapped" and set the Data source to getB.
  8. Map the options correctly: In the "Mapped options" section, ensure that the value is set to {{item.id}} and the label is set to {{item.name}}.

You'll start seeing order_types.name in the place of order_types.id. But the important thing to realize is that the name of order_types is mapped on top of ID only at the app-level.

This approach ensures table1 accurately reflects the structure of TableA, allowing you to easily implement CRUD functionality on this table in the future.

Hope this helps,
Abdul Wasae
Toolshed - Hire Retool Developers

1 Like