The Expression screen allows you to define how a calculated field is determined. The Expression Builder section lists all of the fields currently placed on the form as well as functions. You can double-click on fields and functions to add them to the expression. The Legend lists operators that can be used to define an expression and corresponding buttons. Clicking on a button will insert the operator in the Expression field for further configuration.
The top field contains fields that are currently placed on the form as well as functions that can be used in expressions. The following are the available functions:
|
Function |
Description |
|---|---|
|
addDays(date,days) |
Adds the specified number of days to the date or date/time. Example: addDays(date(2011,12,2),30) This example adds 30 days to the date specified. |
|
addHours(date & time, hours) |
Adds the specified number of hours to the date & time value. Example: addHours([dateTime], 5) This example adds 5 hours to the date/time specified. |
|
addMinutes(date & time, hours) |
Adds the specified number of minutes to the date & time value. Example: addMinutes[dateTime], 10) This example adds 10 minutes to the date/time specified. |
|
addMonths(date,months) |
Adds the specified number of months to the date or date/time. Example: addMonths(date(2011,12,2),2) This example adds 2 months to the date specified. |
|
addSeconds(date & time, seconds) |
Adds the specified number of seconds to the date & time value. Example: addSeconds[dateTime], 45) This example adds 45 seconds to the date/time specified. |
|
addYears(date,years) |
Adds the specified number of years to the date or date/time. Example: addYears(date(2011,12,2),5) This example adds 5 years to the date specified. |
|
avg(repeating section,expression) |
Calculates the average of the fields of the field type specified in the specified repeating section or table. Example: avg([repeatername],[fieldname]) |
|
date(year,month,day) |
Creates a date with the specified year, month, and day. Example: date(2011,12,2) |
|
day(date) |
Populates the field with the day of the month specified for date or date/time Example: day(date(2011,12,2)) In this example, 2 is populated because this is the second day of the month. |
|
daysBetween(beginDate,endDate) |
Calculates the days between the dates specified. The following example compares a specified date to a field on the form: daysBetween(date(2013,2,22),[datefield]) |
|
dateTime(year,month,day,hours,minutes,seconds) |
Gets the date & time value from the given parameters. The time parameter values are expected to be in the range 00:00:00 – 23:59:59 |
|
month(date) |
Populates the month of the specified date as a number for date or date/time. Example: month(date(2011,12,2)) In this example, 12 is populated because this is the 12th month of the year. |
|
newline() |
Note:
This should only be used in conjunction with a Multiline Text Box field. Using this with any other field type could produce unexpected results. This will place a line break in a Multiline Text Box field. |
|
now( ) |
Populates the field with the current date and time. |
|
round(numeric value,decimal places) |
Rounds the number up or down using the decimal place number specified. This rounds the number up when the value of the place after the decimal place number specified is 5 or greater. If the value has a decimal value less than 5, the value will not be rounded up. The value is rounded down when the decimal value is less than 5. For example, if you have round(1.25,1), the value would be rounded up to 1.3 because the second place is 5. |
|
roundDown(numeric value,decimal places) |
Rounds the number down using the decimal place number specified. For example, if you have roundDown(1.25,1), the value would be rounded down to 1.2. |
|
roundUp(numeric value,decimal places) |
Rounds the number up using the decimal place number specified. For example, if you have roundUp(1.25,1), the value would be rounded up to 1.3. |
|
sum(repeating section,expression) |
Calculates the sum total of the fields of the field type specified in the specified repeating section or table. Example: sum([repeatername],[fieldname]) |
|
today( ) |
Populates the field with the current date. |
|
year(date) |
Populates the field with the year of the specified date or date/time. Example: year(date(2011,12,2)) |
Enter the calculation in the Expression field. You can validate the expression by clicking the Validate button. Errors in the expression are displayed below the Expression field.
|
Symbol |
Description |
|---|---|
|
[ ] |
Use brackets to reference fields you want to calculate within the expression. Reference the field by its ID found in the property grid when the field is selected. |
|
" " |
The value within quotation marks is a static value used in the calculated field. |
|
& |
This operator combines two (or more) values as one string value. |
|
+ |
Adds numeric values together. |
|
- |
Subtracts numeric values from each other. |
|
* |
Multiplies numeric values. |
|
/ |
Divides numeric values. |
|
( ) |
Parentheses are used to group expressions and define the precedence of operations within the expression. |