Formulas
The Formula form field property lets you define the content of a field based on specific logical operations performed on one or more other text fields. Text or Area Text fields can be used as operands in formulas. Each field is defined as either a String operand or a Number operand, as follows:
- Fields with a validation type of 'Numeric' or 'Numeric decimal' are defined as Number operands.
- Fields with validation types other than 'Numeric' or 'Numeric decimal' are defined as String operands.
Supported Operations for String Operands
- Concatenation
- Conversion
- Case Statement
- Regular Expression Parsing
Supported Operations for Numerical Operands
- Addition
- Subtraction
- Multiplication
- Division
- Conversion
- Case Statement
Supported Date Field Formulas
- Add Days
- Add Hours
- Add Months
- Add Years
- Date Difference
Formula Examples
Only 'Text' and 'Area' fields are allowed as operands in formulas expressions.
Each 'Text' field can be defined as String (by default) or Number operand.
Fields with validation types 'Numeric' and 'Numeric decimal' will be defined as Number operands.
Fields with other validation types will be defined as String operands
Allowed operations for Strings.
Operations |
Operators |
Samples |
Concatenation |
+ |
@FORMelement1+@FORMelement2+' senior manager.' When element1 = 'John', element2 = 'Smith'. Result will be: 'JohnSmith senior manager.' Note: If you need add some constant text to formula expression, add this text with single quotas. If you need add quota, you should add escape character before it: @FORMelement1+'employee\'s' |
Conversion |
convert |
convert(@FORMelement1, 'Int32') + 10 Result will be integer number convert(@FORMelement1, 'Decimal') Result will be decimal number convert(@FORMelement1, 'DateTime') convert('08/16/2017', 'DateTime') |
Case statement |
case |
case when @FORMelement1='NYC' then 'New York' when @FORMelement1='DC' then 'Washington, D.C' else @FORMelement1 end + ' the best city.' when elemen1='NYC' result will be 'New York the best city.' when elemen1='DC' result will be 'Washington, D.C the best city.' when elemen1='Denver' result will be 'Denver the best city.' Allowed operations with 'when' statement:
|
Regular Expression Parsing |
RegExExtract |
RegExExtract(@FORMelement1,'\-(.*?)\-') If the value entered into FORMelement1 = '123-456-789' the result will be 456 For assistance building and testing regular expressions: https://regexr.com/ |
Allowed operations for Numbers.
Operations |
Operators |
Samples |
Addition |
+ |
@FORMelement1+@FORMelement2 When element1 = '1', element2 = '2'. Result will be: '3' |
Subtraction |
- |
@FORMelement1-@FORMelement2 When element1 = '3', element2 = '2'. Result will be: '1' |
Multiplication |
* |
@FORMelement1*@FORMelement2 When element1 = '3', element2 = '2'. Result will be: '6' |
Division |
/ |
@FORMelement1/@FORMelement2 When element1 = '6', element2 = '2'. Result will be: '3'. Note: You will get infinity '∞' in case of division by 0 |
Conversion |
convert |
convert(@FORMelement1+@FORMelement2, 'string') +' Dalmatians' When element1 = '100', element2 = '1'. Result will be: '101 Dalmatians'. |
Case statement |
case |
case when @FORMelement1/@FORMelement2 <= 0 then 0 when @FORMelement1/@FORMelement2 >= 100 then 100 else @FORMelement1/@FORMelement2 end when elemen1=1, elemen1=-2 result will be 0 when elemen1=1000, elemen1=2 result will be 100 when elemen1=100, elemen1=2 result will be 50 Allowed operations with 'when' statement:
|
Date Field Formulas
Operations |
Operators |
Samples |
Add Days |
AddDays(DateField,Days) |
AddDays(@FORMelement4,2) Adds 2 days to the date entered in Formelement4 |
Add Hours | AddHours(DateField,Hours) |
AddHours(@FORMelement4,2) Adds 2 Hours to the date entered in Formelement4 |
Add Months | AddMonths(DateField,Months) |
AddMonths(@FORMelement4,2) Adds 2 Months to the date entered in Formelement4 |
Add Years | AddYears(DateField,Years) |
AddYears(@FORMelement4,2) Adds 2 Years to the date entered in Formelement4 |
Date Difference |
DateDifference(StartDate,EndDate,IncludeWeekends) |
DateDifference(@FORMelement4,@FORMelement3,'yes' |