Formulas

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

👍

Best Practice

Parameters are separated by commas and for best practice spaces should be inserted after commas to avoid issues for users using international cultures.

👍

Best Practice

Formulas in Variable questions can reference other SmartIQ questions. Whenever possible, questions should only refer to other questions which come earlier in the form structure. References to later questions (often called a "forward reference") may work but can also behave unreliably; for this reason forward references are not recommended.

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

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, the 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 AddDay(), AddMonth() and AddYear()

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

Usage
AddDay( d, n )
AddMonth( d, n )
AddYear( d, n )

Examples
Calculate a due date: This example shows the AddYear function being used to calculate a due date. AddYear([q1.Date], 2)

The parameters passed in do the following:

Assuming the referenced question resolves to a value of 01/01/2023, the result of AddYear() will be 01/01/2025.

Function Age()

Returns the current age or age of someone on a particular date.

Usage
Age( d )
AgeOn( d, td )

Examples
This example shows the AgeOn function being used to show the age on a certain date.
AgeOn([q1.CurrentDate], [q2.TargetDate])

The parameters passed in do the following:

Assuming [q1.CurrentDate] resolves to a value of 01/01/2000 and [q2.TargetDate] resolves to 01/01/2025, the result of AgeOn() will be 25.

Function And()

Compares multiple values and returns true if all values are true or false if at least one input is false.

Usage
And( e , e [, e…])

Examples
And(1 , 1 , 1) will return true as all the values are true.
And(1 , 0 , 1) will return false as one of the values is false.
And(“test”,”test”) will return false as the function only recognises boolean values.
And(IsEqual(q1_lastname , q2_lastname) , IsEqual(q3_firstname , q4_firstname)) will return true if both pairs of values are equal.

Function Base64EncodeString()

Encodes the supplied string value as Base64.

Usage
Base64EncodeString(s)

Examples
Base64EncodeString("Sample text to encode via base64")

This formula will return "U2FtcGxlIHRleHQgdG8gZW5jb2RlIHZpYSBiYXNlNjQ="

Function Base64DecodeString()

Decodes the supplied Base64 string value into its original value.

Usage
Base64DecodeString(s)

Examples
Base64DecodeString("U2FtcGxlIHRleHQgdG8gZGVjb2RlIHZpYSBiYXNlNjQ=")

This formula will return "Sample text to decode via base64".

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
chr(169)
Insert a copyright symbol ‘©’: The ASCII decimal code for the ‘©’ symbol is 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 example
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

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)

You can also use Function EOM.

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.

Alternatively, use the Formula Age to calculate the age.

Function DayOfWeek()

Returns the enum value for the Day of the Week corresponding to the date.

Usage
DayOfWeek( date )

Function EOM()

In some documents, particularly contracts, it is necessary to calculate the last day of the month. Function EOM() returns the last day of the month of a particular date.

Usage
EOM( d )

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 FileDownloadLink()

Returns a link (HTML Anchor Tag) for downloading binary files retrieved from a data connection.

Usage

FileDownloadLink(r, f, t)

Example
FileDownloadLink([q1.resumeBinary], “Resume.PDF”, “Download Resume”)

The parameters passed in do the following:

Function FileImageDisplay()

Returns an image (HTML Image Tag) that will render a binary image file retrieved from a data connection for display within an in progress form or dashboard.

Usage

FileImageDisplay(r, f, d [, h] [, w])

Example
FileDownloadLink([q1.profileBinary], “profile.png”, Concat([q1.FullName], “Profile Picture”))

The parameters passed in do the following:

Function FileUrl()

Returns a link that can be used to render a custom HTML tag within a form.

📘

Note

The file is only available during the user’s current session.

Usage

FileUrl(r, f)

Example
FileUrlLink([q1.profileBinary], “profile.png”)

The parameters passed in do the following:

The function returns a URL only. The URL is usually rendered in a label via editing the question's source:
<a href="[q1.12]">blah</a>

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 more information on date formatting, see Custom date/time format strings.

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 more information on number formatting, see Custom numeric format strings and Named Numeric Formats.

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

• Format([q1],”+-+-“)
• The Format function supports the following capitalization formats:
  • “+” (All capitalized)
  • “-“ (All lowercase)
  • “+-“ (First letter capitalized)
  • “+-+-“ (First letter of each word capitalized)

For more details about Format Strings, refer to Format Strings.

Function FromEpochMs(epochMsValue)

The From Epoch MS formula converts an epoch value in milliseconds to a universal date.

Usage

FromEpochMs(epochMsValue)

Function HtmlEncode()

Converts a specified string and applies HTML encoding so it is safe to use in HTML.

Usage

HtmlEncode(s)

Example
HtmlEncode(<b>bold</b>)

The browser will display this as bold instead of bold.

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 IndexOf and LastIndexOf

IndexOf and LastIndexOf functions search for a pattern in a string and return its zero based position. The difference is IndexOf returns the first zero based position while LastIndexOf returns the last zero based position. If the pattern is not found, -1 will be returned.

The pattern can be single character or long string.

IndexOf(s, p)
LastIndexOf(s, p)

Index Of example:
IndexOf("abcabc", "b")

LastIndexOf example:
LastIndexOf("abcabc", "b")

returns 4

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 ItemAt()

takes an index and a pipe-separated array, and returns the item at the specified index.

**Usage
ItemAt(index,array)

The index is one-based (i.e. the first item is index 1). For example, ItemAt(2, "Yellow|Blue|Red") will return "Blue".

Function FindIndex()

Finds the first occurence of an item in a pipe-separated array, and returns its one based index position. Returns blank if the item is not found.

Usage
FindIndex(item,array)

Partial matches are not considered, so FindIndex("ab", "abc|ab") will return 2, not 1.

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 LocalToUtc()

Converts the specified date from local to UTC

Usage
LocalToUtc(d)

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/2020 4:41:21 PM.

Usage
now()

Parameters
(None)

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

Convert the specified time from Utc to Local
Formula: UtcToLocal(Now())

Function Or()

Compares multiple values. Returns true if at least one of them is true.

Usage

Or(e , e [, e…])

Examples
Or(1 , 0 , 1) will return true as at least one of the values is true.
Or(“test”,”test”) will return false as the function only recognises boolean values.
Or(IsEqual(q1_lastname , q2_lastname) , IsEqual(q3_firstname , q4_firstname)) will return true if either pair of values is equal.

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 RegexMatch()

Compares a pattern against a target and returns whether the target matches the pattern

Usage
RegexMatch(pattern,target,[caseInsensitive])

for example:
RegexMatch("[a]", "a") => true
RegexMatch("[a]", "b") => false

An optional parameter controls case-insensitivity:

RegexMatch("[a]", "A", 0) => false
RegexMatch("[a]", "A", 1) => true

An error will be returned by the function if the supplied pattern can't be parsed as a regular expression.

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(), Ceiling() and Floor()

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.

• Round – rounds to the closest given number of decimal places. For example, 0.1 becomes 0 while 0.9 becomes 1.
• Ceiling – rounds UP to the nearest given number of decimals. For example, 0.1 becomes 1 while 0.9 becomes 1.
• Floor – rounds DOWN to the nearest given number of decimals. For example, 0.1 becomes 0 while 0.9 becomes 0.

Usage

Round ( e , n )
Ceiling ( e , n )
Floor ( e , n )

Examples
Round after division:

This table shows the Round, Ceiling and Floor functions being used and the different results.

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 ToEpochMs(date)

The ToEpochMS formula converts a given date to an epoch format in milliseconds. If the date is a local date, < converts it to the user's local time zone. If not, it will convert the date according to the user’s system configuration.

Usage
ToEpochMs(date)

Function Today()

Returns the current date.

Usage

Today()

Examples

Calculate a person’s age

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

Function UrlEncode()

Converts a specified string and applies HTML encoding so it is safe to use in a URL.

Usage

UrlEncode(s)

UrlEncode(https://www.smartcommunications.com/)

The output for this example will be “https%3A%2F%2Fwww.smartcommunications.com%2F”.

Function UtcToLocal()

Converts the specified date from UTC to local

Usage
UtcToLocal (d)

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###

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.

Australia
New Zealand
United States of America
England

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

Will return
Australia,New Zealand,United States of America,England

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

Will return
Australia, New Zealand, United States of America and England

Function JsonEncode()

Encodes a string so it is safe to use in JSON. Especially useful to encode values provided by users that are being used by subsequent REST calls.

Usage
JsonEncode(value)

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

Function Switch()

Switch takes the first parameter and compares it against a list of matches, returning a corresponding value.

Switch(compareValue, match1, return1, match2, return2 [, default])

If a match is not found the optional default value is returned. If not default is specified a blank string ("") is returned.

Examples:
Switch(2, 1,"Sunday",2,"Monday",3,"Tuesday","No match")

returns the value "Monday".
Switch(4, 1,"Sunday",2,"Monday",3,"Tuesday","No match")

returns "No match".

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.

Mathematical Operators