This article will guide you on how to add and use Formulas in DronaHQ Studio.
In Studio, go to Apps, click on any existing app, on the Studio builder page, click on any control and then click on ‘F’ icon to put the formula to it. As soon as the control is added on the builder page, a formula menu will open. Using this menu, you can add formula to perform calculations as per your requirement. This formula menu will look as shown in the below image -
Formulas can be added in Studio in the following ways,
- Functions and Keywords are to be written in ALL CAPS
- No need for = sign to write a Functions unlike Excel
- Control name, Sheet name, Column name has to be exact and is case sensitive
- Characters or String data need to be within “”, e.g. "DronaHQ Studio" . But if your data contains an apostrophe(’) then you need to put the data within double quotes, e.g. if you want to use John’s , then use it as "John’s" .
Using Static Data
Click on Static Data option on the formula menu to input static values for the control. For instance, if you want to show the text Studio in the text box, you need to put the text Studio in the Static data box and click on save button.
Once you click on Save, preview the app by clicking on the play icon.
To know more about different ways to bind static data, please read the article - Binding data to controls using static data.
Use this option to fetch data from any Sheet. When you click on Sheet option, you will get a list of all the sheets available in the account. When you select any sheet, you will get list of all the columns under that specific sheet. Select the desired column and click on Save button to the formula for the control.
You can also add filter criteria while fetching data from sheet or select multiple columns by clicking on Wizard or Add Filter button on the menu popups. Both these options will open the Sheet formula wizard using which you can put additional columns/filter criteria (as shown in the below image).
When you click on the Insert button, the LOOKUP() formula built using the wizard will be inserted in the formula box. Click on the play icon to preview the output.
To know more about how to use LOOKUP() formulas and various operators that can be applied to filter criteria in a LOOKUP() formula please refer the articles - Binding data to controls from Sheets and How to use Operators.
You can perform calculations/operations by fetching the data from other controls on the current screen or any other screen in the app. For e.g. if you want to pass data from a text control to a number control then you will need to follow these steps:
- Add Text and Number control
- On the Number control, click on ‘F’ icon to open the Formula menu
- On the formula menu, click on Controls options. This will open a list of all available controls in the app, showing their unique name. Select the desired control from the list to add the control’s reference in the target control (as shown in the below image).
On app preview, when you type data in the Text box, that data will also be passed to Number and the Number box will populate the data written in the Text box. To know more about different ways to use Control’s reference, please read the article - Binding data to controls using a control’s reference.
- Using Functions - Functions are a set of procedures to perform specific tasks. It accepts input arguments or values and returns computed value based on the inputs. You can also pass static data to these functions, control reference, or LOOKUP() functions to the existing formulas. For e.g.
SUM(1,2) - will result into 3 i.e. addition of 1 & 2
AVERAGE(number, number1, number2) - where the number, number1, number 2 are three different number type controls.
To know about how to use formulas in various ways and all the functions that are supported in Studio, please read the section - Binding Data using Functions and Formulas and article - All Supported Formula.
- Using Keywords - Keywords are special values that do not require any input arguments or input data and can be used directly in any function or in formula box to fetch the data.
Let’s say you want to fetch the logged-in User’s name and email address, then simply use Keywords USERNAME & USEREMAIL directly in the formula box. If you want to display both the values together, then you can use a function called CONCATENATE which will join these values in a control. E.g. Put the formula in the formula box after clicking on the formula options after clicking in ‘F’, CONCATENATE(USERNAME," - ", USEREMAIL). This formula will look like this -
Clicking preview app, the formula will look like this -