Validations in Subform

Introduction

Feature(s)

Validations on form submission means that the form can be submitted successfully only when the filled data meets the validation conditions. You can also set validations for submitting subform data, but the settings for subfields are slightly different from those of main form fields. Therefore, this article will introduce several commonly used methods for setting subform validation.

Application Scenario(s)

Preview

Taking the section "Comprehensive Validation" as an example, the form can only be successfully submitted if the following conditions are met: a. When gender is equal to "female", the age should be greater than 55 years old; b. When the gender is equal to "male", the age should be greater than 60 years old.

Where to Set

1. After designing the form, click Form Properties, and set Add Condition under Validations on form submission.

2. In Validations on submission, you need to enter:

After the settings, click OK to save the content you enter.

Methods for Setting Validations in Subform

Number Validation

Number validation refers to validating the calculation results of one or multiple Number fields. For example, you can validate subfield data through the MAX and the SUMPRODUCT functions.

1. Validate data through the MAX function.

For example, you can set that the Number field of every single record should be less than 100. That is to say, the maximum number in all records should be less than 100. You can set formulas as follows:

MAX (Order Details.Number)<100

2. Validate data through the SUMPRODUCT function.

For example, you can set the total price to be less than 100. That is, the sum of the prices obtained by multiplying the unit price and quantity of each product is less than 100. You can set the formula as follows:

SUMPRODUCT(Order Details.Unit Price, Order Details.Number)<100

Text Validation

Text validation refers to validating the content of text-type fields in a subform.

For example, in validating the content of an option, if you want to set that a subform can be submitted only when the Yes option is ticked, you can set the formula as follows:

TEXT(UNION(SubForm.Single Select))=='Yes'

Comprehensive Validation

You can set multiple validations on the values of multiple subfields. When the validation conditions are too complicated, the following methods can be used to set the validation conditions:

1. Create a Number field as an auxiliary field in the subform, and set the formula as:

IF(logic conditions,0,1)

2. Set the form validation formula as follows:

SUM(Number field)==0

For example, the values in a subform should meet the following conditions: a. When the gender is equal to "female", the age should be greater than 55 years old; b. When the gender is equal to "male", the age should be greater than 60 years old. If any row of records fails to meet the conditions, the form cannot be submitted.

1. Set the formula for Field (Number) as follows:

IF (OR (AND(Subform.Gender == 'Female', Subform.Age > 55), AND(Subform.Gender == 'Male', Subform.Age > 60)), 0,1)

2. Set the form validation formula as follows:

SUM (Subform.Field (Number)) ==0

Note:

If you want to set Field (Number) as invisible, you have to set Hidden Field Default Value to Always Re-calculate in Form Properties.