Connect a sheet to a DataTable

Prerequisites

To use DataTable, you must:

• Be a Licensed User
• Have Owner or Admin permissions on any DataTable connected sheets
• Have DataTable permissions enabled in User Management by a Smartsheet System Admin

Connect a sheet to a DataTable

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

• Add and update mode:  This connection type pulls subsets of the data into a sheet from a DataTable. You can use filter criteria to narrow down the subset of data, map the DataTable data into the sheet columns, and control how changes to the DataTable are applied to the sheet. Use add, update, and remove options to ensure your sheet matches the filter criteria defined in the connection. You can count on your sheet being up-to-date with the latest data from a DataTable. 
  
  This method does not sync changes made in the sheet back to the DataTable. 

• Lookup mode: This connection type merges data from a DataTable into a sheet based on a unique identifier, 
  similar to a VLOOKUP You will select a unique identifier to look up values in the DataTable, and choose which sheet columns to map back to the DataTable. This method allows you to use a DataTable as a reference for processes running in a sheet.
  
  The connection will overwrite changes made in the sheet for any mapped columns. Lock your mapped columns to prevent this from happening.  
  
  This method does not sync changes in the sheet back to the DataTable

Add and update mode

Step 1: Select the table

1. Go to the sheet you want to connect with a DataTable, select the Connections menu, and then select Connect to a DataTable. 
2. Select Add and update mode.
3. Go to the sheet you want to connect with a DataTable, select the Connections menu, and then click Connect to a DataTable.

Step 2: Filter your data

Filter the data you will sync from the DataTable into your sheet. Your sheet is limited to 500,000 cells of data and 20,000 rows, while the DataTable will likely have significantly more data.

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

• Select Sync records that match at least one condition to toggle between requiring one or more conditions to match or, leave it off to match all conditions. After you’ve set your filters, click Next. 

Step 3: Map data from the DataTable into a sheet

1. Click the toggle next to the field you want to map, and then select the matching sheet column from the dropdown.
   NOTE: 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. Click Next.

Step 4: Set connection behaviors

You can set the connection to:

• Add rows to the sheet as they are added to the DataTable
• Update rows to the sheet when they are changed in the DataTable
• Remove rows from the sheet when they no longer match the filter conditions
  NOTE: If a previously synced row no longer matches your filter criteria the row will be deleted. 

Leave these settings in their default state (add and update rows) unless you only want updates or new rows added.

• Select Next when you’re done or Back to edit your filters. 

Step 5: Review the connection

To revise your connection settings, click Back (or the step on the left menu).

Click Done if you’re satisfied with the way the connection is configured.  

Lookup mode

Step 1: Set up the connection

1. Go to the sheet you want to connect with a DataTable, select the Connections menu, and then select Connect to a DataTable.
2. Select Lookup mode.
3. Select a DataTable and then select Next. The list shows only the DataTables you have created.

Step 2: Select a unique identifier

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 is typically a string of numbers and letters that comes with the dataset, such as a store code, employee email address, or database record ID.

If the DataTable has records with multiple matching unique identifier values, the connection will use the data from the first record it finds. In most cases, this will be the lowest DataTable record ID.

• After you map your unique identifiers, select Next.

Step 3: Match fields

On the left are DataTable fields, and on the right, sheet columns. 

To map data from the DataTable into a Sheet:

1. Slide the toggle next to the field you want to map.
2. Select the sheet column from the dropdown.
3. Select Next to continue or Back to edit your unique identifiers.

Step 4: Review connection

To revise your settings, select the item you want to revise from the left menu, or click Back to make more changes.

Select Done to complete the connection.