Table lookup DAC

The Table lookup dynamic advanced command is part of the variable commands. Use this command to search for a value in a table (the "lookup value") and retrieve a corresponding value. You can retrieve different types of values, such as the lookup value’s location, all values in its row or column, or a specific value from a chosen column or row. Drag it into a wizard from the Advanced commands view in the Nintex Wizard Editor to use it.

Before you begin:

Use the Table lookup command to enhance your automation in various scenarios:

  • When you need to find and return specific values from a table based on set conditions.

  • To search an entire table, a specific column, or a specific row for matching values.

  • To retrieve either a single matching value or all matching values in a table.

  • When you want to return various types of data such as a single value, entire rows or columns, or the location of matching values.

In the Nintex Wizard Editor, search for the Table lookup command and drag it into your steps. Use the table below to configure each field and understand the settings:

When using variables in any of the fields, remember to enclose the variable name in dollar signs (e.g., $MyVar$). You can either type the variable with the dollar signs or select it from the drop-down menu, if available.
Field Description What to do
From the table stored in The table where you are performing the search. Enter the variable that contains the table data.
Column delimiter

The variable or character that separates columns in the table.

When relevant, the command separates the retrieved values using the delimiter you specify here.
 Enter the variable or character that separates columns in the table (e.g., a comma, semicolon, or pipe |). See Best practices.
Row delimiter

The variable or character that separates retrieved rows in the table.

When relevant, the command separates the retrieved values using the delimiter you specify here.
 Enter the variable or character that separates rows in the table (e.g., a newline \n, or pipe). See Best practices.
Find Option to find a single matching value or all matching values.

Select A single matching value or All matching values based on your requirements.

If you select A single matching value and multiple matches occur, the Variable list shows the Multiple lookup values found error instead of the result.
Condition The condition for finding the lookup value (e.g., equals, contains, begins with, etc.). Choose a condition from the dropdown and enter the lookup value or variable. Options include:
  • Equals (ignore case)
  • Equals
  • Contains (ignore case)
  • Contains
  • Begins with
  • Ends with
  • Match regular expression (ignore case)
  • Is empty
  • Is defined
  • Is greater than/less than
Reverse comparison Reverses the selected condition to perform a negative comparison (if not). Select this box if you want to reverse the comparison (e.g., "not equals" instead of "equals").
Search entire table/column/row Specifies where to perform the search. Select Search entire table, Search column, or Search row. Enter the column or row number if necessary.
Return Return The type of data to return based on the lookup. Select from the dropdown menu.
Address (column & row) Returns the location of the lookup value (row and column).

Select Address (column & row).
Entire row Returns all values in the lookup value’s row. Select Entire row.
Entire column Returns all values in the lookup value’s column. Select Entire column.
A value from a specific column in the same row Returns the value from a specific column in the same row as the lookup value. Select A value from a specific column in the same row. Then choose whether you want the wizard to return:
  • The value from the last column in the lookup value’s row.
  • The value from a specific column in the lookup value’s row (enter the column number).
Use the actual column number, not the column's position relative to the lookup value.
A value from a specific row in the same column Returns the value from a specific row in the same column as the lookup value. Select A value from a specific row in the same column. Then choose whether you want the wizard to return:
  • The value from the last row in the lookup value’s column.
  • The value from a specific row in the lookup value’s column (enter the row number).
Use the actual row number, not the row's position relative to the lookup value.
The matching value itself

Returns the value that matches the lookup criteria.

This option is useful when retrieving all values that match a condition other than equals. For example, if your table contains transaction data, and you want to find all transactions greater than $10,000.

 Select The matching value itself and enter the name of the Output variable to store the result.
Column # The column number where you want to retrieve the value (when returning a specific value from a row). Enter the specific column number or select a variable if retrieving a value from a specific column in the same row.
Row # The row number where you want to retrieve the value (when returning a specific value from a column). Enter the specific row number or select a variable if retrieving a value from a specific row in the same column.
Output variable (for multiple values) The variable where you want to store the result if multiple matching values occur. Enter or select the output variable to store all matching values.
Error handling Set how you want to handle errors when issues occur during the lookup. Use default error handling or customize error messages as needed.

Follow these best practices when using the Table lookup command:

  • When working with complex tables, ensure the column and row delimiters are set correctly to avoid misalignment of values. Misconfigured delimiters may lead to incorrect data retrieval.

  • Consider using regular expressions when performing advanced searches, such as pattern matching. Be sure to test your regular expressions to avoid unintended matches.

  • Use the Note command to document the conditions in your command to help other users and with future maintenance.

  • At the start of the wizard, use the Set value command to define variables for common delimiters. This ensures consistent handling of row and column delimiters throughout your process:

    • Create a variable named Comma and set its value to ,.

    • Create a variable named Tab and set its value to <Tab>.

    • Create a variable named Pipe and set its value to |.

    • Create a variable named Newline and set its value to <Enter>.

This example shows how to find and return all transactions from a table where the amount is greater than $10,000.

  1. Add the Set value command to your wizard and complete the following fields:

    • In the variable: NewLine

    • Set the value: <Enter>

  2. Add another Set value command to define a variable with the source text.

    Copy

    Transaction_data

    Transaction ID, Date, Customer, Amount, Status
    1001, 2023-01-15, John Smith, 15000, Completed
    1002, 2023-01-18, Jane Doe, 8500, Pending
    1003, 2023-01-20, Alex Johnson, 22000, Completed
    1004, 2023-01-25, Emily White, 5750, Pending
    1005, 2023-01-30, Michael Brown, 12300, Completed
    1006, 2023-02-01, Lisa Green, 20000, Pending
    1007, 2023-02-03, David Clark, 18500, Completed
    1008, 2023-02-05, Paul Harris, 9500, Pending

  3. Add the Table lookup command and complete the following fields:

    • From the table stored in: Transaction_data

    • Column delimiter: , (comma)

    • Row delimiter: $NewLine$

    • Find:

      • Select All matching values.

      • Select is greater than.

      • Enter 10000.

    • Select Search the entire table.

    • Return: Select The matching value itself.

    • Output variable: Matching_value

    • Error variable: Error

  4. Use the View variable list command to verify the command works as intended.