A Formula is a calculation, such as addition or subtraction, to be performed on one or more variables. In the Forms Designer, formulas are used to do a great many of things such as totaling a column of controls, converting measurements from one type of unit to another, displaying a variable, and much more.
A formula can be used in any object/control that has a Text field in its properties panel.
The Insert drop down contains many tools to add to a formula in order to take advantage of all of what Surety Pro has to offer.
Important Terms to Know
- Literal
- a value that you want shown exactly as entered
- Control
- any object placed on the canvas of a custom form. Can include text boxes, labels, maps, drop downs and more.
- Control Name
- This is the name given to the Object in the Forms Designer. (It is Case Sensitive and does not support special characters!)
- camelCase
- The first letter of the first word is a lower case letter and all subsequent first letters are capitalized for a compound word i.e. camelCase, bearingToMapCenter (Lat Lon) or distanceToMapCenter (Lat Lon).
- Variable
- A set of predefined values that always start with an @ sign which can reference values of the map, company, or client information.
- There are 2 categories of variables: Account Variables & Parcel Variables
- Group
- A set of related controls that are created using Lists or Grids
- List
- is a set of controls that creates a way to store multiple lines of data and be able to select one of them to populate a text field. (Assigned a group number)
- Grid
- A group of objects that are saved together and can be placed all at once on a form. (Assigned a group numbers)
- "if" Statement
- A formula style that is used to perform defined actions based on defined or referenced values.
Control Name
The Control Name is the name given to each control on the canvas. These names are setup within the Control Properties panel after selecting an individual control.
- Control names should be unique on a form unless they are part of a group or need to be used in a Sum or Max formula.
- Control Names should not contain any special characters, only letters and/or numbers. See: Caveats
Math (Eval): Use Symbols
Eval (see below) or evaluation symbols can be used within formula containing numbers or controls containing numbers.
Remember to place the equals sign (=) before the formula!
Add +
=4+2
will generate
6
Control names can also be used in place of numbers. Say we have 2 different Controls;
- TotalA had a value of 10
- TotalB contained a value of 20
=TotalA + TotalB
will equal
30
- Subtract -
- Multiply *
- Divide /
Evals: Use Symbols
Evals can include control names and constants. Constants are something(in this case numbers) that are hard coded and will not vary or change.
- Equal =
- Greater than equal >=
- Less than equal <=
- Greater than >
- Less than <
- Not equal to <>
- And &&
- Or ||
Color coded referenced controls
When referencing another control name in the form, the Forms Designer assists with showing what controls are referenced by coloring them.
Example:
This particular text box control, called FGTotalApplied, contains this formula.
=ActualAcres * FGRatePerAcre
Since it is referencing 2 control names, when you click on FGTotalApplied to edit it, the 2 controls that it is referencing in its formula on the visible form are now colored in black. If there is another control that is colored red, the red colored control contains a reference to the FGTotalApplied control to get its value.
 Coloring of referenced controls |
Literal
A Literal is something that you want shown exactly how you enter it.
Literal is contained in quotes
''
or ""
Example: ="8 + 4" will show up like this 8+4
while =8 + 4 will show up as this
12
Another Example
='(' + TotalA + ')'
if TotalA equals 10 then the following will be what you see show up in the form
(10)
Variables
A set of predefined values that always start with an @ sign which can reference values of the map, company, or client information.
These are Predefined by us and can not be created by the user
=@myVariable
See: Account Variables & Parcel Variables
Group
A set of related controls that are created using Lists or Grids
If Statements
If Statements are very important to use when wanting to have one control's value react with another control's value when it is changed.
The basic structure of the if formula.
if () {} An if statement evaluates what is in the parentheses to determine if the action in curly brackets is to be performed. If A happens then the result is C =if(A){C} Curly brackets { are placed at the beginning and a } is placed at the end of an action to be performed in an if statement.
Curly brackets { }
elseif statement
elseif(){}An elseif statement evaluates what is in the parentheses to determine if the action in curly brackets is to be performed.
You would use an elseif if there are additional actions.
An elseif is case sensitive and is always lower case. An elseif is optional.
A curly bracket { is placed at the beginning and a } is placed at the end of thee action to be performed.
You would use an elseif if there are additional actions.
Example: =if(A=1){C}
elseif(A=2){D}
else
else { } An open curly bracket { is placed at the beginning and a closed curly bracket } is placed at the end of thee action to be performed.
You would use an else for the finality of the evaluations. The finality of the evaluations is provided if all previous evaluations are false.
An else is case sensitive and is always lower case. An else is optional. if(A=1){C}
elseif(A=2){D}
else{E}if statements can include control names, variables or literals.
Example of an if statement.
Compare two literals if ('foo' = 'bar'){
'foobar'
}Compare a control (foo) and a literal 'bar' if (foo = 'bar'){
'foobar'
}Compare a control (foo) to an empty string if (foo = null){
'foobar'
}Incorrect way to compare a control to an empty string if (foo = ''){
'Incorrect'
}Another Example
In this example the control that I will be qualifying my if statement off of is one that contains a numerical value. I want to have my result vary depending on the number in that text control.
if(Subtotal >= 3,000){'-500'}
elseif(Subtotal < 3,000){'0'}
elseif(Subtotal = null) {'0'}
Using a formula in a drop-down list
An if statement is commonly used in drop-downs that require a different set of data to choose from depending on the choice made in an earlier selection in the form. Here we have a formula that looks at the first unit of measurement(Unit1) and then gives different options depending on what that measurement(Unit1) was.
When writing formulas for drop-down lists, 2 things are necessary.
1)place the string "VisualList:" after the equals(=) sign to signify that the list needs to be created from this data.
2)Place \n on the beginning of the next value so it doesn't show up all on one line.
=VisualList:if (Unit1 = 'fl oz'){
'fl oz\npt\nqts\ngal'
}elseif(Unit1 = 'pt'){
'fl oz\npt\nqts\ngal'
}elseif(Unit1 = 'qts'){
'fl oz\npt\nqts\ngal'
}elseif(Unit1 = 'gal'){
'fl oz\npt\nqts\ngal'
}elseif(Unit1 = 'dry oz'){
'dry oz\nlbs\ntons'
}elseif(Unit1 = 'lbs'){
'dry oz\nlbs\ntons'
}elseif(Unit1 = 'tons'){
'dry oz\nlbs\ntons'
}
Concatenating items to a Target Control list item.
"VisualAppend:" is to be used for adding items to the end of a list's Target Control drop down.
See the Forms Designer - New Custom List support page for more information.
Notes:
Remember to always test your formulas and pay attention to the case of the letters used in the formula.
Troubleshooting:
If your form stalls after clicking on it in the forms List, make sure all of the if statements are completed with a curly bracket "}" at the end.
Learn how to use a formula to pull data from previous form.
See Also: