Tables: Look up data in a column

Use this action to search a column in a Catalytic data table for matching data and return data from other columns in the same row.

This action makes it easy to find data in a table by row, like looking up the price of product by the product name. If multiple rows match the Search term, only the first matching row is returned. To filter a table and return multiple rows based on a criteria, try the Tables: Apply Filters or Tables: Run SQL query on data table and return data table actions.

Use Case

Example
  • Olivia connected her POS systems to a Workflow, and now has data on each transaction across her stores. She can use the Excel: Look up Data in Columns action to look up individual transactions based on a unique transaction ID. She configures the action to return the store location, sale price, and items sold for that specific transaction. She plans to use this in a reporting Workflow that sends alerts depending on a transaction’s sale price.

How to configure this action

This action searches a column for a search term. If the action finds a match, it returns data from that row based on the columns to return.

For example, to find the price and quantity of an Apple from a data table, we need to search the Fruit column for the Apple row and return data from that row for the Price and Quantity columns.

Fruit Price Quantity Vendor ID
Apple 10 5 ABC
Banana 40 8 DEF
Lime 20 2 GHI

To find the price and quantity of an Apple from the data table:

  1. Set the lookup column name to Fruit
  2. Set the search term to Apple
  3. Set the columns to return to Price,Quantity.
  4. The output would be two fields; a price field of 10 and a quantity field of 5.
note
  • Note: Enclose column names in quotation marks if they are a field reference, or contain special characters like commas, leading/trailing whitespace, and newlines. If the column name contains a quotation mark, escape each quotation mark with quotation marks, for example: if the column name is "Column name", enter it as """Column name""".

Fields for this action

  • Table ID

    • Select a table from a list of all tables available on your team. The list only includes tables you have permission to view.
      • You can also reference a table stored in a field. Change the left hand drop-down to Use table via field then select from any field that is part of the process. Learn more.
      • If necessary, you can enter the Table ID directly. Change the left hand drop-down to Use table by ID then enter the ID manually. Learn more.
  • Lookup column name

    • Enter the column name to search through using the search term. For example, if the search term is Apple, the action will search this column for that term.
    • If a column name is a field reference or has any special characters, enclose the column names in quotation marks.
  • Search term

    • Enter the term to search the lookup column name for. For example. if the lookup column contains fruit names, enter the fruit to search for here.
      • By default, the action performs an exact match using the term. To use REGEX, set the Advanced search type field to REGEX and enter the expression in this field.
  • Columns to return

    • Choose which columns the action returns from the matched row. If blank, the action returns all columns.
      • To return specific columns, enter the selected column names in a comma separated list. For example, Column1, Column4, Column 12.
    • If a column name is a field reference or has any special characters, enclose the column names in quotation marks.
  • Advanced search type

    • Enter the type of search.
      • Leave empty to perform an exact match search on the column that uses the Search term field value.
      • Enter REGEX to perform a REGEX search on the column that uses the Search term field value as the regular expression.
  • Output Field Prefix

    • To help keep output fields organized, choose an output field prefix to add to the beginning of each output field name as this action may output more than one field.
    • The step’s name is used as the prefix by default.

What will this output?

If a match is found, this action outputs a field for each column to return. If no matches are found, no output field will generate.

Each field will result as:{{output-field-prefix--column-name}}, where column-name is the name of the columns from columns to return, for example my-results--fruit.

This action may generate multiple fields. To help keep output fields organized, the prefix above will be added to the beginning of each of the output field names, separated by two dashes.

Output fields for this action

  • Matched column 1 (2, 3, etc.)

    • A new field will store a result for each column defined in configuration. The field name will be the columns to return name defined during configuration.

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.

My table has multiple matching rows, why does the action only return one row’s data?

If multiple rows match the Search term, only the first matching row is returned. To filter a table and return multiple rows based on a criteria, try the Excel: Apply Filters or Excel: Run SQL query on Excel file and return Excel file actions.

Why do some column names not work?

Enclose individual column names and values in quotation marks (""), if they contain special characters like commas, leading/trailing whitespace, and newlines. For example:

  • If the column name is $Weekly Report,,,, enter the column name as "$Weekly Report,,,", with quotation marks.
  • If you want to use the field reference {{tablecolumn}} to dynamically reference the column name, enter it as "{{tablecolumn}}"

If the column name contains a quotation mark, escape each quotation mark with quotation marks, for example: if the column name is "Column name", enter it as """Column name""".

Sorry about that. What was the most unhelpful part?









Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.