A table lookup connects two tables in Datarails, so one can borrow columns from the other. For example, your general ledger is in a table called Financials, and your chart of accounts is in a table called COA. A table lookup matches them on the account number and adds the account group and P&L line to every ledger line.
If lookups are new to you, start with Lookups overview. It explains what a lookup is and how a table lookup differs from a Filebox lookup.
When to use a table lookup
- The reference data is already in Datarails as a table, or is too large to keep as a simple reference file.
- You want to join two sets of data that share a column, such as employee details and monthly salaries that both have an Employee ID.
Table lookups don't flag unmapped items. If a value in your data has no match in the source table, Datarails doesn't notify you. To be told about new values, use a Filebox lookup instead.
Before you start
- Both sets of data must already be in tables, and in different tables. In the example, the general ledger is mapped to the Financials table and the chart of accounts to the COA table. See Creating a new Data Mapper.
- The two tables need a column in common. This is the key that Datarails matches on, such as Account ID.
The words you'll see
| Term | What it means | In the example |
|---|---|---|
| Target table | The table that receives the extra columns. | Financials |
| Source table | The table the extra columns come from. | COA |
| Keys | The column or columns used to match the two tables. | Account ID |
| Fields to be added to target table | The columns to bring across. | Account Group, P&L Line |
Create a table lookup
-
In the left navigation, open Setup and click Tables.
-
Find the target table, the one you want to add columns to. Click the ⋮ menu at the end of its row and select Table lookups.
-
In the Table lookups window, click + New.
-
In the Add lookup connection window, change Source type to Table.
-
Under Source name, select the source table.
-
The source table's columns appear under Source table fields. Use the search box if the list is long.
-
Drag the column to match on into Keys. To match on more than one column, drag each of them in.
-
Drag each column you want to bring across into Fields to be added to target table.
-
Click Save.
The lookup appears in the Table lookups window, and Datarails rescans the target table so that the new columns are filled in.
Saving starts a rescan. Every time you save a lookup, the target table is rescanned. On a large table this can take a while, so set up all the keys and fields before you save.
To add another lookup to the same table, click + New again and repeat the steps.
Edit or delete a table lookup
- Go to Setup > Tables. Click the ⋮ menu on the target table's row and select Table lookups.
-
Find the lookup in the list. Click the pencil icon to edit it, or the trash icon to delete it.
-
In the Edit lookup connection window, change what you need: choose a different source table, or drag columns into or out of Keys and Fields to be added to target table.
- Click Save. The target table is rescanned.
© Datarails Ltd. All rights reserved.
Updated