When using the Form controls you would be capturing data from the user and then storing the data to the linked sheets. Similarly, you would also need to use the data that is stored in these sheets and auto-populate it to the necessary controls for further calculation. The data is bound to the controls and can be stored and fetched using different functions. We may also have scenarios wherein we will validate the data from the sheets and fetch it to the drop-down controls, display in a tabular format using a table grid, display on a details view, and so on.
To fetch data from any sheet and bind it to a control, you need to define the LOOKUP() formula in the control’s formula box. The LOOKUP formula works by looking up values in a given range of rows and columns or even from an array.
Syntax
LOOKUP([Sheetname.columnname], filter, sorting, order)
Where,
- 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 operator & filter criteria.
- 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.
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.
There are two ways in which we can add the LOOKUP formula: one is by binding data from a sheet using a wizard and one by creating the LOOKUP formula. We will be looking at creating the LOOKUP formula here.
Now let us first have a quick look at the video to understand how to bind data.
Let us consider the following sheet Prod_doctest for the purpose of the examples to understand the LOOKUP formula.
Binding single column single row
Let us take a simple example here to bind a single control Product name to the Prod_name column of the sheet with a condition that it is for only those items whose Product Type is “Apparels” and for products with a quantity greater than or equal to 5.
You can fetch the data from the sheet by binding the control to the column and adding a LOOKUP formula. Add or paste the formula and click Save and Validate.
Formula :
UNIQUE( LOOKUP([Prod_doctest.Prod_name] , AND(Prod_doctest.Prod_type=“Apparels”, Prod_doctest.Quantity>=5)))
Where
- Prod_doctest. Prod_name is the name of the column bound to the controls
- AND is the operator used for the filter
- Returns: The formula returns unique rows that match the specified condition. The above formula returns the two rows from the sheet that have the product type as “Apparels” and Quantity more than or equal to 5. In this case, it will return - T-Shirt.
Let us now take a preview of the form to understand the results.
Binding Single column, multiple rows
Now let us consider another scenario. Let us take the same form and the sheet as above. Let us assume that we want to fetch the Product types available in the sheet as values for the dropdown list for the product type.
You can fetch the data from the sheet by binding the control to the column and adding a LOOKUP formula. Add or paste the formula and click Save and Validate.
Formula :
UNIQUE(LOOKUP([Prod_doctest.Prod_type]))
Where
- Prod_doctest.Prod_type is the column bound to control to get the values for Product type.
- Returns: The formula returns all the rows from the Prod_type column. The above formula thus will have product types listed as “Apparels”, “Essentials”, Non-Essentials”.
Note that the results include All rows, thus having multiple rows for the same Prod_type . So we will have the workaround here and add the UNIQUE() formula so that only Unique values are fetched from the column. The dropdown list now has the list of unique Prod_types from the sheet.
Binding Multiple columns, multiple rows
Now that we have seen some simple examples of using the LOOKUP formula, let us consider some more scenarios. Let’s add a Table Grid control wherein we can fetch the data from the sheet and even select the specific columns to be reflected in the tableview. In this case, all the records from the sheet are fetched, but in case you want to add for example only unique values, then you can set the filters accordingly. Select Filter to get the options to set up the filters as required.
Formula :
LOOKUP([Prod_doctest.Prod_name,Prod_doctest.Quantity,Prod_doctest.Price,Prod_doctest.Prod_type],””, Prod_doctest.Prod_name,”ASC”)
**Where ** we have listed out all the columns from sheet to fetch data.
Returns: The formula returns multiple rows from the specified columns.
You can add or paste the above formula and click Save and Validate. The result can be seen in the result box. The result set includes all the rows fetched from the column Prod_name, Quantity, Price, and the Prod_type.
Now let us take a preview to understand how the data is fetched and reflected to the Table grid control.
Binding Multiple columns single Row
Now let us consider another scenario where we want to fetch multiple columns to bind to a Detail view Control. Let us take a different scenario to fetch only the record matching the criteria. You can add or paste the below formula and click Save and Validate. The result can be seen in the result box.
Formula :
LOOKUP([Prod_doctest.Prod_name,Prod_doctest.Prod_descr,Prod_doctest.Quantity,Prod_doctest.Price,Prod_doctest.Prod_type] , AND( Prod_doctest.Prod_name = productname5 , Prod_doctest.Prod_type = producttype1))
Returns: The formula returns a single row that satisfies both the given conditions/ filters.
Now let us take a preview to understand how the data is fetched and reflected to the Detail view control.
Thus the LOOKUP formula can be used to fetch data from your sheets, apply conditional statements, and ensure that you are getting the best data as per your requirement.