While Form or Input Controls are meant to accept data from a user, you can also pre-populate these control with default data as per your need. This default data can be a static data or can be derived from a field or formula or can be fetched from a sheet. We will see how to populate a control via -
Manually using static data
When you add a control on the screen, you will see a letter ‘F’ displayed on the right corner of the control. Clicking on it will open a formula modal for that control. This is the modal where you can apply formula to perform operations on your data. Type in some mathematical formula say 5*10 and click on Save and Validate button. As soon as you click on this button, the modal will display the result of the formula.
Fetching from other Fields
Every control is uniquely identified in a microapp by its Unique Name. To pass data from one control to another control and to use a control’s reference in formulas, you need to use control’s Unique Name. Lets see how to use this method.
Let’s take a small example of dividing two numbers and displaying the result in one control. For this example, let’s add 3 controls on the screen, two number type controls that will accept two numbers and an input_label control which will display the result of the calculations using the first two controls.
Now to use Control’s reference in formulas, we need to use first two control’s unique name and use the mathematical operator slash (/) i.e. the division operator to divide first number by second number. Lets name the first control as Dividend, the second control as Divisor and the third control as Quotient. Now select the Quotient control and click on the ‘F’ option. In the formula box, copy and paste the unique name of the Dividend and Divisor and type in the formula as dividend/divisor and click on the Save and Validate button. We will get a result as Success. We will not be able to see the desired quotient since we are using control’s reference in the formula. To see the desired result, click on the preview button and input the values in Dividend and Divisor, this will in turn fetch the resultant Quotient.
Now very important aspect when it comes to using formulas in Studio is that you need to ensure the control’s reference should be control’s Unique Name, in other words, a control’s Unique Name is always in small cases and has to be used as is. Whereas, Functions & predefined Keywords are to be written in ALL CAPS. For example, SUM(), AVERAGE(), LOOKUP() are to be written in CAPS.
To use any text as a static value in formulas you need to write them within double quotes ("").
Fetching data from Sheets
Generally, all the data that user submits from an microapp gets stored in the Sheets. To use this data on any screen in your app, or use it in workflows, you need to fetch this data from sheet using a special function known as LOOKUP. LOOKUP is used to retrieve data from any sheet. First we need to have a sheet in order to fetch data in LOOKUP. We already have a sheet created and try to fetch data from that sheet. Let's take a look at the sheet first and so we go to home page and click on Sheets tab and click on Products sheets. The Products sheets contains two columns named Product Name and Product Category with some values in them. Now we shall try to fetch this data in the app using LOOKUP. In the app, drag and drop a control known as Sheet LOOKUP and in the formula modal, need to type LOOKUP([Products.productname, Products.productcategory]). This means fetch all the records of Products sheet having column name as productname & productcategory. Now we’ll see in the preview whether we can see the data. There you go, we can see all the entries in the Product Name & Product Category columns in the sheet Products. Now let's dig into the LOOKUP formula. The LOOKUP formula that we have used is pretty simple formula to all fetch records. But in most cases, we may want to filter some records and sort the result in some order i.e. in either ascending or descending order. To use filter and sorting criteria while fetching the data from sheet, we need to tweak the LOOKUP formula in the following format -
Now lets breakdown down the LOOKUP formula into parts. The first part is the columns to be fetched and displayed i.e. Products.productname. Every column can be accessed by its Sheet name and hence we have Products.productname. Now these column needs to be written within square brackets [ ]. You can put more than one column separated by comma (,) within the square brackets, just the way we saw in earlier example, to fetch multiple columns.
The second part is the filter, Products.productcategory="Cell Phones", this condition is used to select those records whose productcategory has value Cell Phones. The rest ones will be filtered out. Filter is an optional entity in LOOKUP. So if you want to fetch all records you can skip writing a filter.
The next part is the sorting, Products.productname,"ASC". This column is used to sort the results based on the column mentioned in the order specified after the sorting column. Hence the result will be sorted based on Product Name in ascending order.
Please note that you can only use one sheet in a LOOKUP formula. This is how you can use LOOKUP formula to fetch data from a sheet. But the usage of LOOKUP does not stop here.
Data fetched using a LOOKUP formula can be passed on to any control using the control’s reference. To best understand this, lets restructure our app. Let’s add two dropdown controls, where we will fetch only the Product Category in the first control, select the category and basis that we will fetch the Product Name in the next drop down.
So to start off with, we will add a LOOKUP formula in the first drop down. The formula would look like -
Save and Validate the formula. Now to pass the selected category to the next control, we need to use the control’s unique name as a reference and the column name whose data we need to pass. Thus the formula will look like - category.productcategory where category is the unique name of the first dropdown and productcategory is the column name.
Now we need to use this reference in the next LOOKUP’s filter criteria since we want to display all the products of the selected category. Hence the LOOKUP formula for the Products drop down will be like -
Click on Save and Validate button, close the formula modal and click on Preview button to see whether we are able to fetch products for the selected category.
This way you can add and use formulas on any control in Studio.