Formulas

Infiniti provides several functions for certain tasks which are grouped under two categories – Formula Functions and Aggregate Functions.

Formula Functions

To perform more complex calculations, formulas may also incorporate various math, date and text functions. For example, the DateDiff() function can be used to calculate a person’s age based on their date of birth and today’s date.

Functions require a set of parameters to determine a result. These parameters may be literal values, references to other questions, or even other functions. Functions must include the function name followed by any parameters in brackets, or empty brackets if no parameters are required.
Parameters are separated by commas.

📘
The function engine will take any value that looks like a date (eg: a version number 9.2.16) and interpret it as a date. To prevent this conversion always prefix any date-like non date values with a string (eg: v9.2.16), then insert the answer in a label and reference from there.

Format 0 (zero) with slash (Ø)

The following formula will replace 0 with Ø

replace([q1.Amount],"0", "Ø")

Function Abs()

Returns a positive (absolute) version of the number passed in as a parameter.

Usage

Abs ( n )

Parameters	Description
n	The number to be converted to absolute.

Example:

Basic absolute value

Abs(-1)

Abs([q44])

Perform a subtraction between two numbers entered by the user in two separate Text Field questions (q1 and q2).

Abs([q1] – [q2])

The parameters passed in do the following:

[q1] References the Text Field question with ID 1. For the example, we will assume this is 10. [q2] References the Text Field question with ID 2. For the example, we will assume this is 100.

After references have been calculated our example will be resolved to ‘Abs(10 - 100)’, and the calculation engine will process the inner formula to yield ‘Abs(-90)’. Finally, the Abs() function will return 90, discarding the negative sign.

Function Chr()

This function allows characters to be referenced by their ASCII decimal code. This is useful for when characters, such as the copyright symbol ‘©’, are not readily available on the keyboard.

Usage

chr( v )

Parameters	Description
v	The ASCII decimal code of the required character or symbol.

Examples

Insert a copyright symbol ‘©’: The ASCII decimal code for the ‘©’ symbol is 169.

chr(169)

Used within an IIF function

The following iif() statement compares the answer value of another question to a fixed string.

iif(IsEqual([Q1.Currency],"GBP"), chr(163),"$")

The formula returns a ‘£’ symbol if the value is ‘GBP’ otherwise it returns a ‘$’ symbol.

ASCII comma in concat

concat([q1.surname],chr(44)," ",[q1.firstname])

Function Concat()

Returns a string created by concatenating of any number of strings or question references.

Usage

Concat( p[1], p[2], .., p[n] )

Parameters	Description
p[n]	An expression or value to be concatenated, which may include equations, question references and function calls.

Examples

Build a sentence:
This example shows the Concat() function being used to build a typical sentence.

Concat([q1.firstName], " has ", [q7.NumberChildren], "children")

or to handle the plural

Concat([q1.firstName], " has ", [q7.NumberChildren], iif(isequal([q7.NumberChildren],"1")," child"," children"),".")

Assuming ‘John’ has two children the concat() function would return: John has 2 children.

Function DateAdd()

Returns a date that is the result of adding days, months or years to a particular date.

Usage

DateAdd( t, n, d )

Parameter	Description
t	Time interval for comparison, e.g. years. Valid values: Day = 4 DayOfYear = 3 Month = 2 Quarter = 1 Weekday = 6 WeekOfYear = 5 Year = 0 Hour = 7 Minute = 8 Second = 9
n	Number of units to be added e.g. 14 days
d	Date value for additon. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, e.g. #25/12/2020#.

Examples

Calculate a due date: This example shows the DateAdd function being used to calculate a due date.

DateAdd(4,30,[q4.Date])

The parameters passed in do the following:


4	Time interval of “Days” to ensure we are adding days to the date.
30	Number of days to be added the date
[q4.date]	References question with ID 4 and the answer named “date”. This will contain the original date value – e.g. a hire date or invoice date.

Assuming the referenced question resolves to a value of 01/01/2020, the result of the DateAdd() will be 31/01/2020.

Calculating the last day of the month or year

In some documents, particularly contracts, it is necessary to calculate the last day of the month. This can be achieved by using a formula. There are different ways to calculate this, but the formula below works by finding the first day of the month, adding a month then subtracting a day.

dateadd(4,-1,dateadd(2,1,concat("01/",format([q1],"MM/yyyy"))))

(Replace [Q1] with a reference to your date)

Function DateDiff()

Returns the number of days, months or years between two date/time values.

Usage

DateDiff ( t, d1, d2 [, a] [, w] )

Parameters	Description
t	Time interval for comparison, e.g. years. Valid values: Day = 4 DayOfYear = 3 Hour = 7 Minute = 8 Month = 2 Quarter = 1 Second = 9 Weekday = 6 WeekOfYear = 5 Year = 0
d1	First date/time value for comparison. If this value is hard-coded must be prefixed and suffixed with a hash (#) character, e.g. #31/1/2006#.
d2	Second date/time value for comparison. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, e.g. #31/1/2006#.
a	Optional – 1 to enforce the return value is positive, i.e. return absolute. 0 or leave out to return positive or negative values based on whether d1 is greater than d2. Note that you must include this parameter if you wish to use any following parameter.
w	Optional – 1 to compare whole units (works for years or months). For example, in an age calculation based on date of birth and current date, if the subject has not had their birthday yet this year a simple DateDiff calculation would return a result 1 year older than their age. Setting this parameter to 1 will avoid this issue.

📘
Note

If the first date/time value is later than the second date/time, a negative difference will be returned. For this reason, the first date should be considered the start date, and the second date the end date.

The Absolute parameter may be used to ensure the difference is always returned as a positive number.

Examples

Calculate a person’s age:

This example assumes there are 2 questions: a user prompt question (q1) which collects the person’s date of birth; and a variable question containing two answers, one for today’s date named “Today” and the other to perform the age calculation.

DateDiff(0, [q1], Today(), 1, 1)

The parameters passed in do the following:


0	Time interval of “Year” to ensure we are comparing years between the dates.
[q1.DOB]	References the user prompt question with ID 1. This will contain the person’s date of birth – for the example we will assume this is 1st Jan 1970.
Today()	References the user prompt question with ID 1. This will contain the person’s date of birth – for the example we will assume this is 1st Jan 1970.
1	References the user prompt question with ID 1. This will contain the person’s date of birth – for the example we will assume this is 1st Jan 1970.
1	Sets the Whole Units option, so that it eliminates the problem of just assuming the age is the difference of years

So in effect, after references have been calculated (assuming today is 1 Jan 2020) our example will appear as follows: DateDiff(0, #1-Jan-1970#, #1-Jan-2020#, 1, 1) and evaluate to 50.

Please note that the hash (#) character did not need to be provided in the original formula, as the system automatically adds those when resolving the references.

Function Errormask()

Evaluates an expression and returns a constant number or string if the expression returns an error.

Usage

ErrorMask (e,v)

Parameters	Description
e	Any valid formula expression, including functions, equations and question references.
v	A numeric or string value to be returned if an error is generated by evaluating the first parameter.

The expression in the first parameter can be as simple as a single question reference, such as [q1] or a full calculation such as [q1] + [q2]/[q3]. In any event, the ErrorMask function simply checks that the final result of its first parameter is numeric. If it’s not numeric, it deems it to be an error.

Examples

Adding a total of calculated values:

This example takes a series of values and does calculations using those values. Each value is retrieved from a different user prompt question.

The final formula used to total each of the three values would be as follows:

ErrorMask([q1] + [q2] + [q3], 0)

If the values entered were 1, 2 and 3 respectively, the output will be 6

If the values entered were 1, 2 and A respectively, the output will be 0

Let the final formula used to total each of the three values would be as follows: [q1] + [q2] + ErrorMask([q3]/1, 0)

If the values entered were 1, 2 and 3 respectively, the output will be 6

If the values entered were 1, 2 and A respectively, the output will be 3

The parameters passed in ([q1] [q2] [q3]) references answers containing the variable formulas that in turn refer to each of the user prompt questions. 0 Replace a non-numeric value returned from the first parameter with a 0, so that it doesn’t impact the addition operations.
Notes

A string value can also be returned if an expression returns an error. Eg: ErrorMask([q1]+[q2]+[q3], “Non-numeric values can’t be added”) 1.The first parameter in an ErrorMask function should always be an expression. Eg: ErrorMask([q3], 0) – Wrong ErrorMask([q3]/1, 0) – Correct

Function Format()

Returns a string created by formatting a value in the specified style.

Usage

Format( e, f )

Parameter	Description
e	An expression or value to be formatted, which may include equations, question references and function calls
f	The format string to apply to the expression.

Examples

Format the current date:

Format(today(),”long date”) -> “Friday, 1 January 2000”
Format(today(),”dd MMM yyyy”) -> 01 Jan 2000
*For further details of date formatting, see http://ixsupport.intelledox.com/kb/a357/custom-date-time-format-strings.aspx

Format a number to always show digits/decimal places:

Format([q1],”{0:#.0}”) -> will always show one decimal place (e.g. 5.0 for the value 5) and and only show units if they exist (e.g. “.1” for the value 0.1)
For further details of number formatting, see http://ixsupport.intelledox.com/kb/a359/custom-numeric-format-strings.aspx

Format a string to “title case” (first letter of each word capitalized):

*Format([q1],”+-+-“)

Function IIf()

Evaluates a true / false expression, and returns a parameter value if the expression is true and another if the expression is false.

Usage:

IIf( e, t, f )

Parameter	Description
e	An expression or value to be evaluated to true or false.
t	Defines what to return if the evaluation of expression e is true.
f	Defines what to return if the evaluation of expression e is false. Example:

Example:
IIf(IsEqual(0, 1 - 1), "Values are equal!", "Values are unequal!")

Function IsTrue()

The isTrue() function evaluates determines if an expression is true or false, Returns 1 if true otherwise 0 if false.

This function can be used to convert string based True/False results into a numeric representation.

By default, this function will interpret the following as True:

1
T
True
Y
Yes

This function is not case sensitive, so the value ‘t’ and ‘T’ both evaluate to True. In addition to the built-in interpretation of True, this function can optionally be passed a second parameter to define an alternative value for True. Used in this way this function is similar to the IsEqual function.

Usage

IsTrue( e, [c] )

Parameter	Description
e	Expression to be evaluated as True.
c	(Optional) value to be used as a definition of True. Note that if a second parameter is provided, the built-in definitions of True no longer apply and only the value passed in will be tested.

Examples:

IsTrue([q1])
IsTrue([q1], "2")

Function Left(), Right() and Substring()

Left(), Right(), and Substring() return a portion of a larger text string, used primarily for manipulating data that is received in an unusable format.

Usage

left(o, v) right(o, v) substring(o,s,v)

Parameter	Description
o	Original value, or question reference.
s	A numeric value representing the character position where the sub-string begins. A value of 1 indicates the first value in the text.
v	A numeric value representing the character position where the sub-string ends.

Example:

right([q1.ClientID], 6) substring([q1.ClientID],4,6)

Function Length()

Length() returns the number of characters in a value or question reference.

Usage

length(o)

Parameter	Description
o	Original value, or question reference.

Example

length([q1.Postcode])

Function Now()

The now() function returns the current date and time, useful for time stamping. When referenced directly it returns the web server's local time, for example 17/02/2016 4:41:21 PM.

Usage
now()

Parameters
(None)

Examples
Calculate the number of hours between a question value and now - DateDiff(7,[q1.DateTime],Now(),1,1)

Convert the server time to -UTC LOCALTOUTC(Now())

Function ProperCase()

Using specified characters, words and terms the propercase() function will capitalize text to suit the situation. Although it is possible to capitalize the first or each letter within a string of text the propercase() function caters for more complex examples such as MCDONALD and SMITH-JONES.

Usage

Parameters	Description
o	Original value, or question reference.
i	Words to ignore or exclude from capitalisation such as and, the, of, etc.
t	Terms after which to capitalise the next letter such as ‘ Mc’, ‘-’, etc.

Examples

The function below would render the text below as follows =propercase([q1.Surname], "and|the|of", "Mc|O'|Mc'|Mac")


MCDONALD	McDonald
MCDONALD AND O'DONNELL	McDonald and O'Donnell
MCDONALD-O'DONNELL	McDonald-O'Donnell
MC'CARTHY	Mc'Carthy

Function Propercase() - Excluding Abbreviations and Acronyms from Propercase

The propercase() function will capitalize text to suit the situation. It will capitalize the first letter of each word within a string of text and exclude some words from capitalization like: "and, the, of".

But what about acronyms or abbreviations that you do not want in propercase or excluded from capitalization like: "PO Box, RD, ROI or HR"? Unfortunately acronyms and abbreviations do not work with the proper case function as they simply don’t follow the same standard.

If you wish to ignore some abbreviations or acronyms when using the propercase document formatting or functions, you need to use a formula that nests the Propercase function inside the required Replace function(s).

This is an example of the formula that you should use, notice that the propercase is nested in as many replace functions as required:

Replace(Replace(propercase([*Insert your Data Reference HERE*],"and|the|of","Mc|O'|Mc'Mac"), "Po Box","PO Box"),"Rd 1","RD 1")

Breaking the formula down

We have the inner propercase function, which returns our string:

propercase([*Insert your Data Reference HERE*],"and|the|of","Mc|O'|Mc'Mac")

Then we have our outer Replace function, which replace our Abbreviations that have been turned into propercase:

Replace(propercase([*Insert your Data Reference HERE*],"and|the|of","Mc|O'|Mc'Mac"), "Po Box","PO Box")

Anything outside this, is optional and allows you to replace additional abbreviations, you can add as many outer replace functions as you like, just stick to the same format:

Replace(Replace(propercase([*Insert your Data Reference HERE*],"and|the|of","Mc|O'|Mc'Mac"), "Po Box","PO Box"),"Rd 1","RD 1")

Example in Web form:

Example of setup in question set:

Function RangeIndex()

Returns the 0-based index of the range the expression in the first parameter falls within, with the ranges indicated by each subsequent parameter.

Usage:

RangeIndex ( e, r1 [, r2] [, …] )

Parameters:

Parameters	Description
e	Any valid expression that resolves to a number. This may include standard formulas, function calls and question references, e.g. [q1] + [q2].
r1	The starting number of the first range. If the expression in e is found to be equal or greater than this value, but less than the value in the next range parameter (if provided), then the RangeIndex function will return 0.
r2...	Optional – The starting number of the second or subsequent ranges. If the expression in e is found to be equal or greater than this value, but less than the value in the next range parameter (if provided), then the RangeIndex function will return the index of this parameter.

The index returned by the function is entirely dependent on which of the range parameters the expression falls within. If it’s the first range parameter (i.e. the second parameter in the function), then 0 is returned; subsequent range parameters will return 1, 2, 3 etc.

This is a useful function for scoring algorithms which calculate a score, and determine a final result based on ranges of possible scores. It can be useful for other scenarios as well, such as calculating a person’s age and determining which age range they fall into to affect the logical flow of the document being generated.

Examples:

Determine a range based on a scored questionnaire:

This scenario is based on a questionnaire made up of 5 multiple choice questions, each with 3 possible responses. Each of the possible answers is scored with a value of 0, 1 or 2 which is provided via the answer’s Tag property.

A variable question will be used to perform the calculation, returning a result that can be anything from 0 to 10. There will be 5 ranges of scores: 0 – 3 = Fail; 4 – 5 = Pass; 6 – 7 = Credit; 8 – 9 = Distinction; 10 = High Distinction.

The formula to calculate the range is:

RangeIndex([q1] + [q2] + [q3] + [q4] + [q5], 0, 4, 6, 8, 10)

Parameters	Description
[q1] + [q2] …	This expression adds up the score for each of the multiple choice questions.
0	If the total of the first parameter is 0 to 3, RangeIndex returns 0.
4	If the total of the first parameter is 4 or 5, RangeIndex returns 1.
6	If the total of the first parameter is 6 or 7, RangeIndex returns 2.
8	If the total of the first parameter is 8 or 9, RangeIndex returns 3.
10	If the total of the first parameter is 10 or higher, RangeIndex returns 4.

Once the calculation is set up in a variable question, it can be used as a parent to drive other questions, such as a multiple choice question which alters the output of the final document depending on the calculated index.

Determine an age range based on a person’s age calculation:

This scenario is based on using an Address Prompt question with a custom Date of Birth field, which is used along with a current date variable to determine the age of the person. The age is then processed by the RangeIndex function so that information can be displayed in the document relevant to that person’s age range.

To simplify the formulas, the age calculation will be performed in a separate answer to the RangeIndex, and then referenced. For simplicity it is assumed that all individual calculations are separate answers that belong to the same Variable question.

The age calculation is as follows:

DateDiff(0, [q1.DOB], Today(), 1, 1)

The age range calculation is as follows:

RangeIndex([Age], 0, 18, 25, 40, 60)

A multiple choice question can then be driven by the age range calculation by using it as its Dynamic Parent. The possible age ranges derived are Child (0 – 17), Young Adult (18 – 24), Adult (25 – 39), Middle-Aged (40 – 59) and Senior Citizen (60+).

Advanced RangeIndex Usage

Most of the time the RangeIndex function will be used with hard-coded range values. However, there might be a reason why the ranges can vary – for example, if a different scoring pattern is required depending on the type of person using the questionnaire.

To achieve dynamic range values you can simply place a question reference instead of a hard-coded range value in the parameters of the RangeIndex function.

For example, consider the following RangeIndex formula:

RangeIndex([Score], [q2.Range1], [q2.Range2], [q2.Range3], [q2.Range4], [q2.Range5])

In this example, the question identified as q2 could be a data field question, set up to be dynamically answered based on an Address Prompt question. That data field question should be based on a data table which exposes fields called ‘Range1’, ‘Range2’, ‘Range3’, ‘Range4’ and ‘Range5’. Each record in that table would be related to which Address is selected, and would return the appropriate range values.

Further to that, the range values could actually be calculated by basing the data source on a view rather than a table, or by instead pointing them to other formula-based Variables. There are a wide range of possible uses for this kind of flexibility.

Keep in mind, though, that you are always limited to the number of ranges possible as defined by how many parameters are set up in the call to the RangeIndex function. To work around this limitation, consider creating enough possible range parameters to suit any need and ensure that in any given case unused range values are set to a number less than its preceding range value.

For example, if the RangeIndex formula above is used to point to a data table, one possible row of data might return the range values 10, 20, 30, 0, 0. This has the effect of having only 3 possible ranges instead of 5. You can even use this to merge two or more inner ranges, for example 10, 20, 0, 30, 40

Function Replace()

Returns a value where a specified character or term has been replaced with another value.

Usage

Replace( o, f, r )

Parameters	Description
o	Original Value
f	Character or term to find
r	Value to replace the found text with

Examples

The examples below manipulate a value returned from a data source so they appear clearer in the document.

Replace([q1.staffID], "STAFFNUM", "Staff Number - ")

Replace([q1.PhoneNumber]," ", "")

The replace function is useful for removing unwanted characters from strings, commonly used to cleanse data retrieved from other systems.

RangeIndex() formula in auto logic question rules, including content based on age##

Sometimes it is necessary to use a formula to calculate a value that can be used to determine if a particular piece of content should be included in the document.

In the formula below the RangeIndex() formula is used to place people into three different categories based on their age, the [Q4] reference is to a date of birth, possibly from a text field or data source.

RangeIndex(DateDiff(0,[q4], Today(), 1, 1), 0, 18, 55)

The formula will return 0 for a child (0-18 years), 1 for an adult (18-55 years) and 2 for retired (55+ years). This formula when coupled with an auto logic question is a straightforward way to include content based on age. In the screenshot below the RangeIndex() formula answer rules determine which content is included.

Function Round()

Returns the value passed into the first parameter, rounded to the number of decimal places defined by the second parameter, or to a whole number if the second parameter is not provided.

Usage:

Round ( e [, n] )

Parameters	Description
e	An expression or value to be rounded, which may include equations, question references and function calls.
n	Optional – The number of decimal places to which the number will be rounded. Left out, 0 is the default and the value will be rounded to a whole number.

Examples
Round after division:

This simple example shows the Round function being used to ensure a division operation returns only precision of only one decimal place.

Round([q1.Value] / 2.5, 1)

Assuming the referenced question resolves to a value of 10.88, the result of the division is 4.352 and so 4.4 is the final rounded result.

Function StartsWith() & Ends with()

Determines if a value starts or ends with a particular value or reference. The functions resolve to 1 or 0, i.e. true or false.

Usage

StartsWith( o, f)

EndsWith( o, f)

Parameters	Description
o	Original value, or question reference.
f	Value to find within the original value or reference.

Examples

The example below returns true if the ‘Region’ data field starts with ‘22’.

StartsWith([Q1.Region], "22")

Function Today()

Returns the current date.

Usage

Today()

Examples

Calculate a person’s age

DateDiff(0, [q1.DOB], Today(), 1, 1)

Nested iif() formulas

Often, it is necessary to nest iif() functions to cater to scenarios where there are several possible input values.

For example, in the formula below, the [q1] reference has three possible values namely 1, 2,and 3 and resolves to the phrase Plane, Train, or Automobile.

iif(isequal([q1],1),"Plane",iif(isequal([q1],2),"Train", "Automobile"))

Aggregate Functions

A collection of aggregate functions are available to perform calculations on questions within a repeating section or page, or a multiple row datasource. For example, calculate the total price of individual line items or determine the average purchase order value for a particular client.

Aggregate functions require a reference to a question within a repeating page or section, which may be of any question type, or a multiple row data-source question. Aggregate functions examine all the values retrieved to determine a result.

Function Average()

Returns the average of a series of numbers.

Usage

Average( r )

Parameters	Description
r	Reference to a question in a repeating page, repeating section or multiple row data source.

Examples

average([q4.income])

Function Contains()

Contains() determines if at least one of the values retrieved from a repeating section, page or data source matches a specified value. Note that the value must be an exact match, although it is not case sensitive.

Returns 1 if any values match or 0 if no values match.

Usage

Contains( p1, p2[, p3…])

Parameters	Description
p1	The value to search for.
p2	Reference to a question within a repeating page, repeating section or multiple row data source.
[p3 …]	Optional additional references to extend the data set being matched.

Examples

The examples below return true or false values based on whether the value ‘inactive’ is found in the ‘Account’ field from a multi row data source question.

contains("inactive", [Q5.account])

Function Count()

Returns the total number of rows in a repeating page or section, or in a multi row data source.

Usage

Count( r )

Parameters	Description
r	Reference to a question in a repeating page, repeating section or multiple row data source.

Examples

This example shows how to find the number of staff.

count([q6.employeeID])

Function CountIf()

CountIf() returns the number of values retrieved from a repeating section, page or data source that match a specified value.

Usage

CountIf( f, s )

Parameters	Description
f	Reference to a question within a repeating page, repeating section or multiple row data source.
s	The value to search for.

Examples

The example below returns the number of times a particular value appears within collection of records.

CountIf([Q4.Active], "False")

Using the CountIf function##

Purpose

The CountIf function will return the number of items that match the parameters set out in the formula. This particular function is commonly used with data source questions where there is a need for calculations once a specific threshold has been reached.

Example 1

CountIf("a|b|c", "b")

The formula would return a result of one.

Example 2

CountIf([Q1], "High Risk")

This will return the amount of High Risk’s that are present in [Q1].

Function CountNumber()

Returns the number of numerical values in a set of mixed data types. Any non-numerical values are ignored in the count.

Usage

CountNumber( r )

Parameters:

Parameters	Description
r	Reference to a question in a repeating page, repeating section or multiple row data source.

Examples

The following example counts any employees in a set of data who have been assigned an agreement number. Some employees do not have an assigned number, so any blank or non-numeric values are not counted.

CountNumber([q6.EmployeeAgreementNumber])

Function First() and Last()

Returns the first or last value in a set.

Usage

First( r )

Last( r )

Parameters	Description
r	This is a reference to a question in a repeating page, repeating section or multiple row data source

Examples

The example below returns the most recent item bought from a store.

Last([Q1.BoughtItems])

Function GreaterThan, LessThan, EqualTo

This group of functions compare their parameters. A true result of 1 or a false result of 0 is returned. These functions are often used in conjunction with the IIf function to return a string or value other than 0 or 1.

Usage

IsEqual( p[1], p[2], .., p[n] )

IsNotEqual( p[1], p[2])

IsGreaterThan( p[1], p[2])

IsLessThanOrEqualTo ( p[1], p[2])

Parameters	Description
p[n]	Each parameter is an expression that is evaluated and then compared to the other parameter to determine a true or false value.

Examples

IIf(IsEqual(0, 1 - 1), "Values are equal!", "Values are unequal!")

Function Join()

Joins an unknown number of values together, and separates each with one or more specified characters. This is useful for creating lists from a data set, commonly these would be comma separated.

For example, consider a dataset containing a list of countries. The Join() function can convert that data set into a comma-separated list, or a list divided into separate paragraphs for creating a bullet list in the document.

Usage

Join( s, r )

Parameters	Description
s	The Separator is used to separate the items in the second parameter.
r	This is a reference to a question in a repeating page, repeating section or multiple row data source

Examples

The example below creates a comma-separated list of countries.

Join(chr(44),[Q1.Country])

Function JoinAnd()

Joins an unknown number of values together, with more complex separation rules to form a proper sentence from a data set. This function is very similar to Join(), except that it allows you to specify a different separator to use before the last item joined.

For example, you can create a comma separated list with the final item separated with the word ‘and’, such as ‘red, green, yellow and blue’.

Usage

JoinAnd( s, l, r )

Parameters	Descriptions
s	The separator is used to separate all but the last item.
l	The separator used for joining the last item.
r	This is a reference to a question in a repeating page, repeating section or multiple row data source

Examples

The example below returns a properly formed sentence:

JoinAnd(", "," and ",[Q1.Colours])

Function Max() and Min()

Functions returns the highest and lowest number in a data set.

Usage

Max( r )

Min( r )

Parameters	Description
r	eference to a question in a repeating page, repeating section or multiple row data source.

Examples

max([q4.income])

Function NotContains()

NotContains() determines whether none of the values retrieved from a repeating section, page or data source matches a specified value. Note that the value must be an exact match, although it is not case sensitive. This is the opposite of the Contains() function.

Returns 1 if no values match or 0 if any values match.

Usage

NotContains( p1, p2[, p3…])

Parameters	Contains
p1	The value to search for.
p2	Reference to a question within a repeating page, repeating section or multiple row data source.
[p3 …]	Optional additional references to extend the data set being matched.

Examples

The example below returns true or false values based on whether the value ‘inactive’ is found in the ‘Account’ field from a multi row data source question.

notcontains("inactive", [Q5.account])

Function StringContains()

Contains()

Contains(paramtr1,paramtr2,paramtr3,…….,paramtrN)

The function checks whether or not the first parameter paramtr1 is equal to one of the other parameters paramtr2,paramtr3,…….,paramtrN and returns a value.

Examples

1: Contains("Tom", "Tom", "Dick", "Harry")--------------------------> Returns: 1
2: Contains("Tom", "Jenny", "Dick", "Harry")------------------------> Returns: 0

NotContains()

NotContains(paramtr1,paramtr2,paramtr3,…….,paramtrN)

The function checks whether or not the first parameter paramtr1 is not equal to one of the other parameters paramtr2,paramtr3,…….,paramtrN and returns a value.

Examples

1: NotContains("Tom", "Tom", "Dick", "Harry")--------------------------> Returns: 0
2: NotContains("Tom", "Jenny", "Dick", "Harry")------------------------> Returns: 1

StringContains()

StringContains(paramtr1,paramtr2)

The function checks whether or not the second parameter paramtr2 is present in string paramtr1.

Examples:

1: StringContains("abcnam", "ab")------------------------> Returns: 1
2: StringContains("abcnam", "do")------------------------> Returns: 0

Function Sum()

The Sum() function returns the total of all numerical values in a data set.

Usage

Sum( r )

Parameters	Description
r	Reference to a question in a repeating page, repeating section or multiple row data source.

Examples

sum([q4.income])

Special Format Strings

The following are format strings available for use in the “Document Format” field of Answers. The first four formats (“+”, “-“, “+-“ and “+-+-“) are additionally available for use within the Format() function. All other format strings listed here are available only within the Document Format field.

Format String	Description
+	Displays all text in upper case.
-	Displays all text in lower case.
+-	Displays the first letter of text in upper case, with the rest in lower case.
+-+-	Capitalize each word, title case.
one	Displays the number as text rather than a figure, for example, 100 would display as ‘One Hundred’.
+one	Displays a number as uppercase text.
one	Displays a number as lower case text.
+-one	Displays a numer in text that starts with a capital letter with the rest in lower case.
lpad	Pads the left of the value with a character to the specified length, for example, LPAD '0'5 would return 00001 for a value of 1.
rpad	Pads the right of the value with a character to the specified length, for example, RPAD 'A' 10 would return HelloAAAAA for a value of Hello.
Left n	Provides the first n amount of letters in a text string.
Right n	Provides the last n amount of letters in a text string.
-Left n	Provides the first n amount of letters in a text string in lower case.
-Right n	Provides the last n amount of letters in a text string in lower case.
+Left n	Provides the first n amount of letters in a text string in upper case.
+Right n	Provides the last n amount of letters in a text string in upper case.
Trim	Removes any leading or trailing spaces.
OADate	Format string for converting serial dates (OLE Automation or OA Dates) into actual dates. For example: e.g.OADate {‘f’} Where ‘f’ is an optional inner format string for altering the format of the date produced by the OA date conversion. e.g.2 OADate ‘dd MMM yyyy’ The OA date format is used when calculating an end date based on a start date and a number of days to add. To add the number of days from [q2] user prompt to a date entered in user prompt [q1] use the below formula with an [OADate] format. errormask(DateDiff(4, #30-dec-1899, [q1]), 0) + errormask([q2], 0)

Functions Join() and Joinand()

The Join and Joinand functions are useful when multiple values from a data source or repeating section need to be combined into a properly formed sentence. For example:

Australia
New Zealand
United States of America
England

Join

Join(chr(44),[Q1.Country])
Join(concat(chr(44)," "),[Q1.Country])

Parameters	Description
p1	separator character or term
p2	Reference

Australia,New Zealand,United States of America,England

JoinAnd
The first parameter is used as a separator for the items in third parameter. The second parameter (" and ") is used to separate the last two items.

JoinAnd(p1,p2,p3)
JoinAnd(chr(44)," and ",[Q1.Country])
JoinAnd(concat(chr(44)," ")," and ",[Q1.Country])

Parameter	Description
p1	Separator
p2	Last item separator
p3	Reference

Australia, New Zealand, United States of America and England

Formula Functions

📘

Format 0 (zero) with slash (Ø)

Function Abs()

Function Chr()

Function Concat()

Function DateAdd()

Calculating the last day of the month or year

Function DateDiff()

📘Note

Function Errormask()

Function Format()

Function IIf()

Function IsTrue()

Function Left(), Right() and Substring()

Function Length()

Function Now()

Function ProperCase()

Function Propercase() - Excluding Abbreviations and Acronyms from Propercase

Function RangeIndex()

Function Replace()

RangeIndex() formula in auto logic question rules, including content based on age##

Function Round()

Function StartsWith() & Ends with()

Function Today()

Nested iif() formulas

Aggregate Functions

Function Average()

Function Contains()

Function Count()

Function CountIf()

Using the CountIf function##

Function CountNumber()

Function First() and Last()

Function GreaterThan, LessThan, EqualTo

Function Join()

Function JoinAnd()

Function Max() and Min()

Function NotContains()

Function StringContains()

Function Sum()

Special Format Strings

Functions Join() and Joinand()

📘
Note