A lookup adds extra information to your data. Your raw data usually arrives with only the basics, such as an account number and an amount. A lookup is a reference list that tells Datarails what each of those items means, so your reports can group, total and drill down in the way your business thinks about them.
If you've used VLOOKUP in Excel, it's the same idea. Datarails finds each item from your data in the reference list and brings back the columns that go with it.
A simple example
Your general ledger export has an account number on every line, but nothing that says which line of the P&L that account belongs to.
| Account ID | Amount |
|---|---|
| 4000 | 120,000 |
| 6100 | 18,500 |
Your chart of accounts is the reference list. It has the same account numbers, plus a description of each one.
| Account ID | Account group | P&L line |
|---|---|---|
| 4000 | Product sales | Revenue |
| 6100 | Marketing | Operating expenses |
When you connect the chart of accounts as a lookup, Datarails matches the account numbers and adds the extra columns to your data.
| Account ID | Amount | Account group | P&L line |
|---|---|---|---|
| 4000 | 120,000 | Product sales | Revenue |
| 6100 | 18,500 | Marketing | Operating expenses |
You can now build a report by P&L line, and drill down from Revenue to Product sales to the individual accounts.
Why use lookups
| What you want | How a lookup helps |
|---|---|
| Group accounts into a hierarchy | Connect your chart of accounts to your general ledger. Reports and dashboards can then roll accounts up into groups and drill back down. |
| Combine two sets of data | Connect an employee details file to a monthly salaries file using the employee ID they share. Each salary line picks up the employee's department, location and so on. |
| Convert currencies | Connect an exchange rates file, so amounts in different currencies are converted and totaled in one currency. |
The words you'll see
| Term | What it means |
|---|---|
| Lookup | The reference list, such as a chart of accounts. Each lookup lives in its own Filebox under Setup > Lookups. |
| Table | Where your data is stored in Datarails. A lookup is connected to a table. |
| Key field | The column that appears in both your data and the lookup, and is used to match them. In the example above, it's Account ID. |
| Lookup connection | The link between one lookup and one table, including which key fields to match on. |
| Unmapped item | A value in your data that isn't in the lookup yet, so Datarails doesn't know what it means. |
Key fields: how the match is made
Datarails matches your data to the lookup using one or more key fields. For the match to work, the values must be the same in both places. Account 4000 in your data must be 4000 in the lookup.
You can use more than one key field when one isn't enough to identify a line. For example, if two entities use the same account numbers for different things, match on both Account ID and Entity.
Unmapped items: when your data has something new
An unmapped item is a value in your data that the lookup doesn't recognize. The most common cause is something new. For example, this month's general ledger includes an account that was opened last week, and it isn't in your chart of accounts yet.
When this happens, Datarails adds the new value to the lookup for you and flags it. You then fill in the missing details, such as which account group and P&L line the new account belongs to.
Why it matters: Until an unmapped item is filled in, its amounts can't be placed in the right group, so they may be missing from your totals or show up under a blank heading. Check for unmapped items after each upload.
See Lookups: Unmapped Items for how you're notified and how to map them.
Find your lookups
-
In the left navigation, open Setup and click Lookups.
-
The Lookups page lists each lookup, how many unmapped items it has, and when it was last updated.
Two kinds of lookup
There are two ways to connect a lookup. Most people start with a Filebox lookup.
| Filebox lookup | Table lookup | |
|---|---|---|
| What it connects | A reference file you upload, to a table. | One table to another table. |
| Use it for | Smaller reference lists you maintain by hand, such as a chart of accounts. | Large sets of data that are already in Datarails, such as employee details. |
| Example | Connect a chart of accounts to your general ledger by Account ID. | Connect employee details to monthly salaries by Employee ID. |
| Where the key fields must be | In the first column or columns of the file. | Anywhere. |
| Flags unmapped items | Yes. | No. You can keep an eye on them with a dashboard instead. |
| Where you set it up | Setup > Lookups, or Setup > Tables. | Setup > Tables. |
To see every lookup connected to a table, go to Setup > Tables. Filebox lookups are marked "Filebox" and table lookups are marked "Table".
For setup steps, see Lookups: Filebox Lookup and Lookups: Table Lookup.
Match many values with one row
Normally, each row in a lookup matches one exact value. Sometimes that would mean a very long list. For example, if accounts 1000 to 1500 are all "Current assets", you don't want 500 separate rows.
Instead, you can type a rule in the key column of your lookup file. One row then covers every value that fits the rule.
| Type this in the key column | It matches | Example |
|---|---|---|
DR_All() |
Everything. | Give every account the same value. |
DR_Range(1000, 1500) |
Numbers or dates from the first value to the second. | Accounts 1000 to 1500. |
DR_Range(1000, ) |
The first value and everything above it. | Accounts 1000 and up. |
DR_Range(, 1000) |
Everything up to the value. | Accounts up to 1000. |
DR_Pattern(my*key*) |
Text that fits the pattern, where * stands for any characters. Capital letters must match. |
DR_Pattern(Travel*) matches "Travel - Flights" but not "travel - flights". |
DR_IPattern(my*key*) |
The same, but capital letters don't matter. |
DR_IPattern(Travel*) matches both. |
Rules are also useful when the same key appears more than once in the lookup, because they let you say which row should apply.
© Datarails Ltd. All rights reserved.
Updated