Connect a sheet to a DataTable

Prerequisites

To use DataTable, you must:

• Be aLicensed User
• Have Owner or Admin permissions on any DataTable connected sheets
• Have DataTable permissions enabled inUser Managementby a Smartsheet System Admin

Connection methods

There are two ways to connect a sheet to a DataTable:Add & update modeandLookup mode.To create a connection, you must have Admin, Owner, or Editor rights on a sheet.

• Add & 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 apply 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 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.
  
  In Lookup mode, the connection will overwrite changes made in the sheet with data from the DataTable. Your sheet will always match what’s in the DataTable. Because of this, the connection will lock the mapped columns, so people can’t edit the data.
  

Changes made in the sheet do not sync back to the DataTable.

Add & update mode

Step 1: Select the table

1. Go to the sheet you want to connect with a DataTable, select the连接menu, and then selectConnect to a DataTable.

2. SelectAdd & update mode.

3. Select the DataTable you want to use. The list includes DataTables you created and those you’re shared to as an Admin or Viewer.

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 criteria to your connection. You can filter your data using various criteria depending on the field type.

1. SelectallinSync records that meet all the conditionsto toggle between requiring all filters to be met andat least one condition. Select+Add New Conditionif you want to add more conditions.
2. Set your filters and selectNext.

Step 3: Map data from the DataTable into a sheet

1. Toggle on the fields you’d like to map, then select the sheet column you’d like that information to port to. The names in your DataTable and sheet do not 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. SelectNext.

第四步:设置连接行为

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 longer match the filter conditions
2. When you’ve toggled on your desired set of behaviors, SelectNext.

Step 5: Review the connection

You will see a summary of the connection you’ve built. To revise your connection settings, selectBack.

SelectCreatewhen you’re done. Any data that matches the filter criteria will be automatically added 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 you want to connect with a DataTable, select the连接menu, and then selectConnect to a DataTable.
2. SelectLookup mode.
3. Select the DataTable you want to use. The list includes DataTables you created and those you’re shared to as an admin or viewer.

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, 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.
2. After you map your unique identifier, selectNext.

Step 3: Match Fields

1. Toggle on the fields you want to map, then select the sheet column you’d like that information to port to. You must select at least one field. The names in your DataTable and sheet do not need to match.
2. SelectNext.

Step 4: Review the connection

You will see a summary of the connection you’ve built. To revise your connection settings, selectBack.

SelectCreateto finalize your connection. The connection will automatically run and add 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.