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
Parameter | 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, 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 )
Parameter | Description |
---|---|
d | Date value for addition. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, e.g. #25/12/2023#. |
n | Number of units to be added, for example, 14 days |
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:
Sample Parameter | Description |
---|---|
[q1.date] | References question with ID 1 and the answer named “date”. This will contain the original date value, for example, a hire date or invoice date. |
2 | Number of years to be added to the date |
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 )
Parameter | Description |
---|---|
d | Date value |
td | Target Date value |
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:
Sample Parameter | Description |
---|---|
[q1.CurrentDate] | References question with ID 1 and the answer named “Current Date”. This will contain the current date value, for example, start of school date. |
[q2.TargetDate] | References question with ID 2 and the answer named “Target Date”. This will contain the target date value, for example, graduation date. |
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…])
Parameter | Description |
---|---|
e | An expression or value, including functions, equations and question references to be evaluated to true or false. |
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)
Parameter | Description |
---|---|
s | The string value to be encoded into a Base64 representation. |
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)
Parameter | Description |
---|---|
s | The Base64 representation of the string to be decoded to its original value. |
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 )
Parameters | Description |
---|---|
v | The ASCII decimal code of the required character or symbol. |
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] )
Parameter | 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 |
n | Number of units to be added e.g. 14 days |
d | Date value for addition. 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
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] )
Parameters | Description |
---|---|
t | Time interval for comparison, e.g. years. |
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/2020#. |
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.
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.
Enum | Value |
---|---|
1 | Monday |
2 | Tuesday |
3 | Wednesday |
4 | Thursday |
5 | Friday |
6 | Saturday |
7 | Sunday |
Usage
DayOfWeek( date )
Parameter | Description |
---|---|
d | Date value |
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 )
Parameter | Description |
---|---|
d | Date value |
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) – WrongErrorMask([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)
Parameters | Description |
---|---|
r | Reference to a Data Connection Answer (Field) pointing to the binary data |
t | Filename/extension of the binary data |
f | Link Text |
Example
FileDownloadLink([q1.resumeBinary], “Resume.PDF”, “Download Resume”)
The parameters passed in do the following:
Parameters | Description |
---|---|
[q1.resumeBinary] | Question reference to binary data |
“Resume.PDF” | Filename/extension of the target binary data. Include the extension so that the user’s device will know which application to open the file with. |
“Download Resume” | Appropriate hyperlink text to instruct the user to click if they want to download the file. |
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])
Parameters | Description |
---|---|
r | Reference to a Data Connection Answer (Field) pointing to the binary image data. |
f | Filename/extension of the binary data (.jpg, .jpeg, .png, etc.) files. |
d | Description of the image. Appears as hover text and is useful for screen-reading tools. |
h | (Optional) The maximum height of the image in pixels. |
w | (Optional) The maximum width of the image in pixels. |
Example
FileDownloadLink([q1.profileBinary], “profile.png”, Concat([q1.FullName], “Profile Picture”))
The parameters passed in do the following:
Parameters | Description |
---|---|
[q1.profileBinary] | Question reference to binary image data |
“profile.png” | Filename/extension of the image binary. |
Concat([q1.FullName], “Profile Picture”) | Combines the user’s full name together with “profile Picture” creating a description of the image. |
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)
Parameters | Description |
---|---|
r | Reference to a Data Connection Answer (Field) pointing to the binary data. |
f | Filename/extension of the binary data |
Example
FileUrlLink([q1.profileBinary], “profile.png”)
The parameters passed in do the following:
Parameters | Description |
---|---|
[q1.profileBinary] | Question reference to binary data |
“profile.png” | Filename/extension of the target binary data. Ensure the extension is present so that the user’s device know which application to open the file with. |
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 )
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 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)
Parameter | Description |
---|---|
epochMsValue | Epoch value in milliseconds |
Function HtmlEncode()
Converts a specified string and applies HTML encoding so it is safe to use in HTML.
Usage
HtmlEncode(s)
Parameter | Description |
---|---|
s | The string to encode |
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 )
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:
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)
Parameters | Description |
---|---|
s | The string to search into |
p | The pattern to find in the string |
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] )
Parameters | 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 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)
Parameters | 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 length of the substring to be returned. |
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 LocalToUtc()
Converts the specified date from local to UTC
Usage
LocalToUtc(d)
Parameter | Description |
---|---|
d | Date value for conversion. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, for example #25/12/2020#. |
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…])
Parameter | Description |
---|---|
e | An expression or value, including functions, equations and question references to be evaluated to true or false. |
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
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 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:
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(), 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 )
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 table shows the Round, Ceiling and Floor functions being used and the different results.
Sample Parameter | Result |
---|---|
| Where q1.value = 10.88 , the result of the division is 4.352. |
| Where q1.value = 10.88 , the result of the division is 4.352. |
| Where q1.value = 10.88 , the result of the division is 4.352. |
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 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)
Parameters | Description |
---|---|
epochMsValue | Epoch value in milliseconds |
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)
Parameter | Description |
---|---|
s | The string to encode |
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)
Parameter | Description |
---|---|
d | Date value for conversion. If this value is hard-coded it must be prefixed and suffixed with a hash (#) character, for example #25/12/2020#. |
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###
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 )
Parameter | 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])
Parameter | 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.
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 )
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])
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 )
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 )
Parameter | Description |
---|---|
r | Reference to a question in a repeating page, repeating section or multiple row data source. |
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.
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: 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. |
Mathematical Operators
Operator | Description |
---|---|
Addition operator. Can be used in any formula eg: | |
Subtraction operator. Can be used in any formula eg: | |
Multiplication operator. Can be used in any formula eg: | |
/ | Multiplication operator. Can be used in any formula eg: |
^ | Power operator. Can be used in any formula eg: |
% | Modulus operator. Can be used in any formula eg: |
Updated 3 months ago