Functions

The table below lists all the functions that are available for use wherever a calculations is required in PYXI. This includes Calculated Fields as well as a number of fields of other application configuration record types.

Functions may be nested within each other as much as you need – but bear in mind that you may benefit from breaking up highly nested calculations into separate Fields. This makes it easier to manage the calculations, and potentially easier for your users to understand how complex figures are derived.

Date Functions

FunctionTypeDescription
dateFormat(date [, format])stringDisplay date using optional format text (see Date Formats for syntax)
today()dateToday’s date
now()datetimeThe date and time now
year(date)numericYear of date as a number (e.g. 2012)
month(date)numericMonth of date as a number (e.g. 4 for April)
day(date)numericDay of date within month as a number
dayOfWeek(date)numericDay of date within week as a number (Monday = 1, Sunday = 7)
daysInMonth(date)numericNumber of days in the month in which date falls
weekDaysBetween(firstDate, lastDate)numericWhole Number of week days between dates firstDate and lastDate inclusive
workingDaysBetween(firstDate, lastDate)numericWhole Number of working days between dates firstDate and lastDate inclusive (working days defined as week days that are not public holidays in England)
workingHoursBetween(firstDateTime, lastDateTime)numericNumber of working hours between firstDateTime and lastDateTime inclusive (working days defined as week days that are not public holidays in England)
publicHolidaysBetween(firstDate, lastDate)numericNumber of public holidays (in England) between firstDate and lastDate inclusive
isPublicHoliday(date)logicalTrue if date is a public holiday (in England)
isWeekDay(date)logicalTrue if date is a weekday (Monday to Friday)
isWorkingDay(date)logicalTrue if date is a working day (working days defined as week days that are not public holidays in England)
daysBetween(firstDate, lastDate)numericWhole Number of days between firstDate and lastDate
daysBetweenPrecise(firstDateTime, lastDateTime)numericPrecise Number of days between firstDateTime and lastDateTime, including fraction
daysSince(date)numericNumber of days since date (until now)
daysUntil(date)numericNumber of days until date (from now)
weeksBetween(firstDate, lastDate)numericWhole Number of weeks between firstDate and lastDate
monthsBetween(firstDate, lastDate)numericWhole Number of months between firstDate and lastDate
weekNumber(date)numericWeek number of date
startOfWeek(date)dateDate of the start of the week (Monday) in which date falls
endOfWeek(date)dateDate of the end of the week (Sunday) in which date falls
startOfMonth(date)dateDate of the start of the month in which date falls
endOfMonth(date)dateDate of the end of the month in which date falls
firstDayOfMonth(date, nDay)dateFirst occurence of day nDay (Mon=1, Sun=7) in the month in which date falls
firstFridayOfMonth(date)dateFirst Friday in the month in which date falls
lastDayOfMonth(date, nDay)dateLast occurence of day nDay (Mon=1, Sun=7) in the month in which date falls
lastFridayOfMonth(date)dateLast Friday in the month in which date falls
startOfQuarter(date)dateDate of the start of the calendar quarter in which date falls
endOfQuarter(date)dateDate of the end of the calendar quarter in which date falls
startOfYear(date)dateDate of the start of the year (1st Jan) in which date falls
endOfYear(date)dateDate of the end of the year (31st Dec) in which date falls
quarterNumber(date)numberNumber of the calendar quarter in which date falls (1-4)
addDays(date, nDays)dateThe date nDays on from date. nDays can be negative to give an earlier date.
addWeeks(date, nWeeks)dateThe date nWeeks on from date. nWeeks can be negative to give an earlier date.
addMonths(date, nMonths)dateThe date nMonths on from date. nMonths can be negative to give an earlier date.
addYears(date, nYears)dateThe date nYears on from date. nYears can be negative to give an earlier date.
Return to Top

Number Functions

FunctionTypeDescription
abs(number)numericAbsolute Value of number
round(number, nNumDecimalPlaces)numericnumber Rounded to nNumDecimalPlaces
trunc(number, nNumDecimalPlaces)numericnumber truncated to nNumDecimalPlaces (without rounding)
numberFormat(number, nNumDecimalPlaces)stringDisplay Number number with thousand separator (comma) and nNumDecimalPlaces decimal places
currencyFormat(number, nNumDecimalPlaces)stringDisplay Number number with currency symbol, thousand separator (comma) and nNumDecimalPlaces decimal places
max(number1, number2 [,…])numericMaximum of all the parameters
min(number1, number2 [,…])numericMinimum of all the parameters
sqrt(number)numericSquare Root of number
Return to Top

String Functions

FunctionTypeDescription
left(string, nCharCount)stringThe leftmost nCharCount characters of string
right(string, nCharCount)stringThe rightmost nCharCount characters of string
substr(string, nStartPos, nCharCount)stringThe nCharCount characters of string starting at position nStartPos
substitute(haystack, oldNeedle, newNeedle)stringString haystack with all instances of oldNeedle replaced with newNeedle
find(needle, haystack [, nStartPos])numericThe starting position of string needle within string haystack, optionally starting from position nStartPos
countWithin(needle, haystack)numericNumber of times string needle appears within string haystack
upper(string)stringstring converted to all upper-case
lower(string)stringstring converted to all lower-case
proper(string)stringstring converted to proper-case (first letter of each word capitalised)
pluralword(word [,amount])stringConvert word to its plural. Optionally provide amount – the word will be converted to plural only if the amount is anything other than one.
pluraltext(word, amount)stringConvert word and amount to a description being amount word(s).e.g. pluraltext(“device”, 1) returns “1 device”, while pluraltext(“person”, 2) returns “2 people”.
length(string)numericNumber of characters in string
trim(string)stringstring with whitespace trimmed from left and right
value(string)numericNumeric value of string
contains(haystack, needle)logicalString haystack contains string needle (case-insensitive)
concat(string1, string2 [, …])stringConcatenation of 2 or more string parameters
firstName(fullName)stringGet FirstName from fullName of a person
lastName(fullName)stringGet LastName from fullName of a person
splitCamelCase(camelCaseText)stringSplit camel-case string into separate words e.g. “AssetType” becomes “Asset Type”
Return to Top

Logical Functions

FunctionTypeDescription
if(logical, answerIfTrue, answerIfFalse)anyIf logical is true, then answerIfTrue, otherwise answerIfFalse
and(logical1, logical2 [,…])logicalTrue if all the parameters are true
or(logical1, logical2 [,…])logicalTrue if at least 1 parameter is true
xor(logical1, logical2)logicaleXclusive OR: true if either logical1 or logical1 is true, but not both
not(logical)logicalTrue if logical is not true. Note alternative syntax is simply !logical
empty(parameter)logicalTrue if parameter is empty e.g. an empty string, a non-record, zero, or false
isBlank(parameter)logicalTrue if parameter is empty (this is an alternative name for the empty() function)
notEmpty(parameter)logicalTrue if parameter is not empty (the inverse of the empty() function)
Return to Top

Lookup Functions

FunctionTypeDescription
match(lookupValue, aSourceArray)numericNumeric index of lookupValue in aSourceArray
Array syntax is individual values comma-separated, all surrounded by braces ({ and }).
e.g. match(“Weeks”,{”Weeks”,”Quarters”,”Years”}) = 1
index(aSourceArray, nIndex)anyItem from aSourceArray with nIndex
Array syntax is individual values comma-separated, all surrounded by braces ({ and }).
e.g. index({“Weeks”,”Quarters”,”Years”},2) = “Quarters”
lookup(lookupValue, aSourceArray, aTargetArray)numberGet numeric index of lookupValue in aSourceArray, then lookup equivalent item from aTargetArray.
Array syntax is individual values comma-separated, all surrounded by braces ({ and }).
e.g. lookup(“Weeks”,{”Weeks”,”Quarters”,”Years”},{52,4,1}) = 52
Return to Top

Record Functions

FunctionTypeDescription
getRecord(recordType, recordName|recordID)recordGet record of recordType with name of recordName or recordID of recordID
recordExists(recordType, recordName|recordID)logicalTrue if record exists of recordType with name of recordName or recordID of recordID
searchForRecord(recordType, searchTerm [,…searchTerm])recordTrue if record exists of recordType found with any of multiple searchTerm. Useful in Action Script that might need to look for an existing record by name, serial number etc.
isRecord(record)logicalTrue if record is a real record. Usually used in processing the results from a Form in an Action Script
getFieldValue(record, fieldName)anyGet value of field fieldName on record record. Note that this gives the same result as using the “:” syntax on a variable, but provides more flexibility as both the record and the fieldname can be variables or calculations e.g. rPerson:FirstName and getFieldValue(rPerson, “FirstName”) are equivalent, but you can also use this function for examples such as getFieldValue(getConfig(“currentOrg”), “Name”) where you have saved an Organisation record in config variable currentOrg
isFieldUpdated(fieldName)logicalTrue if fieldName was updated in the just-completed add/edit of this record. Note that calling this function only makes sense within a Rule that runs on add or edit of a record. Will return true if a value was passed for this fieldName, regardless of whether this value is the same or different from the previous value of the same field (on edit).
getOurOrganisationRecord()recordGet Organisation record for our organisation
countActiveCategoryValues(recordType)numberThe number of active category values of recordType
triggerEvent(eventName [,record])updateTrigger Event named eventName on current record, or on record if specified
setDefaultRecord(record)updateSet record as the default record of its type. This record will be saved permanently (until overwritten by a subsequent use of this function), for the current user only. It will be retained between logins. It can be retrieved for use in any other script by using the corresponding getDefaultRecord() function, or it can be cleared with clearDefaultRecord().
getDefaultRecord(recordType)recordGet the user’s current default record of this recordType. This will retrieve a record previously saved using the setDefaultRecord() function.
clearDefaultRecord(recordType)updateClear the user’s current default record of this recordType. This will clear a record previously saved using the setDefaultRecord() function (note – it won’t delete the record – it is simply removing the reference to this record as the current default for the current user.
Return to Top

Aggregations Functions

FunctionTypeDescription
countOf(recordType [,filter[, ignoreCurrentFilter]])numberThe number of records of recordType, with optional filter text. If this function is used on a Page viewed in RecordType DisplayContext, then it will automatically filter by the currently displayed filter for that RecordType, as well as any filter explicitly set in the filter parameter. If you set ignoreCurrentFilter to true, then only your explicit filter will apply. This enables you, for example, to get total values regardless of current filter, and to show the filtered values as a proportion of the totals.
sumOf(recordType [,fieldName [,filter[, ignoreCurrentFilter]]])numberThe sum of the values in fieldName (defaults to “Value” being the standard value field of transactions) for records of recordType, with optional filter text e.g. sumOf(“Asset”, “Cost”, “Vehicle”). If this function is used on a Page viewed in RecordType DisplayContext, then it will automatically filter by the currently displayed filter for that RecordType, as well as any filter explicitly set in the filter parameter. If you set ignoreCurrentFilter to true, then only your explicit filter will apply. This enables you, for example, to get total values regardless of current filter, and to show the filtered values as a proportion of the totals.
averageOf(recordType [,fieldName [,filter[, ignoreCurrentFilter]]])numberThe average of the values in fieldName (defaults to “Value” being the standard value field of transactions) for records of recordType, with optional filter text. If this function is used on a Page viewed in RecordType DisplayContext, then it will automatically filter by the currently displayed filter for that RecordType, as well as any filter explicitly set in the filter parameter. If you set ignoreCurrentFilter to true, then only your explicit filter will apply. This enables you, for example, to get total values regardless of current filter, and to show the filtered values as a proportion of the totals.
maximumOf(recordType [,fieldName [,filter[, ignoreCurrentFilter]]])numberThe maximum of the values in fieldName (defaults to “Value” being the standard value field of transactions) for records of recordType, with optional filter text. If this function is used on a Page viewed in RecordType DisplayContext, then it will automatically filter by the currently displayed filter for that RecordType, as well as any filter explicitly set in the filter parameter. If you set ignoreCurrentFilter to true, then only your explicit filter will apply. This enables you, for example, to get total values regardless of current filter, and to show the filtered values as a proportion of the totals.
minimumOf(recordType [,fieldName [,filter[, ignoreCurrentFilter]]])numberThe minimum of the values in fieldName (defaults to “Value” being the standard value field of transactions) for records of recordType, with optional filter text. If this function is used on a Page viewed in RecordType DisplayContext, then it will automatically filter by the currently displayed filter for that RecordType, as well as any filter explicitly set in the filter parameter. If you set ignoreCurrentFilter to true, then only your explicit filter will apply. This enables you, for example, to get total values regardless of current filter, and to show the filtered values as a proportion of the totals.
countOfLinkedRecords(recordsFieldName [,filter])numberThe number of records linked to this record, where recordsFieldName is the name (as a string) of a built-in or field of linked records, optionally filtered by filter text e.g. countOf(“Notes”)
sumOfLinkedRecords(recordsFieldName [, filter [, valueFieldName]])numberThe total of value of records linked to this record, where recordsFieldName is the name (as a string) of a built-in or field of linked records, optionally filtered by filter text. The result is the sum of field valueFieldName on the linked records (defaults to “Value” being the standard value field of transactions) e.g. sumOfLinkedRecords(“Sales”)
averageOfLinkedRecords(recordsFieldName [, filter [, valueFieldName]])numberThe average value of records linked to this record, where recordsFieldName is the name (as a string) of a built-in or field of linked records, optionally filtered by filter text. The result is the average of field valueFieldName on the linked records (defaults to “Value” being the standard value field of transactions) e.g. averageOfLinkedRecords(“Sales”)
maximumOfLinkedRecords(recordsFieldName [, filter [, valueFieldName]])numberThe maximum value of records linked to this record, where recordsFieldName is the name (as a string) of a built-in or field of linked records, optionally filtered by filter text. The result is the maximum of field valueFieldName on the linked records (defaults to “Value” being the standard value field of transactions) e.g. maximumOfLinkedRecords(“Sales”)
minimumOfLinkedRecords(recordsFieldName [, filter [, valueFieldName]])numberThe minimum value of records linked to this record, where recordsFieldName is the name (as a string) of a built-in or field of linked records, optionally filtered by filter text. The result is the minimum of field valueFieldName on the linked records (defaults to “Value” being the standard value field of transactions) e.g. minimumOfLinkedRecords(“Sales”)
Return to Top

File Functions

FunctionTypeDescription
fileText(fileName [, directoryName])stringContents of a text file on this record whose name includes fileName. Optionally specify directory directoryName – either an absolute path beginning with “/”, in which case only files in that directory/sub-directory will be considered, or a directory name not beginning with “/” in which case only files in a directory named directoryName will be considered.
getSpreadsheetCellValue([File,] cellRef)anyGet the value of a cell from a spreadsheet File. The cellRef can be the name of a Named Range in the spreadsheet (case-sensitive), or a cell reference including sheet name e.g. MySheetName!A1. If the function is called in the context of a File object, then only the cellRef is needed. Otherwise, pass the File object as the first parameter.
moveFile([File,] newOwner [, subDirectory])updateMove a file to a newOwner record, optionally into a specific subDirectory. If the function is called in the context of a File object, then only the newOwner and optional subDirectory is needed. Otherwise, pass the File object to be moved as the first parameter.
Return to Top

Page Functions

FunctionTypeDescription
getpagecontent(pageName, [evaluateTemplate])stringGet the content of a Page named pageName. Typical usage is in a template where a Page record is used for the convenient storage of a block of Template that might require occasional user maintenance, such as the template content of an outgoing email. By default, the page content is evaluated as a template according to the current output context. If, however, you want the content raw (i.e. not yet evaluated as a template), pass false as the second parameter evaluateTemplate. You would typically want this if you are using the page content as the default value in a multiline text box in a Form, where you allow the user to modify that template content at the time e.g. as part of a Step to send an email.
Return to Top

Display Functions

FunctionTypeDescription
displayRecordCard([DisplayFields])textGenerate a RecordCard for the current record. Optionally specify the fields of the record to be displayed, as a comma-separated text list of the field names.
Note that the panel will automatically include an Edit link if the record is editable, and will also provide links to any relevant Next Steps.
. Example: displayRecordCard(“Owner,ParentAsset”)
displayRAGListItem(RAGStatusField)textGenerate a display for the current record showing its link and a RAG Status, in a standard list item format typically intended for use in Column View lists. Specify the field of the record used to generate a RAG indication Example: displayRAGListItem(“RecordCompletenessRAG”)
displayIcon(iconName [, colour])textGenerate display html of a fixed-width icon, optionally with a specific html colour (named colour or hex value preceded with #)
displayIconCounter(iconName, count [, colour])textGenerate display html of an icon with numeric counter superscript, optionally with a specific html colour (named colour or hex value preceded with #)
displayRagStatus(percent [, amberLow, amberHigh])textGenerate html of a RAG status indicator. Pass percent between 0 and 1. Bar colour is determined by amberLow and amberHigh – these default to 0.35 and 0.80, otherwise pass in values to override these ranges. Colour is red if less than amberLow, green if greater than amberHigh, otherwise amber.
displayMultiColumn(content1 [, …])textGenerate the HTML for multiple columns of information of fixed widths. Use in template definitions of a Page.
displayForceNewRow()textWhen used in the template definition of a Page, forces subsequent content to start on a new display row regardless of the number and size of boxes displayed previously
displayTableStart([heading1 [, heading2 …]])textGenerate the HTML for the start of a Display Table with column headings of heading1 etc. Typically used in the template definition of a Page. Following this, call displayTableRow() for each row, then terminate with a call to displayTableEnd(). Note – call displayTableStart() for a table with no column headings.
displayTableRow(cellContent1 [, cellContent2 …])textGenerate the HTML for a Display Table row with cell contents of cellContent1 etc. Typically used in the template definition of a Page. This must be called one or more times after calling displayTableStart(). The number of cells of content should match the number of headings passed in displayTableStart().
displayTableFullWidthRow(content)textGenerate the HTML for a Display Table row with single cell having content of content.
displayTableEnd()textGenerate the HTML for the end of a Display Table. Typically used in the template definition of a Page. This must be called after all the rows of a table have been added.
Return to Top

Filter Functions

FunctionTypeDescription
matchToFilter([record,] filter)logicalTrue if record matches filter text. If record is not passed as first parameter, then the filter will be tested against the record against which the calculation is being evaluated.
setCurrentFilter(recordType, filter)updateSet the current filter for recordType to filter. Applies only in the context of a user within the user interface. The filter is set for the current user only, for the duration of the session until logout. It is the functional equivalent of the user setting a filter themselves on a record selection View
Return to Top

Specialist – UK Tax Functions

FunctionTypeDescription
UKTaxYear(date)stringThe UK HMRC Tax Year Code in “yy-yy” format for date
UKTaxMonth(date)stringThe UK HMRC Tax Month Code in “nn” format for date
UKTaxWeek(date)stringThe UK HMRC Tax Week Code in “nn” format for date
Return to Top

Configuration Functions

FunctionTypeDescription
getConfig(settingName)anyGet the value of configuration setting settingName
getConfig(settingName, value)updateSet the value of configuration setting settingName to value
Return to Top

User Functions

FunctionTypeDescription
getCurrentUser()recordThe current user record
currentUserIsAdmin()logicalTrue if current user is an administrator
currentUserIsDeveloper()logicalTrue if current user is a developer
currentUserInGroup(groupName)logicalTrue if current user is a member of group groupName
isSmallScreen()logicalTrue if currently using a small screen (e.g. mobile phone)
countUserSessions()numberNumber of times the current user has logged in
notifyUser(template)updateSend current user a notification message. The template is evaluated as a template against the current record and a closable message displayed
Return to Top

Account Functions

FunctionTypeDescription
accountName()stringName of this account (usually the organisation name)
accountHasMultipleUsers()logicalTrue if there is more than one user on the account
accountHasGroups()logicalTrue if there is at least one Group on the account
appName()stringName of this application
nextBillingDate()dateDate of next invoice/payment
Return to Top