Lookup table - is it even possible in retool?

App Building
1

i have two tables branches and invoices

branches table
branchesid
branchesname

invoices table
invoicesid
branchesid
invoicepath

in the invoices table, I want to show the branchesname instead of the branchesid and its seems impossible to me and I can't find an example of this anywhere online. I feel like this is something easy that im missing. This are my current attempts.

{{getBranchNames.data.branchname[getBranchNames.data.branchid.indexOf(currentRow.branchid)] || ''}}

{{getBranchNames.data.find(branch => branch.branchid === currentRow.branchid)?.branchname}}

I made a new column and i'm using these as the value.

2

Hey there @kauthon-retool, and welcome to the forum!

You can refer to this similar post.

You can use the js find function. So for example, in your invoices table, yo uselect your branchesid column, and within the mapped value field you can write something like {{ branchesTable.data.find (x => x.branchesid === item).branchesname }}

This is kind of a lookup. My recommendation would be to use the query as datasource, so instead of branchesTable.data, you would use query1.data (remember to format it as array, otherwise find function won't work). Another thing to bear in mind is that if your dataset does not contain any of the branchesid in your invoices table column, then it will also throw an error as find is expecting to find a match.

3

Thanks but I still can't get it to work. I feel like there needs to be a youtube video on this. Because it might seem simple but over the years this is something that keeps getting asked and not really answered in a way that is helping people.

here is a screenshot
https://d.pr/i/VLlWrv
Shared with Droplr

I'm getting this error
TypeError: branchLookup.data.find is not a function,TypeError: branchLookup.data.find is not a function,TypeError: branchLookup.data.find is not a function

4

That's because your data is not an array:

try (formatDataAsArray(branchLookUp.data).find.... etc etc

Or, what I always do, within your query go to transform results and write

return formatDataAsArray (data)

image
image844×194 7.99 KB

If you right click on the query ans select View State, you will see how the data will change after yo uapply that.

5

Thanks, I was able to get that to work and I'll study the parts I didn't understand.

I'll also work on making a youtube video in the next week, after I'm done this project, I think it'll help other users.

1 Like
6

Thanks @kauthon-retool,

As this came up a couple of times recently, and prompted by you I've created this video:

2 Likes