Lookups in Grey Boxes

Somebody solve a mystery for me… Why are some of my lookup results wrapped in light grey pill-shaped boxes and others in green plain text?

If the Lookup refers to a multiple relation or directly to a table, then it returns an array of values. It’s an array because it can potentially contain more than one value.

If a Lookup refers to a single relation then it returns a single value because it can only contain a single value.

3 Likes
  • Green text: single value
  • Gray box: array (gray-array)
  • White box: relation

A lookup from a single relation will return a single value, because there is only one value to return (green).

A lookup from a multiple relation will return multiple values in the form of an array (gray). It could not return a comma-separated list of values, because a comma-separated list is a single value that happens to have commas in it.

Try the following:

  • In a table, in 3 rows (under one another), put: blue, white, red
  • In another table (or in the same one), create a Joined List column, point to the column with the colors: notice how you’ve turn rows into a comma-separated list
  • To the right of your Joined List column, create a Split Text column pointing to the Joined List column: notice how you’ve turned a comma-separated list into an array.
  • Finally, create a Joined List column pointing to the Split Text column: notice how you’ve turned the array back into a comma-separated list.

To continue the exercize:

  • In the table where you have your comma-separated lists and array, add empty rows so you have a total of 3.
  • Create a basic number column you can call RowIndex, and populate the column with 0, 1 and 2 down the column.
  • Create 2 Single Value columns you can call Original Colors 1 and Original Colors 2. See how by pointing either to the very first column with the colors or the array of colors and by using Start From pointing to RowIndex, you can recreate the very first column.
4 Likes

Helpful explanations. Thank you. I’m trying to automatically adjust inventory levels when an order is marked “picked up,” but it seems that it’s only possible if the related item column is a single relation. Any suggestions on how to break a multi-relation column into separate single value columns and make use of Set Column Value action for the items in the order?

  • Randall
  • Multiple relation
  • Lookup of the multiple relation → Array
  • Add n Single Value column where n is the maximum number of elements you expect in your array
  • Configuration of the Single Value columns: point to the array, start from 0, start from 1, start from 2, etc. in each column respectively.
1 Like