Table Lookup

This Basic tool retrieves data from a table. These tables are created and maintained in Interaction Administrator. See the Table Editor topics in the Interaction Administrator online help for more information on tables.

This tool allows you to query specific columns within a table for specific values, and then return values from the matching row. Table lookups are case sensitive. In the example table below, you could perform a lookup on the LastName column where LastName = Adams, and then return the email address associated with Adams. You can bind the returned value to a variable that can be used within the handler.

Any columns you want to perform lookups against must be indexed. When you create the table in Interaction Administrator, you can determine which columns are indexed, and the type of index to apply. Tables support two types of indexing: unique and multiple

Unique – each entry in the column is unique (e.g., account number) amongst all the entries in that column. Table Editor warns you if it finds duplicates. Lookups on unique index columns are faster than on columns with duplicate values. In the example table below, LastName has a Unique index.

Since only zero or one rows will be returned from a lookup on a unique column, the type of variables to which you bind the returned row values will be string. See Column Bindings below for more information.

Multiple – each entry in the column may have one or more occurrences of that value in the column (e.g., account type, date, etc.). Lookups on multiple value indexes are slower than on columns with unique indexes. In the example table below, FirstName has a Multiple index.

Since zero, one, or more rows will be returned from a lookup on a multiple column, the type of variables to which you bind the returned values is a list of strings. See Column Bindings below for more information.

Example Table:

LastName	FirstName	EmailAddress
Adams	Jane	JaneA@foo.com
Birch	Steve	SteveB@foo.com
Conner	Jane	JaneC@foo.com

 

In the table above, you could search the LastName column for Adams, and then return the email address associated with Adams.

Settings

Table Name

The name of the table as configured in Interaction Administrator. Any tables you have created in Interaction Administrator are listed here.

Lookup Expressions

Configures the column(s) to search and the value(s) you want to search for. Click the Add button to add a column against which to do the lookup and an expression for which to search in that column. Click the Modify button to modify the selected expression. Click the Delete button to remove the selected lookup column and it’s expression from the list.

From the example table above, you would specify the following to find the row where LastName = "Adams":

Column	Expression
LastName	"Adams"

 

From the example table above, you would specify the following to find the row where LastName = "Adams" and FirstName = "Jane":

Column	Expression
LastName	"Adams"
FirstName	"Jane"

 

Note: CIC inserts an AND condition between multiple lookup expressions when this tool executes. There is no way to specify an "or" or any other condition between lookup expressions on a table. If you desire more robust conditions, we recommend that you use the database tools and an external database.

Column Bindings

The value(s) to extract from the row(s) returned from the lookup. You may specify more than one value to return.

From the example table above, if you wanted to return the first name and email address for the row in which LastName = "Adams," you would use the following Column Bindings:

Column	Variable
FirstName	StrFirstName
EmailAddress	StrEmailAddress

When this step executes, it populates StrFirstName with "Jane" and StrEmailAddress with "JaneA@foo.com."

In another example from the example table above, if you wanted to search for the last name and email address for the row in which FirstName = "Jane", you would use the following Column Bindings.

Note that in this example, you are searching on a multiple index column (where more than one row can have the value of Jane, so you must specify a List of String variable to hold the output values):

Column	Variable
LastName	ListOfStrLastName
EmailAddress	ListOfStrEmailAddress

When this step executes, it populates the ListOfStrLastName with two elements: "Adams" and "Conner." It populates the parallel ListOfStrEmailAddress variable with two values: "JaneA@foo.com" and " JaneC@foo.com." (where "parallel list" means that the 1st element in one list correlates to the first element in a second list, and so on, until the nth element in the first list correlates to the nth element in the second list).

Exit Paths

Success

If the value was found and returned, this tool takes the Success exit path.

Table Not Found

This is returned if the table you requested data on is not found. This can occur if the table has been removed or renamed since this handler was published.

Sync Error

The Sync Error path is taken if someone has changed an index from Unique to Multiple and the variable types the handler passed in are incorrect according to the index type. For example, the handler author selected a unique index column, bound some variables, then published that handler. But then someone went into Table Editor and changed that index column from unique to non-unique. Then the handler executed. The Table Lookup tool will return Sync Error (because the bound variables were of type String instead of List of String), and the bound strings would receive the values of the first row found.

Failure

The tool has failed for some other reason, such as an exception occurred, or the server is not responding, or your input values are incorrect.

Value not Found

This tool takes the Value not Found exit path if the value you are searching for in your Lookup Expression or Column Bindings is not found.