Add lookup values to your query

[ 1 What permissions do I need? ] [ 2 Using lookup operations in the search window ] [ 3 Using lookup operations in LINQ ] [ 4 Lookup operations result ] [ 5 Add all the values in a row ]

What permissions do I need?

To be able to view and use lookup operations when working in the search window, you need to have at least view permissions on Lookups for upload lookups or Query lookups for query lookups.

Take into account that these two permissions are hierarchy-based, meaning you need to have Lookups assigned in order to be able to have Query lookups.

Additionally, you need to receive at least View access to the lookup in question via sharing. Check Share lookups to know more.

Using lookup operations in the search window

You can run a search and correlate its content with values from a lookup table. If any value in the key column of the lookup table is also present in your query, the values in those lookup rows can be added in a new column.

Following the previous example shown in the Upload a lookup table article, suppose you want to include two lookups in the demo.ecommerce.data table showing the street address and printer brand corresponding to the IPs in the data table.

Add Street address:

Go to Data Search and open the demo.ecommerce.data table.
Click Create column in the query toolbar and give it a meaningful name (in this case, IP street address).
Select custom to show only lookup operations, look for the name of the lookup (in this case IP_list) and select the StreetAddress operation.
Add the clientIpAddress column as an argument and click Create column.

Add Printer brand:

Repeat the process described above but in this case, select the Brand lookup operation and then choose again the clientIpAddress column as an argument (you can name the column IP Brand).

Grouping prevents lookup usage

Note that it is not possible to add a lookup to your query after grouping data. The custom button will not appear in the window.

Using lookup operations in LINQ

You can also add lookup values by using the corresponding LINQ syntax in the query. To do it, you can either use the Free Text Query to open the search from the beginning or open the Query code editor if you have it already open. This is the syntax you need to use:

select `lu/lookupName/lookupColumn`(field) as newColumnName

This is the required LINQ query to add the lookup in the example shown above using the search window interface:

from demo.ecommerce.data
select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Lookups in Activeboards

The syntax to use lookup operations in Activeboards is different. Visit the following article to know more: LINQ syntax differences: Activeboards vs search window

Lookup operations result

When using a lookup operation, both the key column specified in the lookup when created and the column specified as an argument in the operation are searched for matching values. When they are found, the corresponding value of the lookup column selected as the operation will be added to the new column.

In the example above, both columns are created with the lookup values, showing the street and printer brand corresponding to the IPs of the clientIpAddress column that are also in the key column of the lookup table. These columns will show null for those IPs that are not included in the lookup table.

Add all the values in a row

When a match is found between the content of your query and a value in your lookup key column, you can select the Whole entry of... operation to add all the values in that lookup row as a JSON object in a new column.

In the search window

To do it, follow the steps in the process explained above but the operation selected must be Whole entry of... instead.

Using LINQ

You can also add lookup rows as JSON objects by using the corresponding LINQ syntax in the query.

To do it, you can either use the Free Text Query to open the search from the beginning or open the Query code editor if you have it already open. This is the syntax you need to use:

select `lu/lookupName`(field) as newColumnName

You can then split the resulting JSON objects into their different key/value pairs and transform them into different data types using the operations in the JSON group.