Connect a sheet to a DataTable

Prerequisites

To use DataTable:

• Check that you have Owner or Admin permissions on any sheets where you want to build a DataTable connection.
• DataTable must be a premium application available for you.
• You must have DataTable Premium Application permissions enabled in User Management by your System Admin.

Log in to DataTable to check your access level. Otherwise, visit the DataTable Marketplace page to contact your Sales representative for pricing-related inquiries.

Connection methods

There are two ways to connect a sheet to a DataTable:

• Add & update mode: Pull subsets of the data into a sheet from a DataTable. You can use filter criteria to narrow down data, map into the sheet columns, and control how changes to the DataTable apply to the sheet. Use add, update, and remove options to ensure that your sheet matches the filter criteria defined in the connection.
• Lookup mode: Merge data from a DataTable into a sheet based on a unique identifier, similar to a VLOOKUP. You select a unique identifier to look up values in the DataTable and choose which sheet columns to map back to the DataTable. Lookup mode allows you to use a DataTable as a reference for processes running on a sheet.

In Lookup mode, the connection overwrites changes made in the sheet with data from the DataTable. Because of how the lookup mode works, the connection locks the mapped columns, so people can't edit the data.
Note that you can connect up to 1,000 sheets to your DataTable.

Changes made in the sheet don't sync back to the DataTable.

Add & update mode

Step 1: Select the table

1. Go to the sheet that you want to connect with a DataTable and select the Connections menu.
2. Select Connect to a DataTable.
3. Select Add & update mode.
4. Select the DataTable you want to use.

Step 2: Filter your data

Filter the data you sync from the DataTable into your sheet. Your sheet limits are 500,000 cells of data and 20,000 rows, while the DataTable can have more data.

You must have at least one filter criteria; you can add up to 10 criteria to your connection. Depending on the field type, you can filter your data using various criteria.

1. Select all in Sync records that meet all the conditions to toggle between requiring all filters and at least one condition. Select +Add New Condition if you want to add more conditions. 
2. Set your filters and select Next. 

Step 3: Map data from the DataTable into a sheet

1. Toggle on the fields you'd like to map and then select the sheet column to port to. The names in your DataTable and sheet don't need to match. 

   The connection adds a new column to your sheet called DataTable Record ID. This ID tracks rows added from the DataTable. This column is locked and hidden by default. If you change values in this column, the corresponding row will stop receiving updates.

2. Select Next.

Step 4: Set connection behaviors

1. Toggle any combination of behaviors: 
   • Add rows as they are added to the DataTable
   • Update rows as they change in the DataTable
   • Remove rows that no longer match the filter conditions
2. When you've toggled on your desired set of behaviors, select Next.

Step 5: Review the connection

A summary of the connection you've built displays.

1. To revise your connection settings, select Back.
2. Select Create when done.

It automatically adds any data that matches the filter criteria to your sheet. It may take several minutes, and you may need to refresh your sheet.

Lookup mode

Step 1: Set up the connection

1. Go to the sheet that you want to connect with a DataTable, select the Connections menu, and then select Connect to a DataTable.
2. Select Lookup mode.
3. Select the DataTable you want to use. 

Step 2: Select a unique identifier

1. Select a unique identifier to match rows in your sheet to records in the DataTable.
   • Be sure to select a DataTable field that contains unique values. This value is typically a string of numbers and letters from the dataset, such as a store code or database record ID.
   • If the DataTable has records with matching unique identifier values, the connection uses the data from the first record it finds. Usually, this is the lowest DataTable record ID.
2. After you map your unique identifier, select Next.

Step 3: Match fields

1. Toggle on the fields you want to map and then select the sheet column you'd like information to port to.

   Select at least one field. The names in your DataTable and sheet don’t need to match.

2. Select Next.

Step 4: Review the connection

A summary of the connection you've built displays.

• To revise your connection settings, select Back.
• Select Create to finalize your connection.

The connection automatically runs and adds lookup values for any unique identifiers with a match in the DataTable. It may take several minutes, and you may need to refresh your sheet.