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 )

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 )

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] )

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 )

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:

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] )

📘

Note

1. 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.

2. 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:

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)

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 )

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 )

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] )

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)

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)

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

Examples

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

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:

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)

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 )

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] )

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)

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 )

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…])

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 )

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 )

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:

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 )

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])

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 )

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 )

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 )

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…])

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 )

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.

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])

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])

Australia, New Zealand, United States of America and England