Calculating Values
Calculations can be used on the Format tab and the Filter tab.
If calculations are not evaluated when submissions are exported, then make sure the Calc field is checked off for the column that should be calculated.
There are two different calculation engines available in Sync & Save:
1. NCalc (Default)
NCalc can perform a wide variety of mathematical and logical calculations:
| Value | Result |
| {AutombileExpenses}*100 | Multiplies the field by 100 |
| {AutomobileExpenses}+{GasExpenses} | Adds the two fields together |
| '{Status}'='Approved' and '{JobType}'='Billable' | True if {Status} is "Approved" and {JobType} is "Billable", otherwise False |
| if({TotalHours}>40,'OVERTIME','REGULAR') | "OVERTIME" if {TotalHours} is greater than 40, otherwise "REGULAR" |
See the full list of available operators and functions.
All values that you expect to be text should be surrounded by single quotes:
- '{UserName}'='jsali@doforms.com'
If you expect the value to be a number, you do not need to surround it in quotes:
- {FormVersion}=4
All date and time values should be surrounded with sharps:
- #{BirthDate}# > #1/1/2012#
If you want to make sure a field is not blank (the last characters are two single quotes):
- '{UserName}'!=''
You can combine formulas using "and" and "or" as well as parenthesis. Note:The keywords "and" and "or" must be lowercase.
- '{UserName}'='jsali@doforms.com' and {JobNumber}=25
- '{UserName}'='jsali@doforms.com' and ({JobNumber}=25 or {JobNumber}=10)
2. CalcEngine (Available beginning 1.13.11)
CalcEngine uses Excel style functions to provide a wider variety of mathematical, logical and text formatting functions than NCalc. CalcEngine is based on this project.
To use CalcEngine instead of NCalc, choose Tools | Options | Jobs | General and check the "Use Excel style calculation functions" option.
| Value | Result |
| {AutombileExpenses}*100 | Multiplies the field by 100 |
| {AutomobileExpenses}+{GasExpenses} | Adds the two fields together |
| AND("{Status}"="Approved", "{JobType}"="Billable") | True if {Status} is "Approved" and {JobType} is "Billable", otherwise False |
| IF({TotalHours}>40,"OVERTIME","REGULAR") | "OVERTIME" if {TotalHours} is greater than 40, otherwise "REGULAR" |
All values that you expect to be text should be surrounded by double quotes.
Operators
- <
- >
- =
- <=
- >=
- *
- /
- ^
Logical
- AND(logical1, [logical2], ...)
- OR(logical1, [logical2], ...)
- NOT(logical)
- IF(logical_Test, [value_if_true], [value_if_false])
- TRUE
- FALSE
Math
- ABS(number)
- ACOS(number)
- ASIN(number)
- ATAN(number)
- ATAN2(number)
- CEILING(number)
- COS(number)
- COSH(number)
- EXP(number)
- FLOOR(number)
- INT(number)
- LN(number)
- LOG(number, [base])
- LOG10(number)
- PI
- POWER(number, power)
- RAND
- RANDBETWEEN(bottom, top)
- ROUND(number, decimal_places) Available 1.13.26
- SIGN(number)
- SIN(number)
- SINH(number)
- SQRT(number)
- SUM(number1, [number2], ...)
- SUMIF(range, criteria, [sum_range])
- TAN(number)
- TANH(number)
- TRUNC(number)
Statistical
- AVERAGE(number1, [number2], ...)
- AVERAGEA(value1, [value2], ...)
- COUNT(value1, [value2], ...)
- COUNTA(value1, [value2], ...)
- COUNTBLANK(value1, [value2], ...)
- COUNTIF(range, criteria)
- MAX(number1, [number2], ...)
- MAXA(value1, [value2], ...)
- MIN(number1, [number2], ...)
- MINA(value1, [value2], ...)
- STDEV(number1, [number2], ...)
- STDEVA(value1, [value2], ...)
- STDEVP(number1, [number2], ...)
- STDEVPA(value1, [value2], ...)
- VAR(number1, [number2], ...)
- VARA(value1, [value2], ...)
- VARP(number1, [number2], ...)
- VARPA(value1, [value2], ...)
Text
- CHAR(number)
- CODE(text)
- CONCATENATE(text1, [text2], ...)
- FIND(find_text, within_text, [start_num])
- LEFT(text, [num_chars])
- LEN(text)
- LOWER(text)
- MID(text, start_num, num_chars)
- PROPER(text)
- REPLACE(old_text, start_num, num_chars, new_text)
- REPT(text, number_times)
- RIGHT(text, [num_chars])
- SEARCH(find_text, within_text, [start_num])
- SUBSTITUTE(text, old_text, new_text, [instance_num])
- T(value)
- TEXT(value, format_text)
- TRIM(text)
- UPPER(text)
- VALUE(text)