Published at: 2025-10-30
Validation Rules
I. Use Cases for Validation Rules
When creating or editing object data, it is necessary to validate the information entered on the page before saving to ensure it is legal or meets business requirements. For example, when saving Account information, the system checks whether the [Mobile Number] field meets the requirements. If it does not, you can configure the validation rule to block the save.
II. User Guide
2.1 Accessing the Feature
Preset Object/Custom Object Management -> Validation Rules
2.2 Basic Configuration of Validation Rules
- Use Cases
- Select New: Entering the new edit page via the New button will be blocked.
- Select Edit: Entering the new edit page via the Edit button on the details page will be blocked.
- If the result of the validation rule is true, a blocking action is executed.
- If blocking is set, the above content is prompted, and saving is not allowed.
- If not blocking is set, the above content is prompted, and you can choose whether to continue saving.
2.3 Complex Configuration
- Advanced Mode: To configure fields under the Primary Object and lookup-related object configurations, you need to enable Advanced Mode. However, if you configure related object or Primary Object’s summary fields or calculated fields in the formula, there may be delays! That is, after configuring the validation rule, it may take some time to take effect.
2.4 AI-Powered Validation Rule Support
- After adding a rule, invoke the AI Formula Assistant by entering “/” or clicking “AI Formula Assistant”.
- After entering the formula description, the AI Formula Assistant automatically generates the formula.
- Click “Copy” or “Apply” to apply the AI-generated calculation formula.
2.5 When Fields in Formulas Are Null
For non-numeric, currency, and percentage fields in calculation formulas, the “When fields in formulas are null” option does not take effect, and null values are directly involved in the calculation. If null values participate in mathematical operations or logical judgments, the program will report an error, and the final calculation result will be null. You need to use null-check functions to handle non-numeric, currency, and percentage type null fields separately.
- Supported field types for insertion: Currency, Date, DateTime, Email, Address, Multi-line Text, Decimal, Number, Percentage, Mobile, Single Choice, Single-line Text, Boolean, URL, Location Component, Check-in Component, Payment Component, Calculated Field, Summary Field, Calculated Field & Summary Field of Lookup Related Object
- Supported Functions:
| Return Type | Operator/Function | Parameters | Function | Remarks & Examples | |
|---|---|---|---|---|---|
| General | () | Denotes operator precedence | |||
| Depends on input parameters | IF(logical_test, value_if_true, value_if_false) | Number of parameters: 3, Parameter type: The first is Boolean, the second and third must be of the same type | Determines if the condition is true; if true, returns return_true_value, otherwise returns return_false_value where return_true_value and return_false_value must be of the same type, and the return type is also that type | Example: 34 = IF(true, 34, 52) | |
| CASE(expression, value1, result1, value2, result2,…,else_result) | Number of parameters: Variable, Parameter type: result(n) and else_result must be of the same type | Returns the value that meets the condition based on different conditions. The value of expression is compared with value(n), if value(x) matches the expression, it returns the corresponding result(n), if none match, it returns else_result, where result(n) and else_result must be of the same type, and the return type is also that type | Example: 33 = CASE(3, 2, 2, 3, 33, 1.3) | ||
| NULLVALUE(expression, substitute_expression) | Number of parameters: 2, Parameter type: The first is Boolean, the second can be of various types | Determines if the condition is null; if so, returns return_value, otherwise returns Null where the return type is also the type of the second parameter | Example: 1 = NULLVALUE(Null, 1) Null = NULLVALUE(5, 1) | ||
| Numeric | +,-,*,/ | Number of parameters: 2, Parameter type: Numeric, Percentage, Currency | Performs addition, subtraction, multiplication, and division operations on numbers | Example: 6 = (3+2)*6/5 | |
| - | Number of parameters: 2, Parameter type: Both are dates, in days | Calculates the difference between dates, in days | Example: a’s value is 1982-04-13 b’s value is 1982-03-20 24 = a - b | ||
| - | Number of parameters: 2, Parameter type: Both are date-times, in hours | Calculates the difference between date-times, in hours | Example: a’s value is 1982-04-13 17:00:00 b’s value is 1982-04-12 15:00:00 26 = a - b | ||
| - | Number of parameters: 2, Parameter type: Both are times, in hours | Calculates the difference between times, in hours | Example: a’s value is 17:00:00 b’s value is 15:00:00 2 = a - b | ||
| VALUE(string) | Number of parameters: 1, Parameter type: Text | Converts a text-formatted number into a number, supports negative numbers and decimals; if conversion fails, returns null | Example: -1982.0413 = VALUE(‘-1982.0413’) Null = VALUE(‘aaa’) | ||
| MIN(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the smaller of the two parameters | Example: 4 = MIN(4,13) | ||
| MAX(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the larger of the two parameters | Example: 13 = MAX(4,13) | ||
| MULTIPLE(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the result of multiplying number1 by number2 | Example: 52 = MULTIPLE(4,13) | ||
| MOD(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the integer part of number1 divided by number2 | Example: 3 = MOD(13,4) | ||
| ADDS(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the result of adding number1 to number2 | Example: 17 = ADDS(13,4) | ||
| SUBTRACTS(number1,number2) | Number of parameters: 2, Parameter type: Both are numeric | Returns the result of subtracting number2 from number1 | Example: 9 = SUBTRACTS(13,4) | ||
| YEAR(date) | Number of parameters: 1, Parameter type: Date or DateTime | Retrieves the year value from the parameter | Example: a = 1982-04-13 1982 = YEAR(a) | ||
| MONTH(date) | Number of parameters: 1, Parameter type: Date or DateTime | Retrieves the month value from the parameter | Example: a = 1982-04-13 4 = MONTH(a) | ||
| DAY(date) | Number of parameters: 1, Parameter type: Date or DateTime | Retrieves the day value from the parameter | Example: a = 1982-04-13 13 = DAY(a) | ||
| LEN(text) | Number of parameters: 1, Parameter type: Text | Returns the length of the string | 6=LEN(‘xiaoke’) | ||
| Percentage | +,-,*,/ | Number of parameters: 2, Parameter type: Numeric, Percentage, Currency | Performs addition, subtraction, multiplication, and division operations on numbers, returning in percentage form | ||
| Intermediate value of DateTime type | YEARS(number) | Number of parameters: 1, Parameter type: Numeric | Specifies the year value for date/date-time calculations | Obtains the year for calculations with date-time, date. Example: 2019-09-09=TODAY()+YEARS(1), where TODAY() returns 2018-09-09 | |
| MONTHS(number) | Number of parameters: 1, Parameter type: Numeric | Specifies the month value for date/date-time calculations | Obtains the month for calculations with date-time, date. Example: MONTHS(2) | ||
| DAYS(number) | Number of parameters: 1, Parameter type: Numeric | Specifies the day value for date/date-time calculations | Obtains the day for calculations with date-time, date. Example: DAYS(4) | ||
| HOURS(number) | Number of parameters: 1, Parameter type: Numeric | Specifies the hour value for date-time/time calculations | Obtains the hour for calculations with date-time, time. Example: HOURS(4) | ||
| MINUTES(number) | Number of parameters: 1, Parameter type: Numeric | Specifies the minute value for date-time/time calculations | Obtains the minute for calculations with date-time, time. Example: MINUTES(4) | ||
| Date | +,- | Number of parameters: 2, Parameter type: The first is date type, the second is time calculated by YEARS, MONTHS, DAYS | Performs date calculations to obtain a new date value | Example: Creatdate+DAYS(4), obtains the date 4 days after the creation time | |
| DATE(year,month,day) | Number of parameters: 3, Parameter type: Numeric | Creates a date type value from numbers | Example: 1982-4-13=DATE(1982,4,13) | ||
| DATEVALUE(string) | Number of parameters: 1, Parameter type: String | Creates a date type value from a string | Example: 1982-4-13=DATEVALUE(‘1982-04-13’) | ||
| TODAY() | No parameters | Returns the current date | |||
| DATETIMETODATE(datetime) | Number of parameters: 1, Parameter type: DateTime formula | Returns the date from a date-time field | Example: 1982-4-13=DATEVALUE(‘1982-04-13 17:00:00’) | ||
| DateTime | +,- | Number of parameters: 2, Parameter type: The first is date-time type, the second is time calculated by YEARS, MONTHS, DAYS, HOURS, MINUTES | Performs date-time calculations to obtain a new date-time value | Example: DeadlineTime-DAYS(1), obtains the time one day before the deadline | |
| DATETIMEVALUE(string) | Number of parameters: 1, Parameter type: String | Creates a date-time type value from a string | Example: 2001-08-24 15:45:25 = DATETIMEVALUE(‘2001-08-24 15:45:25’) | ||
| NOW() | No parameters | Returns the current date-time | |||
| Time | +,- | Number of parameters: 2, Parameter type: The first is date-time type, the second is time calculated by HOURS, MINUTES | Performs time calculations to obtain a new date-time value | Example: submitDailyReport-HOURS(1), obtains the time one hour before submitting the daily report. Note that the result of time type values after calculation is only between 00:00 and 23:59 | |
| DATETIMETOTIME(datetime) | Number of parameters: 1, Parameter type: DateTime formula | Returns the time from a date-time field | Example: 17:02:40=DATEVALUE(‘1982-04-13 17:02:40’) | ||
| Text | & | Number of parameters: 2, Parameter type: Both are text type | String concatenation | Example: “Zhang San” & “Li Si” results in Zhang San Li Si | |
| ’’ | Number of parameters: 1, Parameter type: String | Inserts a single-line text constant | Example: ‘Single-line text’ | ||
| ’’’’’’ | Number of parameters: 1, Parameter type: String | Inserts a multi-line text constant | Example: ‘'’Multi-line text’’’ | ||
| NUMBERSTRING(number) | Number of parameters: 1, Parameter type: Numeric, Currency | Converts a number into Chinese uppercase numeral display | Example: 壹拾玖万捌仟贰佰零肆点壹叁 = NUMBERSTRING(198204.13) | ||
| NUMBERSTRINGRMB(number) | Number of parameters: 1, Parameter type: Numeric, Currency | Converts a number into Chinese uppercase currency display, supporting up to jiao and fen | Example: 壹拾玖万捌仟贰佰零肆元壹角叁分 = NUMBERSTRINGRMB(198204.13) | ||
| Boolean | <,>, >=,<=,≠ | Number of parameters: 2, Parameter type: Both are numeric, percentage, currency | Compares numeric types, returning true or false | Example: true = 1 < 2; false = 2 < 1 | |
| AND(boolean1,boolean2,boolean3,…) | Number of parameters: Multiple, Parameter type: Boolean | Logical AND, if all conditions are true, the result is true | Example: true = AND(2>1, 5>3) | ||
| OR(boolean1,boolean2,…) | Number of parameters: Multiple, Parameter type: Boolean | Logical OR, if any condition is true, the result is true | Example: true = OR(2>1, 5<3) | ||
| NOT(boolean) | Number of parameters: 1, Parameter type: Boolean | If the condition is true, the result is false, and vice versa | Example: false = NOT(2>1) | ||
| ISNULL(expression) | Number of parameters: 1, Parameter type: Unlimited | Determines if the expression result is null; if null, returns true, otherwise false | Example: false = ISNULL(5) | ||
| ISNUMBER(string) | Number of parameters: 1, Parameter type: String | Determines if the expression string can be converted to a number | Example: true = ISNUMBER(‘5’) | ||
| STARTWITH(string1, string2) | Number of parameters: 2, Parameter type: String | Returns whether the string starts with the query string | Example: true = STARTWITH(‘abcdef’, ‘ab’) | ||
| ENDWITH(string1, string2) | Number of parameters: 2, Parameter type: String | Returns whether the string ends with the query string | Example: true = ENDWITH(‘aecdab’, ‘ab’) | ||
| EQUALS(string1, string2) | Number of parameters: 2, Parameter type: String | Returns whether the string is equal to the query string (case-sensitive) | Example: true = EQUALS(‘aecdab’, ‘aecdab’) | ||
| CONTAINS(string1, string2) | Number of parameters: 2, Parameter type: String | Returns the match result of the query string in the string (true, false) | Example: false = CONTAINS(‘abcdef’, ‘kl’) |
2.5 Notes
- Validation rules support defining multiple validation rules, and the system will validate them one by one according to the creation time.
- You can disable or enable validation rules; when disabled, validation rules do not take effect.
III. Common Issues and Troubleshooting for Validation Rules
3.1 Validation Rules Using Summary Fields Not Effective
3.1.1 Scenario Description
- Not blocked when creating, but blocked when editing.
- When creating, the summary field clearly has a summary value and meets the validation rule conditions, but it is not triggered.
3.1.2 Cause of the Problem
The validation rule directly or indirectly uses summary fields in lookup-related objects, and the summary field summarizes one of the following three situations: current object fields, current object’s Primary Object fields, or current object’s lookup-related object fields. When editing, the validation uses the last saved summary value in the database, not the real-time summary field value seen on the page, so there will be deviations when the validation rule is executed. When creating, the summary field value has not yet been stored in the database, so the validation rule does not take effect.
- You can understand the scenario again with the cause of the problem.
In the order details, set a validation rule: block when the available quantity < 0.
The existing data is:
Product Object: Mineral Water - Total Inventory 20, Sold 0
Create an order and add order detail data: Order Detail Product - Mineral Water, Detail Quantity 30; the displayed available quantity (calculated) is -10; according to the validation rule (available quantity < 0), it should be blocked, but it can be saved successfully.
The existing data is:
Product Object: Mineral Water - Total Inventory 20, Sold 0
Create an order and add order detail data: Order Detail Product - Mineral Water, Detail Quantity 30; the displayed available quantity (calculated) is -10; according to the validation rule (available quantity < 0), it should be blocked, but it can be saved successfully.
3.1.3 Solution
Use the pre-validation function configured in the save button for new and edit saves to block the save using summary fields.
3.2 Validation Rules Using the Primary Dept. of the Responsible Person in the Object Not Effective
3.2.1 Scenario Description
- Using the Primary Dept. of the responsible person under this object.
- Using the responsible person’s Dept. in the related object.
- Jointly using the Primary Dept. of the responsible person under this object and the responsible person’s Dept. in the related object.
3.2.2 Cause of the Problem
When submitting new data, the “Primary Dept.” field and the “Dept. of the responsible person” field have no values. The Primary Dept. is stored in the database after data submission (after the validation rule is executed).
3.2.3 Solution
Configure the pre-validation function in the save button for new and edit saves, and bring the Dept. information to the personnel field through the responsible person’s ID. The operation is relatively complex, please contact implementation for configuration.
3.3 How Was Data Saved When It Does Not Meet Validation Rules When Viewing Data?
3.3.1 Scenario Description
In the registration form object, a validation rule is set: [Cannot save if the student’s company email or personal email is empty], but when viewing the data, it is found that there are still data with empty emails.
3.3.2 Cause of the Problem
Reason 1: There was a value when creating the data, but the data was directly deleted using a function later, so it did not go through validation.
Reason 2: The data was created first, and the validation rule was created later.
Reason 2: The data was created first, and the validation rule was created later.
3.3.3 Solution
Method 1: Check the modification records to see if any function has modified the data.
Method 2: Compare the creation time of the validation rule and the data.
Method 2: Compare the creation time of the validation rule and the data.
3.4 Validation Rules Using Calculated/Summary Fields Not Effective When Values Are Not Filled
3.4.1 Scenario Description
In the order object, a validation rule is set: [Order Total Amount (Original Currency) <= 0 cannot be saved].
However, when creating data, the order total amount (Original Currency) is not filled, yet it can be saved successfully.
3.4.2 Cause of the Problem
At this time, the user thinks that when the value is null, it is 0, so it does not meet the validation rule and should be saved successfully. However, when the value is null, it does not participate in the validation rule calculation.
3.4.3 Solution
Set [When fields in formulas are null - default to zero] in the validation rule.
3.5 Default to Zero When Fields in Validation Rule Formulas Are Null, Configuration Not Effective
3.5.1 Scenario Description
In the validation rule of the [Device Loan/Return] object, it is set that the return date - application date > 180, and it is set that when fields in formulas are null, default to zero. The validation rule does not meet the condition, so it should not block, but it blocks when submitting.
3.5.2 Cause of the Problem
For non-numeric, currency, and percentage fields in calculation formulas, the “When fields in formulas are null” option does not take effect, and null values are directly involved in the calculation. If null values participate in mathematical operations or logical judgments, the program will report an error, and the final calculation result will be null. You need to use null-check functions to handle non-numeric, currency, and percentage type null fields separately.
3.5.3 Solution
Use the null-check function ISNULL to handle non-numeric, currency, and percentage type null fields separately.
