Skip to main content

Control - Formula

The formula control is read-only and cannot be edited. Once the formula is configured, the results can be automatically updated once the fields involved in the calculation are updated. The control supports two kinds of data operations, number and date.

  • 1. Calculation of numbers

    For example, Amount = Quantity * Unit Price. After entering the values of the Quantity and Unit Price fields, the amount is automatically calculated. The result can be displayed in thousandths or percentage format.

  • 2. Calculation of date and time

    • Duration
    • Time from the moment
    • Time plus/minus at a date

  • Effect:

To ensure the performance of the worksheet, when the number of worksheet records exceeds 10w rows, after modifying the formula configuration (for example, adjusting the calculation formula or adjusting the fields involved in the calculation) the historical data will not be updated, if you still need to update the historical data, you need to manually refresh.

I. Calculation of numbers​

Scenario: The purchase details need to include the purchased product, model, unit price, quantity and amount. Where the amount does not need to be entered manually, it can be calculated automatically by the formula.

1. Add a formula control​

2. Set the formula control​

Set the field name, type (number), units, and decimal places.

3. Set the calculation formula​

There are two types of numeric calculation formulas, system preset formulas and custom formulas.

1). Preset formula in the system​

If you choose a preset formula, you can just click to select the field without entering the calculation symbols. After saving, you can test the formula.

The formulas preset in the system are relatively single and only one type of operation can be used.

2). Custom formulas ​

If you need a mixture of addition, subtraction, multiplication and division to get the result, you need to customize the formula . You need to fill in the calculation symbols, and the English input status is required when entering the operation symbols (+-*/).

Custom formulas support multiple operations nested together, such as complex formulas for calculating ratios, etc.

In custom formulas you can also continue to use more system formulas in combination with custom formulas.

If the field value involved is null, it will be considered as 0

If unchecked, if the field value involved in the calculation is null, the formula value will also be null. If checked, it will be considered as 0.

This function is only available for custom formulas.

Fields that can be involved in the calculation

  • Numeric, Amount, Rollup

  • Formula (days from today is not supported to participate in the calculation again)

  • Foreign (It must be [Storage data], not [Display only].)

4. Set the display format​

For the calculation results, you can set the display format, such as decimal places, thousandths or percentage. You can also set the units.

  • Display by percentage

    The calculation result will be automatically multiplied by 100. For example, if the calculation result is 1, it will be displayed as 100%. If the result of the calculation is 0.99, it will be displayed as 99%.

  • If [Display by percentage] is checked, the units (prefix/suffix) are no longer displayed.

II. Calculation of the date​

1. Duration​

Scenario: The duration of the project is automatically calculated with the start date and the end date of the project.

Explanation of calculation logic

1). Formatting

  • 2 types of formatting.

    • Formatting 1: Start date 0:00, end date 24:00

    • Formatting 2: Start date 0:00, end date 00:00

Formatting only works for dates without a time part, if it contains a time part, the actual time will be involved in the calculation.

Example 1

Start date: 2020-12-13 , End time: 2020-12-14 ,Unit: Day

  • If format 1 is selected, the actual time is calculated from 2020-12-13 00:00 to 2020-12-14 24:00 and the result is 2 days.

  • If format 2 is selected, the actual calculation is from 2020-12-13 00:00 to 2020-12-14 00:00, and the result is 1 day.

Example 2

Start date: 2020-12-13, End time: 2020-12-14 12:00, Unit: Hour

  • If format 1 is selected, the actual time is calculated from 2020-12-13 00:00 to 2020-12-14 12:00, and the result is 36 hours.

  • If format 2 is selected, the actual time calculated is from 2020-12-13 00:00 to 2020-12-14 12:00, and the result is still 36 hours. This is because the end time has a time component and is calculated by the actual time. This field is not formatted, only the start date is formatted.

2). Minimum unit

The output format is year, month, day, hour and minute, and if the result of the calculation is a decimal, only the integer part is taken.

Example: The number of days between July 1 and July 30 is 29.

If you choose the unit "days", the result is 29 days; if you choose the unit "months", the result is 0 months (the result is not approximated, but only the integer part is taken), if the result is 35 days, it is shown as 1 month.

3). Consider workday only

If you calculate the length of leave, Saturday and Sunday should not be counted. You can set your working days here. Unchecked days will not be counted in the date length.

4). Note

  • Calculation between two fields, date field and time field, is not available.

  • For calculations between time fields, formatting does not work.

2. Time plus/minus at a date​

As shown below, once the date of entry and the length of the probationary period are determined, the date of becoming a regular employee can be calculated automatically.

Tips:

  • A: Select a date field or a fixed date. Add or subtract time for this date field.

  • B: Write the formula for the calculation. The beginning must be + or -. Adding 1 month is +1M,subtracting 1 day is -1d. The year, month and day can be written together. For example, +1Y-1M+2d-3m.

    • Uppercase Y for year, uppercase M for month, lowercase d for day, lowercase h for hour, and lowercase m for minute.

  • C: The specific added hours parameter can be a field value, or you can write a fixed number.

  • D:The output format of the new date, you can set whether it is in date format or date/time format.

3. Time from the moment​

For example, you have set a deadline for a task and may need a field reminder for XX days left/XX days overdue.

A: Choose [Time from the moment]

B: Select whether it is target date minus today or today minus target date.

C: Choose the target date: it can be a fixed date or the value of a date field.

D: Select the unit of output, that is, day, week, month, or year.

E: If [Hide negative values] is checked, it will be displayed as empty.

The result of the calculation is not stored, it is only calculated in real time when the page is loaded, so the result cannot continue to be involved in other formula calculations, nor can the field values be referenced in the workflow, nor can they be counted in the statistics chart.

Have questions about this article？ Send us feedback