Lookup a table to lookup an item

AppSheet AppSheet Q&A
1

Hello,

I have a project where there are multiple items across different tables. Then I made a compiled table (Search) that contains all the item IDs and their corresponding table name. For this table I made an action that opens a scanner that scans an item number, then looks that number up in the compiled table, gets the referenced table name back and jumps to that table with that item ID. Formula below:

linktorow(lower([_THISROW].[ScanID]),lookup([_THISROW].[ScanID],“Search”,“SearchID”,“Table”))

This works, however now I need to do this double lookup without a linktorow, and I can’t seem to find out how to do this as it requires a dynamic dataset, and this is not allowed. Here’s the formula that doesn’t work:

lookup([ScanID],lookup([ScanID],“Search”,“SearchID”,“Table”),“ItemID”,“ItemName”)

2

What is that “Table” in your inner lookup? You have a column in the “Search” table with the column name as “Table”?

Lookup can only return a column.

Lookup (what, in which table, in which column of that table, what column to return when found)

Also, I noticed that you dropped the lower() expression in your double lookup.

3

Yes Table is a column with the names of the related tables.

I don’t necessarily need to use lookup, I just need a formula that allows me to pull the item name from various tables, which table to use is referenced in the “Search” table under the “Table” column.