Skip to content

Formula Calculation ​

The calculation formula feature enables users to calculate form component values through formulas, providing more powerful dynamic calculation and data processing capabilities, including the following operations:

1. Overview ​

In form logic design, calculation formulas are a powerful dynamic rule engine that allows developers or business personnel to process form data in real-time through function formulas and set conditional rules based on calculation results (such as field show/hide, required/disabled, value validation, etc.). Its core functionality covers numeric operations, text processing, time calculations, collection operations, and logical judgments, meeting dynamic interaction needs in complex business scenarios.

2. Configuration ​

Using calculation formulas is similar to using Excel function formulas, supporting calculations using fields already added to the form.

Example: The form has contract amount and received amount fields, and needs to calculate the unpaid amount based on these two fields.

Step 1: First select the SUM function formula for calculation, click the function to insert the function formula into the right content area;

计算公式1.png

Step 2: Select the contract amount and received amount fields, and manually type the - sign. A simple calculation formula is now complete!

计算公式2.png

You can create more complex formulas using the same approach.

3. How to View Function Descriptions ​

When hovering the mouse over a function, you can view the function description and example.

计算公式3.png

4. Function Reference ​

Function NameDescriptionExample
TONUMBERConvert parameter to numberTONUMBER("1.5") = 1.5, TONUMBER("2.4e") = 2.4, TONUMBER("ABC") = 0
ADDGet the sum of two numbersADD(1, 2) = 3
SUBGet the difference of two numbersSUB(10, 1) = 9
MULGet the product of two numbersMUL(2, 2) = 4
DIVGet the quotient of two numbersDIV(10, 2) = 5
SUMGet the sum of values in a collectionSUM(1,2,3) = 6, SUM([5, 6, 7]) = 18
MAXGet the maximum value in parameter listMAX(1, 5, 10) = 10
MINGet the minimum value in parameter listMIN(1, 5, 10) = 1
ABSGet the absolute value of a numberSUM(1,2,3) = 6, SUM([5, 6, 7]) = 18
AVGGet the average of parameter listGet the average of parameter list
MODGet the remainder of two numbersMOD(10, 3) = 1
SQRTGet the positive square root of a numberSQRT(9) = 3
POWERGet the power of a specified numberPOWER(2, 4) = 16
RANDRound a decimal to specified digitsRAND() = 0.75348173001531
PIGet piPI() = 3.141592653589793
ROUNDRound a decimal to specified digitsROUND(3.149, 2) = 3.15, ROUND(3.149) = 3
FLOORGet the floor value of a specified numberFLOOR(1.93) = 1
CEILGet the ceiling value of a specified numberCEIL(1.93) = 2
FIXEDKeep a decimal to specified decimal placesFIXED(1.93, 1) = 1.9
TOCHINSESAMOUNTGet the Chinese uppercase amount of a specified numberTOCHINSESAMOUNT(32.14) = "叁拾贰元壹角肆分"
testExample of extended custom calculation functiontest(val) == !!val
STARTSWITHCheck if string starts with specified stringSTARTSWITH("ABCDEF","ABC") = true, STARTSWITH("ABCDEF","AD") = false
SLICELEFTGet a string of specified length from the startSLICELEFT("ABCDE", 3) = "ABC"
SLICERIGHTGet a string of specified length from the endSLICERIGHT("ABCDE", 3) = "CDE"
TOLOWERConvert all uppercase letters in string to lowercaseTOUPPER("ABCD") = "abcd"
TOUPPERConvert all lowercase letters in string to uppercaseTOUPPER("abcd") = "ABCD"
INCLUDESCheck if string contains specified stringINCLUDES("ABCD", "BC") = 1, INCLUDES("ABCD", "E") = -1
CONCATConcatenate all parameters, return concatenated stringCONCAT("ABC", "DEF") = "ABCDEF"
REPLACEReplace part of text in string with different text, only replace first matchREPLACE("AbAc","A","1") = "1bAc"
REPLACEALLReplace part of text in string with different text, replace all matchesREPLACEALL("AbAc","A","1") = "1b1c"
TRIMRemove spaces before and after stringTRIM("\t\n A BC \t\n") = "A BC"
NOWGet current timeNOW() = "2024-03-15 12:08:31"
TODAYGet today's dateTODAY() = "2024-03-15"
YEARGet year of specified dateYEAR("2024-03-15 12:08:31") = 2024
MONTHGet month of specified dateSUBYEAR("2024-03-15 12:08:31", 2) = 2022-03-15 12:08:31, SUBYEAR("2024-03-15 12:08:31", 3, "YYYY-MM-DD") = 2021-03-15
DAYGet day of specified dateSUBYEAR("2024-03-15 12:08:31", 2) = 2022-03-15 12:08:31, SUBYEAR("2024-03-15 12:08:31", 3, "YYYY-MM-DD") = 2021-03-15
HOURGet hour of specified dateHOUR("2024-03-15 12:08:31") = 12
MINUTEGet minute of specified dateMINUTE("2024-03-15 12:08:31") = 8
SECONDGet second of specified dateSECOND("2024-03-15 12:08:31") = 31
ADDYEARAdd years to specified dateADDYEAR("2024-03-15 12:08:31", 2) = 2026-03-15 12:08:31, ADDYEAR("2024-03-15 12:08:31", 3, "YYYY-MM-DD") = 2027-03-15
SUBYEARSubtract years from specified dateSUBYEAR("2024-03-15 12:08:31", 2) = 2022-03-15 12:08:31, SUBYEAR("2024-03-15 12:08:31", 3, "YYYY-MM-DD") = 2021-03-15
ADDMONTHAdd months to specified dateADDMONTH("2024-03-15 12:08:31", 2) = 2024-05-15 12:08:31, ADDMONTH("2024-03-15 12:08:31", 10, "YYYY-MM-DD") = 2025-01-15
SUBWEEKSubtract weeks from specified dateSUBWEEK("2024-03-15 12:08:31", 2) = 2024-03-01 12:08:31, SUBWEEK("2024-03-15 12:08:31", 4, "YYYY-MM-DD") = 2024-02-16
ADDMINUTEAdd minutes to specified timeADDMINUTE("2024-03-15 12:08:31", 30) = 2024-03-15 12:38:31, ADDMINUTE("2024-03-15 12:08:31", 55, "HH:mm:ss") = 13:03:31
SUBMINUTESubtract minutes from specified timeSUBMINUTE("2024-03-15 12:08:31", 30) = 2024-03-15 11:38:31, SUBMINUTE("2024-03-15 12:08:31", 55, "HH:mm:ss") = 11:13:31
ADDSECONDAdd seconds to specified timeADDSECOND("2024-03-15 12:08:31", 20) = 2024-03-15 12:08:51, ADDSECOND("2024-03-15 12:08:31", 40, "HH:mm:ss") = 12:09:11
SUBSECONDSubtract seconds from specified timeSUBSECOND("2024-03-15 12:08:31", 20) = 2024-03-15 12:08:11, SUBSECOND("2024-03-15 12:08:31", 40, "HH:mm:ss") = 12:07:51
DIFFDAYSGet the number of days between two datesDIFFDAYS("2024-01-15","2024-03-15") = 60
DIFFHOURSGet the number of hours between two times, keep two decimal placesDIFFHOURS("2024-01-15 12:00:00", "2024-01-16 15:00:00") = 27, DIFFHOURS("2024-01-15 12:00:00", "2024-01-15 12:30:00") = 0.5
DIFFMINUTESGet the number of minutes between two timesDIFFMINUTES("2024-01-15 12:00:00", "2024-01-15 12:30:00") = 30
TIMESTAMPGet timestamp of specified dateTIMESTAMP("2024-03-15 12:08:31") = 1710475711000
INCheck if second parameter is in collectionIN([1,2,3,4], 4) = true, IN([1,2,3,4], 5) = false
LENGet length of specified collectionLEN(["a", "b", "c"]) = 3
UNIONRemove duplicates from collection/parameters, return deduplicated collectionUNION(1, 3, 5, 1, 4, 3) = [1, 3, 5, 4], UNION([1, 2, 3, 1, 2, 3]) = [1, 2, 3]
COLUMNGet specified field from sub-form and return collectionCOLUMN([{key: 1}, {key: 2}, {key: 3}],"key") = [1, 2, 3], COLUMN([[{key: 1}, {key: 2}], [{key: 3}, {key: 4}]], "key") = [1, 2, 3, 4]
VALUEGet specified field from grouped formVALUE({key: 1}, "key", 2) = 1, VALUE({key: 1}, "value", 2) = 2, VALUE({list: {key: 1}}, "list.key") = 1
INTERSECTIONSETGet intersection of two setsINTERSECTIONSET([1, 3, 5], [1, 4, 3]) = [1, 3]
LISTGet collection of all parametersLIST([1, 3, 5], [1, 4, 3]) = [[1, 3, 5], [1, 4, 3]]
IFCheck if a condition is met, if yes return second parameter, otherwise return third parameterIF(false, 1, 0) = 0, IF(true, 1, 0) = 1
ANDLink expressions with "and", return true when all expressions are true, otherwise return falseAND(true, true) = true, AND(true, false) = false
ORLink expressions with "or", return true when one expression is true, otherwise return falseOR(false, false) = false, OR(false, true) = true
CASECheck if one or more conditions are met, return value of first met conditionCASE(IN([1,2,3,4], 5), 1, IN([1,2,3,4], 2), 2) = 2
NOTGet opposite value of a logical valueNOT(true) = false, NOT(false) = true
EQCheck if two values are equalEQ(100, 100) = true, EQ(100, 90) = false, EQ(100, "100") = false
NECheck if two values are not equalNE(100, 90) = true, NE(100, 100) = false, NE(100, "100") = true
GECheck if first value is greater than or equal to another valueGE(100, 90) = true, GE(100, 100) = true, GE(100, 110) = false
GTCheck if first value is greater than another valueGT(100, 90) = true, GT(100, 100) = false, GT(100, 110) = false
LECheck if first value is less than or equal to another valueLE(100, 90) = false, LE(100, 100) = true, LE(100, 110) = true
LTCheck if first value is less than another valueLT(100, 90) = false, LT(100, 100) = false, LT(100, 110) = true
TRUEReturn logical value trueTRUE() = true
FALSEReturn logical value falseFALSE() = false
DEFAULTCheck first parameter, if empty return second parameter, otherwise return first parameterDEFAULT("#FF7271", "#000") = "#FF7271", DEFAULT("", "#000") = "", DEFAULT(null, "#000") = "#000"
EMPTYCheck if parameter is emptyEMPTY("") = true, EMPTY([]) = true, EMPTY("0") = false
NOTEMPTYCheck if parameter is not emptyNOTEMPTY("") = false, NOTEMPTY([]) = false, NOTEMPTY("0") = true
ISNUMBERCheck if parameter is a numberISNUMBER("2.34") = true, ISNUMBER("2.4e") = false