Foreign Key Column Display

[haj]

In the Retool Database resource, is it possible to display a column from a related table other than the foreign key? I'm thinking of something like a Select UI component which has a value and a label. The value is the foreign key, but the label is what's displayed in the view of the database table. Thanks.

[AnsonHwang]

You could use JOIN keyword to join two tables in SQL. Retool Database is based on POSTGRES

[haj]

Thank you, but your response does not address my question. My question refers to the way that the Retool Database UI displays the foreign key in a foreign key field. I'm suggesting that the value displayed be configurable as another column from the foreign key table.

[mbruijnpff]

@haj , can you give an example? Foreign keys are meant to secure data integrity, not to display data. To connect data from multiple tables to show it, you’ll need joins as @AnsonHwang mentioned

[haj]

Thank you. I'm not expressing my idea well. Let me try again.

Imagine this very simple table in the Retool Database.

Imagine that I want to add data in this UI.

I now need to add the foreign key from another table. How do I find that foreign key?

1. I can switch to the related table, manually find the record I want to link, copy the primary key, switch back to the first table, and paste in the foreign key.

2. Wouldn't it be better if (a) the UI presented a Select style dropdown with search (b) that had search-ahead capability (c) on a column in the related table (not the primary key) and (d) that inserted the foreign key into the table column when the user clicks or presses enter?

So, suppose that the foreign_key column above linked to a table of street names. I'd like to be able to start typing a street name and then click or press enter when the street name I want appears in the select / search box.

Then, in the first table, I'd like the choice to see either the foreign key OR the street name.

Thanks.

[joeBumbaca]

Hey @haj! THis is definitely doable in Retool. I set this up with an example similar to your very simple table

Grab the rows from the table you want to search:

Use that data to populate a select component, passing the id as the value and the searchable column as the label.

You can then search in the select component to find the value you want.

Insert that data into the second table with the current item's id as the foreign key

Let me know if you have any other questions!

[haj]

I am trying to suggest a feature for foreign key columns in the Retool Database view. Instead of showing the foreign key, add a setting to the foreign key column that allows for picking a different column to display instead of the foreign key itself.

[joeBumbaca]

Got it! Created a feature request internally. Can update you here as that gets any movement. Thanks!

[Josh_Zabec]

ok so I may be dealing with something similar. I have a table customer_entry and a table job_entry. I have a table and a last name search, id like to be able to select the customer and create a job entry with the ability thru job_entry to display particulars of that customer in the job_entry json form page.

[vinnie]

Pocketbase does this, would also love to see it in Retool.

Here I have an expense table that references a conferences table on conference_id. All I had to do was mark the field conference_name as what Pocketbase calls presentable, and it can be enabled to show instead of conference_id when adding new records - super helpful.