Formula Rules

Introduction

Formulas support calculating values of fields automatically according to functions. You do not need to fill them again manually. This passage will introduce rules for editing formulas to help you better design and use them.

Editing Formula Page

You can edit Formula on this page:

Components of Formulas

Formulas usually include fields, functions, operators, and punctuation. The following is an example:

ROUND(Total Price/Number, 2)

Formula Rules

Rules of Adding Field(s)

All fields have a built-in ID. Though the fields are represented in their names when you are editing formulas, they are calculated with their IDs. Therefore, if you want to insert fields into a formula, you can follow these three ways:

Note:

1. You can only copy formulas through Copy Formula. Manually entering field names or directly pasting from other locations does not work.

2. Right-clicking with the mouse or using keyboard shortcuts is not supported for copying or cutting formulas in Safari.

Selecting Fields in Current/All Form Fields

Fields that you need to use in Formula can usually be selected from Fields. Only the last two parameters in the MAPX function need to be selected from all form fields.

1. Add fields from the current form.

You can add fields under Current Form.

2. Add fields from all form fields.

You can switch forms in the field-selecting area and select the fields you need.

Note:

Fields selected from Current Form are colored blue. Fields selected from all form fields are colored green.

Changing Variable(s)

If you need to change the variables in a formula, you can do so via the Change Variable feature, which can help you batch change variables, thus improving efficiency.

Click Change Variable, select variables for Select current variable and Select new variable respectively, and click OK.

Note

Note allows you to add information related to the formula, such as its purpose, usage, and so on. This can make the formula easier to understand, which facilitates managing and maintaining the formula in the future.

Click Note, enter a comment in the text box, and click OK.

Debug

Debug allows you to preview how the formula works and therefore verify whether it returns the desired data. This helps you to quickly adjust the formula.

Operators

The following calculation operators and comparison operators are supported in the formula editing of Jodoo:

Note:

== stands for the equality operator that checks if their operands are equal. = is not for the equality operator.

Punctuation

1. All punctuation used in formulas needs to be input in half-width format.

If you use full-width punctuation, an error message will appear: "Wrong Character".

2. When editing formulas, you need to use parentheses to group elements and then check for balanced parentheses in an expression.

If there are no parentheses or the parentheses do not match with each other, an error message will appear: "Syntax error. Identifiers aren't provided".

Inputting Rules of Functions

You can select functions in lists or input them manually. The system will associate automatically when you input functions. Correct functions will be displayed in purple.

Limits of the Number of Characters

When editing formulas, you need to pay attention that you can add at most 2,000 lines in each formula, and each line cannot be filled with more than 10,000 characters. If you need to input many characters in a single line, you can change lines by pressing the Enter key, rather than wait until the system changes lines automatically when characters are beyond the editing box.

If there are more than 2,000 lines in each formula or more than 10,000 characters in each line, you cannot save the formula completely and may fail to run the formula.

The Parameter Settings of Functions

You need to pay attention to the following aspects related to parameters when using functions:

1. The number of parameters.

You need to fill in the number of parameters in functions as required so that the formula can run normally.

For instance, the DATEDELTA function needs two parameters, which are start_date and days_later[_before]. If there is only one parameter, the formula cannot be calculated normally. An error message will appear: "DATEDELTA: Incorrect number of parameters".

2. Data types of parameters.

You need to follow function rules when editing formulas so that the data types of parameters are correct.

For example, all parameters in the DAYS function must be in the time format. If the parameter is in the text format, the formula will not be calculated normally. An error message will appear: "Incorrect parameter type. Invalid parameter number 1".

Formula Using in Subforms

In subforms, one field includes data of a whole column, which means that one field in subforms can include multiple values. For example, the Name field includes values of apple, orange, cherry, and strawberry.

Parameters of some functions like LEFT, LOWER, and TEXTDEPT only support single field values. These functions cannot be used to calculate subform fields in main forms directly.

You need to change data in some columns of subforms into small letters and summarize them in main form fields.

Data Types of Return Values

When editing formulas in fields, you need to know that different fields correspond to different data types of return values of formulas. You may not obtain correct results if not meeting the rules.

For example, when you edit formulas in number fields, the data type of return values in number fields must be number. If the formula is TODAY(), it cannot be calculated, this is because the type for the return value of TODAY function is a date value, which does not meet the requirements of number fields.

Form fields that support editing formulas require the following data types for return values of different formulas:

Extended Reading (Better for Advanced Users)

According to the requirements of data types for return values of different formulas, you may find that the data type of return values in Single Line fields can also be number, except text.

This is because some data types can be supported to transform automatically in Jodoo. For example, number two can be transformed automatically into text two. Any number can be transformed as the above.

But text cannot be transformed. Text "Alice" cannot be transformed into a number. Therefore, the type of return values of formulas in Number fields can only be number.

When editing formulas, you can insert fields into formulas. Data types of fields inserted into formulas are not completely the same as data types of return values of formulas in fields.

For example, when you edit formulas in Single Line fields, the type of return values of formulas can be number values. But when you insert a Single Line field in formulas, the type of the Single Line field can only be text value.

The following shows common data types after inserting fields into formulas: