QuickMap Functions

Lists the Excel-style functions supported in QuickMap

This article contains the list of functions currently supported by QuickMap. Most of these functions are identical with or similar to Excel so that they're as familiar as possible.

String Functions

CONCAT
CONCAT(list)
Concatenates a set of values into a string.

CONTAINS
CONTAINS(list, value)
Returns true if the specified list contains the specified item.

FIND
FIND(findText, withinText, caseSensitive, startAt)
Returns the starting position of one text string within another string.

FINDLAST
FIND(findText, withinText, caseSensitive, startAt)
Returns the starting position of the last occurence of one text string within another string.

FORMATTEXT
FORMATTEXT(value, items)
Replaces tags in a string with values. Eg. FORMATTEXT("Hello {0} ", "World")

ISTEXT
ISTEXT(value)
Returns true if the specified value is a string.

LEFT
LEFT(value, numChars)
Returns the leftmost numChars of a string.

LEFTOF
LEFTOF(value, text)
Returns the string left of text

LEN
LEN(value)
Returns the length of a string

LOWER
LOWER(value)
Converts a string to lower case.

MID
MID(value, start, numChars)
Returns a portion of a string beginning at start and extending for numChars.

MIDOF
MIDOF(value, startString, endString)
Returns the text of value between startString and endString

REPLACE
REPLACE(value, find, replaceWith)
Replaces a portion of string with another string.

RIGHT
RIGHT(value, numChars)
Returns the rightmost numChars of a string.

RIGHTOF
RIGHTOF(value, text)
Returns the string right of text

SPLIT
SPLIT(list, separator, asNumber)
Splits a string of delimited values into an array.

TEXTJOIN
TEXTJOIN(delimiter, ignoreEmpty, list)
Combines a list of values into a string.

TEXTVALUE
TEXTVALUE(value)
Converts a value to a string.

TRIM
TRIM(value)
Trims leading and trailing spaces from a string.

UPPER
UPPER(value)
Converts a string to upper case.

Math & Numeric Functions

AVERAGE
AVERAGE(list)
Returns the average value of a list of numbers.

FORMATNUMBER
FORMATNUMBER(value, numberFormat)
Formats a number according to numberFormat. Eg. FORMATNUMBER(12.345, "0.00")

ISNUMBER
ISNUMBER(value)
Returns true if the specified value is a number. Note that this will return false if the value is a number contained in a string.

NUMBERVALUE
NUMBERVALUE(value)
Converts a value to a number.

RAND
RAND()
Returns a random number between 0 and 1.

ROUND
ROUND(value, numDigits)
Rounds a number to the specified number of digits.

SUM
SUM(list)
Returns the sum of a series of numbers.

CINT
CINT(value)
VB function that returns an integer number without decimal places.

Date & Time Functions

DATEDIF
DATEDIF(startDate, endDate, unit)
Returns the difference between two dates according to the unit specified. Valid units are d , h , m , s , ms

DATEVALUE
DATEVALUE(value, dateFormat)
Converts a string representation of a date to a date type. dateFormat components include yyyy , MM , dd , hh mm , ss

DAY
DAY(value)
Returns the day of the month part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

FORMATDATE
FORMATDATE(value, dateFormat)
Formats a date according to the specified format string.

HOUR
HOUR(value)
Returns the hour part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

MINUTE
MINUTE(value)
Returns the minute part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

MONTH
MONTH(value)
Returns the month of the year part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

NOW
NOW(timeZone)
Returns the current UTC date and time or if timeZone is supplied, returns the current local date and time for the time zone. See List of Supported Timezones for more information.

SECOND
SECOND(value)
Returns the second part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

WEEKDAY
WEEKDAY(value)
Returns the day of the week of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

YEAR
YEAR(value)
Returns the year part of the date expression. Convert the expression to a date using DATEVALUE() first to ensure accurate results.

Evaluation Functions

IIF
IIF(condition, trueValue, falseValue)
Checks whether a condition is met and returns the value specified in trueValue if TRUE, otherwise falseValue if FALSE.

ISEMPTY
ISEMPTY(value)
Returns true if the value specified is empty or null.

Type-Independent Aggregator Functions

COUNT
COUNT(list)
Returns the number of items in a list.

FIRST
FIRST(value)
Returns the first value in an array of values.

INDEX
INDEX(value, position)
Returns the item at the specified position in the given array.

INDEXOF
INDEXOF(element)
Returns the index of a container in a list of containers. The 'element' parameter should be a container element.

MAX
MAX(list)
Returns the highest value in the list. Can be used on numbers and text.

MIN
MIN(list)
Returns the lowest value in the list. Can be used on numbers and text.

Miscellaneous Functions

GUID
GUID()
Returns a GUID (UUID).

GETVALUE
GETVALUE(propertyName)
Gets the value of a Custom Property.

LOOKUP
LOOKUP(value, lookIn, keyProperty, valueProperty, defaultValue)
If lookIn is the name of a Custom Property, finds a container where keyProperty is value and returns the value of the property valueProperty.

If lookIn is a container in the main document, finds a child container where keyProperty is value and returns the value of the property valueProperty.
See Sample Workflow for an example.

If no match is found, an error results unless a value has been provided for defaultValue.