Skip to main content
Mitratech Success Center

Formulas

Add strings or basic mathematical values in fields and leverage these operands to either perform mathematical equations or concatenate string operands. This tutorial shows the difference between the three.

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')

Result will be DateTime

 

convert('08/16/2017', 'DateTime')

Result will be 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:

  • '=' - equal 

  • '<>' - not equal

Regular Expression Parsing

RegExExtract

RegExExtract(@FORMelement1,'\-(.*?)\-')

If the value entered into FORMelement1 = '123-456-789' the result will be 456

Note: The form element must be the first argument to the function and the single quotes are necessary around the regular expression

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:

  • '>' - more

  • '>=' - more or equal

  • '<' - less

  • '<=' less or equal

  • '=' - equal 

  • '<>' - not equal

 

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
(Must be Done in Text Field)

DateDifference(StartDate,EndDate,IncludeWeekends)

DateDifference(@FORMelement4,@FORMelement3,'yes'
Subtracts two date fields and includes weekends in the final count.

  • Was this article helpful?