Article to understand how to use the LOOKUP() Function.
The LOOKUP function is used to fetch data from the sheet which can be based on certain criteria. The fetched data is sorted in ascending order and provides a match. The most common use is to bind the form control to the columns to fetch and display data.
To fetch data from any sheet and bind it to a control, you need to define the LOOKUP function in the control’s formula box. The LOOKUP function works by looking up values in a given range of rows and columns or even from an array.
LOOKUP([Sheetname.columnname AS “”],filter,sorting,order)
LOOKUP([Sheetname.columnname], filter, limit, offset)
- Sheetname : is the sheet from where the data is to be fetched;
- Columnname : is the column whose data is to be fetched.
The parameters mentioned below are optional:
Filter : is the filter format to fetch data from the column. There are three components as Columns to be filtered, filter, and operator .
Sorting: is used to specify the column to sort the data on;
Order: the result set is order on the basis of the column specified in ascending (“ASC”) or descending (“DESC”) order.
AS is used to provide a custom/user-friendly name to refer to this column. This is also useful to bind data from Multiple LOOKUP and assign it to a Control.
limit is typically the value from the PROPERTIES.LIMIT assigned for Page Limit property in the table grid properties. For more information refer this article here.
offset is typically value from the PROPERTIES.OFFSET assigned for the OFFSET property in the table grid properties. For more information refer this article here.
The function returns the list of rows based on the filter criteria with the column details as specified using the filters and data type as string, numeric, date, and others as well.
Let’s understand the LOOKUP function with a few examples. In the examples below, we are assuming that the city, state, and zipcode are the form controls for which the data will be fetched or whose input data will be used for different filter examples
Simple LOOKUP function to fetch data from different columns
Let’s take a simple example where you are getting all the rows from the sheet. In this example, let us use the Citymaster_docs sheet.
The formula will be built as follows:
If you see the resultset the data for all the selected columns is displayed.
Adding a filter using operators for LOOKUP function
You can also define various filters to retrieve data based on a condition. You can use the AND, OR, NOT and other operators like MaxCount, MinCount, Contains, and so on listed here under the LOOKUP filter Truth Table here.
Now let us assume that we need to fetch data with a filter that the city rows are fetched where the state is the one specified.
So let us build the formula as follows:
LOOKUP([Citymaster_docs.City], Citymaster_docs.State = “Alabama”))
Let us take another sheet - Prod_doctest - here to understand a few more scenarios.
Now let us have a look at an example to fetch data that satisfies multiple conditions. Say for example you want to fetch the Product name from Prod_doctest sheet, whose Product type is ‘Apparels’ and Quantity is more than or equal to 5. The formula will be built using the AND operator as follows:
The formula will be built as follows:
LOOKUP([Prod_doctest.Prod_name] , AND(Prod_doctest.Prod_type=“Apparels”, Prod_doctest.Quantity>=5)))
Let’s take another example using the OR operator. Say for example you want to fetch the Product name from Prod_doctest sheet, whose Product type is ‘Apparels’ or where the Quantity is more than or equal to 7. The formula will be built using the OR operator as follows:
LOOKUP([Prod_doctest.Prod_name] , OR(Prod_doctest.Quantity>= 7, Prod_doctest.Prod_type=“Apparels”))
Sorting the data fetched with LOOKUP()
When you are using the LOOKUP function to fetch data you might want to sort the data in an ascending or descending order based on one of the columns.
The formula is built as follows:
where the data will be sorted in Ascending order based on the Prod_name column.
Thus the LOOKUP function can be used to fetch data as per condition and sorted in an ascending or descending order.
Adding pagination using LOOKUP function
Whenever you fetch data from data sources like Connectors and Sheet, you would typically be using the table grid control to display the data. It is always preferable to limit the number of rows displayed in a table grid control for readability. You can use the Limit and Offset parameters of the LOOKUP function to limit the number of rows displayed.
So if you consider the LOOKUP formula that fetches city details from a City Master sheet that returns a large number of rows, you can set the Offset and Limit values for the table grid as shown below that would allows you to limit the rows displayed.
LOOKUP([Citymaster_docs.City,Citymaster_docs.State,Citymaster_docs.Postal_code,Citymaster_docs.Abbreviation] , “” , tablegrid7.PROPERTIES.LIMIT , tablegrid7.PROPERTIES.OFFSET )
Where tablegrid7 is the unique name of the table grid added to the app. The PROPERTIES.LIMIT is assigned the value for Page Limit property and PROPERTIES.OFFSET is assigned the value for the OFFSET property that we have set in the table grid properties as seen above.
For a more detailed explanation about the pagination using the LOOKUP function, refer to this article here.