Formulas

Fields can be defined as formula fields, containing mathematical calculations for combinations of other fields on blocks in the entities.

These formulas can only be used in the Show If, Hide If, and Formula properties for report and screen blocks and fields. Other properties, such as On Mouse Click, On Lost Focus, Hyperlink To, and On Got Focus, use javascript functions or Xephr functions in their programming, and not formulas.

To set a field as a formula, set the Is Formula pop-list to true. The Formula property will be displayed for you to enter the desired formula. A formula must be entered in the Formula property.

With a formula, fields are defined in the familiar BLOCK.FIELD format, but without the $$ substitution clause.

For example, if your field C was to display the difference between field A and field B, the formula would be written as BLOCK.A-BLOCK.B.

Parentheses ( ) are used to indicate that certain parts of the calculation should be done first.

In the following formulas, node set indicates a set of values separated by commas. The values in the set can be a value defined by the user, such as a specific number, or can be a reference to a field on the block, using the familiar BLOCK.FIELD format, but without the $$ substitution clause. For example: highest('10',BLOCK.A,BLOCK.B)

Number indicates a single number, either defined by the user as a specific number, or a reference to a field on the block, using the familiar BLOCK.FIELD format, but without the $$ substitution clause. For example, abs(10) or abs(BLOCK.A).

String indicates a string of characters or numbers. Either defined by the user as a specific string, or a reference to a field on the block, using the familiar BLOCK.FIELD format, but without the $$ substitution clause.

If any of the above are followed by a ?, it indicates that the string, number, or node set definition is optional.

Server Side HTML Formulas

These formulas can be used only in the Hide If property and Show If property for blocks and fields, and the formula property for fields on screens. These cannot be used for report entities (PDF).

Functions

case

now

printf

trim


Base Positional Functions (within record set)
count

Base Math Functions	Description	Syntax & Example
ceiling	Returns the smallest integer that is not less than the number argument	number=ceiling(number) Example: ceiling(3.14) Result: 4
floor	Returns the largest integer that is not greater than the number argument	number=floor(number) Example: floor(3.14) Result: 3
round	Rounds the number argument to the nearest integer	integer=round(number) Example: round(3.14) Result: 3
sum	Returns the total value of a set of numeric values in a node-set	number=sum(nodeset) Example: sum(/cd/price)

Base String Functions	Description	Syntax & Example
concat(node set)	Returns the concatenation of all its arguments	string=concat(val1, val2, ..) Example: concat('The',' ','XML') Result: 'The XML'
string	Converts the value argument to a string	string(value) Example: string(314) Result: '314'
stringlength	Returns the number of characters in a string	number=stringlength(string) Example: stringlength('Beatles') Result: 7
substring	Returns the part of the string in the string argument

Extended Math Functions
avg(node set)
max(node set)
min(node set)

Extended Date & Time Functions	Syntax
date	No additional formatting needed.

Server Side PDF Formulas

These formulas can be used only in the Hide If property and Show If property for blocks and fields, and the formula property for fields for reports (PDFs). These cannot be used for screen entities (HTML).

PDF Formula	Description
pageNumber()	The page number for each pdf page generated.
ReportDate()	The date and time of pdf generation.
TotalPages()	The total number of pdf pages generated.

Operators	Description
+	Addition
-	Subtraction
*	Multiplication
/ or div	Division
sum(block.field)	Sum together all values in a specific field on a block with multiple rows
block.field/sum(~block.field)	Display the field value as a percentage of all values in that field on a block with multiple rows
mod	Modulus

Base Positional Functions (within record set)
count
current
first
last
position

Base Boolean Functions	Description	Syntax & Example
boolean	Converts the value argument to Boolean and returns true or false	bool=boolean(value)
false	Returns false	false() Example: number(false()) Result: 0
not	Returns true if the condition argument is false, and false if the condition argument is true	bool=not(condition) Example: not(false())
true	Returns true	true() Example: number(true()) Result: 1

Base Math Functions	Description	Syntax & Example
ceiling	Returns the smallest integer that is not less than the number argument	number=ceiling(number) Example: ceiling(3.14) Result: 4
floor	Returns the largest integer that is not greater than the number argument	number=floor(number) Example: floor(3.14) Result: 3
number	Converts the value argument to a number	number=number(value) Example: number('100') Result: 100
round	Rounds the number argument to the nearest integer	integer=round(number) Example: round(3.14) Result: 3
sum	Returns the total value of a set of numeric values in a node-set	number=sum(nodeset) Example: sum(/cd/price)

Base String Functions	Description	Syntax & Example
concat(node set)	Returns the concatenation of all its arguments	string=concat(val1, val2, ..) Example: concat('The',' ','XML') Result: 'The XML'
contains	Returns true if the second string is contained within the first string, otherwise it returns false	bool=contains(val,substr) Example: contains('XML','X') Result: true
normalize-space	Removes leading and trailing spaces from a string	string=normalize-space(string) Example: normalize-space(' The XML ') Result: 'The XML'
starts-with	Returns true if the first string starts with the second string, otherwise it returns false	bool=starts-with(string,substr) Example: starts-with('XML','X') Result: true
string	Converts the value argument to a string	string(value) Example: string(314) Result: '314'
string-length	Returns the number of characters in a string	number=string-length(string) Example: string-length('Beatles') Result: 7
substring-after	Returns the part of the string in the string argument that occurs after the substring in the substr argument	string=substring-after(string,substr) Example: substring-after('12/10','/') Result: '10'
substring-before	Returns the part of the string in the string argument that occurs before the substring in the substr argument	string=substring-before(string,substr) Example: substring-before('12/10','/') Result: '12'
translate	Takes the value argument and replaces all occurrences of string1 with string2 and returns the modified string	string=translate(value,string1,string2) Example: translate('12:30',':','!') Result: '12!30'

Extended Math Functions
abs(number)
acos(number)
asin(number)
atan(number)
atan2(number,number)
constant(string,number)
cos(number)
exp(number)
highest(node set)
log(number)
lowest(node set)
max(node set)
min(node set)
power(number,number)
random
sin(number)
sqrt(number)
tan(number)

Extended Set Functions
difference(node set, node set)
distinct(node set)
hasSameNode(node set, node set)
intersection(node set, node set)
leading(node set, node set)
trailing(node set, node set)

Extended Date & Time Functions	Syntax
date	No additional formatting needed.
dateTime	No additional formatting needed.
dayAbbreviation	No additional formatting needed.
dayInMonth	No additional formatting needed.
dayInWeek	No additional formatting needed.
dayInYear	No additional formatting needed.
dayName	No additional formatting needed.
dayOfWeekInMonth	No additional formatting needed.
hourInDay	No additional formatting needed.
leapYear	No additional formatting needed.
minuteInHour	No additional formatting needed.
monthAbbreviation	No additional formatting needed.
monthInYear	No additional formatting needed.
monthName	No additional formatting needed.
secondInMinute	No additional formatting needed.
time	No additional formatting needed.
weekInYear	No additional formatting needed.
year	No additional formatting needed.

Extended String Functions
align(string,string,string?)
concat(node set)
padding(number,string?)
split(string,string?)
tokenize(string,string?)

Xephr is a registered trademark of NDS Systems, LC.