In Datarails, data is stored in tables, representing the database in the environment. Lookups provide structure, logic, and hierarchy to input data, acting as data mapping files that enhance your main database by connecting it with additional fields or categories from another data source. 

For example, a General Ledger input file can connect to a Chart of Accounts lookup to add more data layers.

Key Fields

The lookup function uses a Lookup Key field to link data from the main table with additional information from a Lookup table. Multiple Key Fields can be employed for these connections. It's essential to ensure that these Key Fields match corresponding keys in the main data table, enabling automatic retrieval of relevant fields from the lookup table.

Similar to Excel's VLOOKUP, the Lookup Key Field in Datarails fetches data based on specific criteria but supports multiple Key Fields for data retrieval. For instance, connecting a Chart of Accounts to a General Ledger input file can use 'Account ID' and 'Entity' as Key Fields to import additional fields into the database."

Watch this Datarails University video to learn more about the importance of Key fields in lookup configuration:

Unmapped items

Unmapped items are data entries in the raw data that lack a corresponding match in the lookup file. This happens when the lookup process fails to find a matching entry.

For example, if you import a monthly General Ledger and it includes new Account IDs, these IDs won't be found in the existing lookup file. Consequently, these new IDs are added to the lookup file for you to map into the database. Properly mapping these items is essential to maintain alignment with existing hierarchies.

Identifying unmapped items is crucial because they can indicate missing or incorrect data, which needs to be addressed to ensure data integrity and accuracy in reports or analysis.

Read more about unmapped item notifications and how to map unmapped items here.

Use Cases & Benefits

• Building Hierarchies
  If you have a monthly General Ledger and want to build hierarchies within your organization, enabling drill-down capabilities in your reports and dashboards, you can use lookups. Connect your Chart of Accounts based on 'Account Name' or other relevant fields to establish these hierarchies.
  
  
• Joining two separate databases
  When working with two datasets, such as a Monthly Salaries by Employee file and an Employee Data file, each mapped to different tables in the environment, you can use lookups to join them. Both tables contain the field 'Employee ID', which can serve as the Key Field for the lookup, effectively combining the two datasets.
  
  
• FX Rates
  Using lookups to connect an FX Rates conversion file to consolidate amounts from different currencies into USD. This allows you to automatically convert and consolidate financial data based on the specified exchange rates.

Filebox Lookup vs Table Lookup

In Datarails, there are two types of lookup connections: Filebox Lookup and Table Lookup. Each has different requirements and capabilities. You can see a list of all existing Filebox Lookups and Table Lookups in the Tables section, where Filebox Lookups are represented by 'Filebox' and Table Lookups by 'Table'. Explore the Filebox Lookup and Table Lookup.

Here is a comparison between the two:

Create Dynamic Lookup using smart functions

Creating a Dynamic Lookup using smart functions is beneficial for:

• Effectively managing and retrieving data within specified ranges for keys representing numbers or dates, facilitating time-based or sequential data handling.
• Supporting scenarios where a value applies to all items under the same key, ensuring contextually relevant data.
• Resolving conflicts from duplicate keys by applying intelligent logic to determine the appropriate records.

Dynamic Lookup Classification Rules

You can use Dynamic Lookup to classify your data based on the following smart functions:

• DR_All() - Returns all Results 
• DR.Range (RangeStart, RangeEnd) - Returns all Matches from RangeStart to RangeEnd (can include a range of dates or numbers)
  DR.Range (RangeStart, ) - Returns all matches greater than or equal to RangeStart (can include a range of dates or numbers)
  DR.Range (, RangeEnd) - Returns all matches lower than or equal RangeEnd (can include a range of dates or numbers)
• DR.Pattern(x*) - Returns all matches after x (case-sensitive).
• DR.Pattern(*y) - Returns all matches before y (case-insensitive).

Examples of smart functions 

Lookup Troubleshooting

Understanding how to resolve Lookup errors is essential for maintaining data management and data integrity. Datarails will display an error notification on both the platform's Home Page and the Lookup Filebox.

Troubleshooting from the Home Page

1. Navigate to the Home Page.
2. If the 'Data Status' section displays 'All looks prefect!,' there are no Lookup or Data Mapper issues. If it shows a red error icon with a number, there are Lookup or Data Mapper issues.
3. To review the errors, click on the 'Data Status' section.
4. In the Data Status window, you'll find a list of all errors. Lookup errors will appear under the 'Lookup errors' section.
5. Each error includes the Filebox Name, Number of errors within the Lookup, and Last update date.
6. To access the Lookups Folder, click on the Filebox name.
7. In the Lookups folder, the Filebox will appear with an error icon. clicking on it will take you to directly to the Lookup Configurations.

Troubleshooting from the Filebox

1. Navigate to the Lookups Folder and choose the relevant Filebox.
2. Check for a red 'Lookup error' icon next to the three dots ellipsis icon, which indicates a Lookup error.
3. Clicking on the 'Lookup error' icon will open the Lookup Configuration for this Filebox.
4. Hover over the red error icon to see the error description. click on it to open the 'Edit lookup connection' window.
5. All issues will be highlighted and marked in red.
6. For example, if sheet name doesn't match the Lookup Configuration, the sheet name will be marked in red with an error message: "Sheet name missing in file. Please select a valid sheet". To fix it, choose a different sheet name from existing options or upload a new Lookup file with the correct sheet name.

Updated July 24, 2024 00:22