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. 

In Datarails, there are two types of lookup connections: Filebox Lookup and Table Lookup. Each has different requirements and capabilities. To understand the Filebox Lookup, start by reviewing the Lookups Overview article.

Create Filebox Lookup

You can create a Filebox Lookup from the Lookups folder or within the Tables section. The main difference is that you can upload and connect a new file in the Lookups folder, whereas in the Tables section, you can only connect an existing lookup file. Therefore, you must first upload a lookup file to the Lookups folder.

From the Lookups Folder

1. Go to the Lookups folder under the configuration section in the left side panel.
2. Click on 'New +' and choose 'New Lookups'. To add a new folder, select 'New Folder' first. 
3. In the 'Create New Lookups' window, enter the new Lookup name, such as 'Chart of Accounts'. 
4. Drag and drop your file or browse to select the file. 
5. Click 'Create'. The new lookup file will be added to the Lookups folder.
6. The 'Add lookup connection' window will open. Insert the relevant details: select the Target table to which you want to connect the Lookup, select the Sheet name, and choose the Key length according to the number of columns you want to use.

7. The 'Fill unmapped items' option is enabled by default. This ensures that any items in your data without a corresponding mapping in your lookup file are automatically added. If you prefer to exclude these unmapped items, untick the 'Fill unmapped items' box. This way, only items with predefined mappings will be included in your lookup file.

8. Click 'Save'. Saving the lookup configuration will trigger a Table rescan. 
9. Add as many lookup configurations as needed by repeating this process. 

From the Tables Section

To create a new Filebox lookup configuration from the Tables section, you must first upload the Filebox file to the Lookups folder.

1. Go to the Tables section in the left side panel.
2. Choose the Table to which you want to connect the Filebox Lookup. 
3. Click on the three dots ellipsis icon and select 'Table lookups'.
4. In the 'Table lookups' window, click '+ Add New'.
5. In the 'Add lookup connection' window, insert the relevant details: Keep the Source type, select the Source Filebox you want to connect to the Table, select the Sheet name, and choose the Key length according to the number of columns you want to use.
6. The 'Fill unmapped items' option is enabled by default. This ensures that any items in your data without a corresponding mapping in your lookup file are automatically added. If you prefer to exclude these unmapped items, untick the 'Fill unmapped items' box. This way, only items with predefined mappings will be included in your lookup file.
7. Click 'Save'. Saving the lookup configuration will trigger a Table rescan. 
8. Add as many lookup configurations as needed by repeating this process. 

Edit existing Filebox Lookup

You can edit an existing Filebox lookup configuration from the Lookups folder or within the Tables section.

From the Lookups Folder

1. Go to the Lookups folder under the Configuration section in the left side panel.
2. Choose the Lookup you want to edit, click on the three dots ellipsis icon and select 'Lookup Configuration'.
3. In the 'Lookup Configuration' window, you can Click '+ Add New' to add a new lookup configuration. For existing lookup configuration, you can click the trash icon to delete an existing lookup configuration or click the pencil icon to edit the lookup configuration details.
4. Clicking the pencil icon will open the 'Edit lookup connection' window, where you can change the Target table to which you want to connect the Lookup, update the Sheet name, choose a new Key length according to the number of columns you want to use, or change the 'Fill unmapped items' option. 
5. Click 'Save'. Saving the lookup configuration will trigger a Table rescan. 

From the Tables Section

1. Go to the Tables section in the left side panel.
2. Choose the Table for which you want to edit the lookup configuration.
3. Click on the three dots ellipsis icon and select 'Table lookups'.
4. In the 'Table lookups' window, you can Click '+ Add New' to add a new lookup configuration. For existing lookup configuration, you can click the trash icon to delete an existing lookup configuration or click the pencil icon to edit the lookup configuration details.
5. Clicking the pencil icon will open the 'Edit lookup connection' window where you can change the Source Filebox, select a different Sheet name, choose a new Key length according to the number of columns you want to use, or change the 'Fill unmapped items' option. 
6. Click 'Save'. Saving the lookup configuration will trigger a Table rescan.

Update/Edit Lookup data

You can add new items, update existing ones, or map new unmapped items in your lookup files. There are two ways to update your lookup file: download and edit it using the Datarails Excel add-in or the Online Update tool to apply changes directly online.

You can quickly identify unmapped items in your lookup files through various indicators in the environment, including the Home Page and workflows. Explore further to learn how to recognize unmapped items.

Lookup configurations are based on Key and Sheet names. If you change either of these, ensure you edit the lookup configuration to match the changes. 

Via the Excel add-in

Learn more about Excel add-in. 

1. Go to the Lookups folder under the Configuration section in the left side panel. 
2. Choose the Lookup you wish to edit, hover over it, and click on the 'Download' button, or click on the three dots ellipsis icon and select 'Download'.
3. Open the downloaded file and ensure you are connected to your environment via the Excel add-in.
4. Make the needed changes. Update values, add new items by inserting new rows, add new columns, and change fields or sheet names. 
   All unmapped items, if any, will be added at the bottom of the lookup file.
5. If you change a key field name or sheet name, adjust the lookup configuration accordingly.
6. Upload or submit the file to the relevant Filebox. 

Online Update

1. Go to the Lookups folder under the Configuration section in the left side panel.
2. Choose the Lookup you wish to edit and click the 'Update' button when you hover over the Lookup.
3. The 'Update Mapping' window opens with the 'View unmapped items only' toggle enabled, showing all unmapped items. To see all lookup items, disable the 'View unmapped items only' toggle.
4. Make the needed changes. Change values by choosing from the existing values list or adding new ones, adding new items using the + icon, and changing field names.
   All unmapped items, if any, will be added at the bottom of the lookup file.
5. If you change a key field name, adjust the lookup configuration accordingly.
6. Click 'Submit'. If not all unmapped items were updated, a message will pop up, indicating you can either save changes without addressing the unmapped items or go update the missing items.

Updated July 07, 2024 12:04