Tables: Look up data in a column

We鈥檙e updating the term Pushbot to Workflow throughout Catalytic.聽Learn more

馃暀 4 minute read

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.

Use Case

  • Look up a product price based on the ID number
  • Search for an employee ID and return specific employee information

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.

馃挕Tip: 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 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 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 a prefix that will be added to the beginning of each output field name as this action may output more than one field.

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鈥檚 not working as expected, or you鈥檙e looking for suggestions, check through the options below.

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""".