Lookup tables

Integrations > Data Import > Lookup Tables

A Lookup table stores the mappings between keys and their associated values, and they are typically used to map an input value to an output value during import or in a K-field calculation. For example, a lookup table might map employee IDs to email addresses.

Two column table of IDs and matching Emails; three rows of example rows

When importing a set of survey records or raw invitations, and the data include employee IDs, the importer can use a transformation to map the IDs to their associated email addresses, and include the addresses as a output column. This example populates a field named e_visitor_unit with the email address of the employee identified in an input field named requestor-id by retrieving the value from a lookup table named employee_email:

<output-column>
 <target-field fieldId="e_visitor_unit" fieldName="Employee Unit" requiredness="REQUIRED" type="UNIT">
        <enumerated-field-parse-options mappingKey="IDENTIFIER" />
        <javascript-transform><![CDATA[
            var empID = record['requester-id'];

            lookupVal = lookupSingle('employee_email', {id: empID}, 'email');
            if (hasContent(lookupVal)) {
                return lookupVal;
            }
             return null;
        ]]></javascript-transform>
    </target-field>
</output-column>

Similarly, a K-field can obtain the email address by looking up the ID using either the lookup() and lookupSingle() JavaScript functions functions. These examples demonstrate various usages of the lookup functions:

// When the lookup table contains two columns, the first column is assumed to be the key, and the second is the value
// This table has two columns: 'countryName' and 'language'
var res = lookup('country_languages', 'spain' );
var res = lookupSingle('country_languages', 'spain', 'language');
  
// When the lookup table contains more than two columns, identify the columns
// This table has at least two columns: 'countryName' and 'language'
var res = lookup('country_languages', { 'countryName': 'spain' }).language;
var res = lookupSingle('country_languages', { 'countryName': 'spain' }, 'language');
  
// To locate the value using multiple keys
var areaCode = lookup('area_codes', { country: 'us', state: 'ca', city: 'palo alto' }).areaCode;
var areaCode = lookupSingle('area_codes', { country: 'us', state: 'ca', city: 'palo alto' }, 'areaCode');

To create a lookup table either:

  • Use the Auto Importer with a Lookup Table processor, or
  • Use the Upload Lookup Table option on the Lookup Table Setup screen.
Important: When you update a lookup table, you must rebuild the cache (by running a backfill or when an update is applied to the instance) before K-fields referencing that lookup table will reflect the update. For more information about backfills, see Backfill.

Maximum size

Lookup tables are limited to about 25m cells, where a cell is the intersection of a row and column.

Lookup Tables screen options

Upload Lookup Table
Uploads a .CSV file (or a .ZIP file containing .CSV files) into a lookup table format. The name of the .CSV is the name of the lookup table to create or replace. For example, a file named employee_emails.csv creates or updates a table named employee_emails. Existing tables are replaced with the new data.
The first row of the .CSV file is the column names. Key field column names begin with an asterisk (*).
*id,email
53927,nancy@example.com
74359,linda@example.com
1342,skyler@example.com
Delete
Purges the selected lookup table from the system.
This Lookup table is used in...
Link that lists of the places that reference the lookup table.

Basic Information properties

Lookup Table

Name of the table. The name is defined by either:

  • The filename attribute in the <lookup-table-processor-options> element in the Auto Importer specification, or
  • The name of the uploaded .CSV file.
Creation Time
Timestamp when the lookup table was created.
Last Modified
Timestamp when the lookup table was last updated.

Feed Account properties

Type
Type of Auto Importer feed used to import the lookup table. Empty for uploaded files.
Name
Name of the Auto Importer feed used to import the lookup table. Empty for uploaded files.

Record Processor properties

State (when last processed)
Processing mode of the Auto Importer feed when the feed file was processed. Empty for uploaded files.
Name
Name of the Auto Importer specification used to import the lookup table. Empty for uploaded files.

Original File properties

Incoming File Name
Name of the original incoming file used to create the lookup table. Empty for uploaded files.
Incoming File
Link to download the original incoming file. Says "(no data)" for uploaded files.
Uncompressed File Name
Name of the original incoming data file. Empty for uploaded files.
Uncompressed File
Link to download the original incoming data file. Says "(no data)" for uploaded files.

Lookup Table File properties

Lookup Table File
Link to download the lookup table as a .CSV file.
Table Size

Reports the table dimensions — excluding the heading row — in rows x columns, and total count of cells.

Followed by a preview table showing the headings and first few rows in the table.

Download All Files actions

All Incoming Files
Link to download a .ZIP file containing all the incoming files used as lookup tables in this company.
All Uncompressed Incoming Files
Link to download a .ZIP file containing all the uncompressed incoming files used as lookup tables in this company.
All Lookup Tables in the Company

Link to download a .ZIP file containing all the lookup tables in this company.

This option is useful for moving the lookup tables from one installation to another.

Editing lookup tables

To edit a lookup table, start by downloading the existing table:

  1. Use the Lookup Table Tile link to download the lookup table as a spreadsheet in which the values are separated by commas.

  2. Open the downloaded file in a spreadsheet editor.

  3. In your spreadsheet, find the rows you want to update and modify them. You can also add or delete columns.

    Spreadsheet editor showing lookup table with a new column

  4. Save your work locally, then click Upload Lookup Table and select the spreadsheet file.

    Tip: Note that Experience Cloud adds a timestamp to the filename when you download a lookup table file. Make sure that the file you upload has the same name as the lookup table. The recommended approach is to remove the timestamp from the filename.

  5. Click Save.

    After Experience Cloud processes the file, check the changes in the preview table.

    Screen capture showing the lookup table preview with an additional column

Example

The example below shows a Lookup table Auto Importer for uploading a contact center goals. The idMap() function creates an identifier from a prefix and a value, and also changes the case to lowercase and removes spaces.

<import-spec columnsCheck="ALLOW_EXTRA_OR_MISSING_COLUMNS" name="CC: Goals Lookup Table Import">
	<input-column heading="TEAM_LEAD_ID" />
	<input-column heading="TEAM_LEAD_FCR_GOAL" />
	<input-column heading="TEAM_LEAD_AGENT_OSAT_GOAL" />
	<input-column heading="MANAGER__FCR_GOAL" />
	<input-column heading="MANAGER_AGENT_OSAT_GOAL" />
	<input-column heading="COMPANY_NPS_GOAL" />
	<input-column heading="COMPANY_BRAND_OSAT_GOAL" />
	<input-column heading="COMPANY_EASE_BUSINESS_GOAL" />
	<input-column heading="COMPANY_FCR_GOAL" />
	<input-column heading="COMPANY_AGENT_OSAT_GOAL" />
	<javascript-library><![CDATA[
		function idMap(prefix,value) {
			if (hasContent(value)) {
				if (hasContent(prefix)) {
					return noWhitespace(lower(prefix))+'_'+noWhitespace(lower(value));
				}
			}
			return null;
		}
	]]></javascript-library>
	<output-column-group pluginName="LookupTable" recordUpdateMode="SYNC">
		<output-column>
			<input-column heading="TEAM_LEAD_ID" />
			<target-field fieldId="TEAM_LEAD_ID" fieldName="team_lead_id" requiredness="REQUIRED_USED_FOR_DUPLICATE_CHECK" type="STRING">
				<javascript-transform><![CDATA[
					var prefix = 'cc_teamlead';
					return idMap(prefix,value);
				]]></javascript-transform>
			</target-field>
		</output-column>
		<output-column>
			<input-column heading="TEAM_LEAD_FCR_GOAL" />
			<target-field fieldId="TEAM_LEAD_FCR_GOAL" fieldName="team_lead_fcr_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="TEAM_LEAD_AGENT_OSAT_GOAL" />
			<target-field fieldId="TEAM_LEAD_AGENT_OSAT_GOAL" fieldName="team_lead_agent_osat_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="MANAGER_ID" />
			<target-field fieldId="MANAGER_ID" fieldName="MANAGER_ID" requiredness="REQUIRED_USED_FOR_DUPLICATE_CHECK" type="STRING" >
				<javascript-transform><![CDATA[
					var prefix = 'cc_manager';
					return idMap(prefix,value);
				]]></javascript-transform>
			</target-field>
		</output-column>
		<output-column>
			<input-column heading="MANAGER__FCR_GOAL" />
			<target-field fieldId="MANAGER__FCR_GOAL" fieldName="manager_fcr_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="MANAGER_AGENT_OSAT_GOAL" />
			<target-field fieldId="MANAGER_AGENT_OSAT_GOAL" fieldName="manager_agent_osat_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="COMPANY_NPS_GOAL" />
			<target-field fieldId="COMPANY_NPS_GOAL" fieldName="company_nps_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="COMPANY_BRAND_OSAT_GOAL" />
			<target-field fieldId="COMPANY_BRAND_OSAT_GOAL" fieldName="company_brand_osat_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="COMPANY_EASE_BUSINESS_GOAL" />
			<target-field fieldId="COMPANY_EASE_BUSINESS_GOAL" fieldName="company_ease_business_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="COMPANY_FCR_GOAL" />
			<target-field fieldId="COMPANY_FCR_GOAL" fieldName="company_fcr_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<output-column>
			<input-column heading="COMPANY_AGENT_OSAT_GOAL" />
			<target-field fieldId="COMPANY_AGENT_OSAT_GOAL" fieldName="company_agent_osat_goal" requiredness="OPTIONAL" type="STRING" />
		</output-column>
		<lookup-table-processor-options filename="cc_rds_goals_table" />
	</output-column-group>
</import-spec>