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.
Function Categories: Date | Number | String | Logical | Lookup | Record | Automation | Aggregations | File | Page | Display | Filter | Web Page Scraper | Specialist – UK Tax | Settings | User | Account
| Function | Type | Description |
|---|---|---|
| dateFormat(date [, format]) | string | Display date using optional format text (see Date Formats for syntax) |
| today() | date | Today’s date |
| now() | datetime | The date and time now |
| year(date) | numeric | Year of date as a number (e.g. 2012) |
| month(date) | numeric | Month of date as a number (e.g. 4 for April) |
| day(date) | numeric | Day of date within month as a number |
| dayOfWeek(date) | numeric | Day of date within week as a number (Monday = 1, Sunday = 7) |
| daysInMonth(date) | numeric | Number of days in the month in which date falls |
| weekDaysBetween(firstDate, lastDate) | numeric | Whole Number of week days between dates firstDate and lastDate inclusive |
| workingDaysBetween(firstDate, lastDate) | numeric | Whole 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) | numeric | Number of working hours between firstDateTime and lastDateTime inclusive (working days defined as week days that are not public holidays in England) |
| publicHolidaysBetween(firstDate, lastDate) | numeric | Number of public holidays (in England) between firstDate and lastDate inclusive |
| isPublicHoliday(date) | logical | True if date is a public holiday (in England) |
| isWeekDay(date) | logical | True if date is a weekday (Monday to Friday) |
| isWorkingDay(date) | logical | True if date is a working day (working days defined as week days that are not public holidays in England) |
| daysBetween(firstDate, lastDate) | numeric | Whole Number of days between firstDate and lastDate |
| daysBetweenPrecise(firstDateTime, lastDateTime) | numeric | Precise Number of days between firstDateTime and lastDateTime, including fraction |
| daysSince(date) | numeric | Number of days since date (until now) |
| daysUntil(date) | numeric | Number of days until date (from now) |
| weeksBetween(firstDate, lastDate) | numeric | Whole Number of weeks between firstDate and lastDate |
| monthsBetween(firstDate, lastDate) | numeric | Whole Number of months between firstDate and lastDate |
| weekNumber(date) | numeric | Week number of date |
| startOfWeek(date) | date | Date of the start of the week (Monday) in which date falls |
| endOfWeek(date) | date | Date of the end of the week (Sunday) in which date falls |
| startOfMonth(date) | date | Date of the start of the month in which date falls |
| endOfMonth(date) | date | Date of the end of the month in which date falls |
| firstDayOfMonth(date, nDay) | date | First occurence of day nDay (Mon=1, Sun=7) in the month in which date falls |
| firstFridayOfMonth(date) | date | First Friday in the month in which date falls |
| lastDayOfMonth(date, nDay) | date | Last occurence of day nDay (Mon=1, Sun=7) in the month in which date falls |
| lastFridayOfMonth(date) | date | Last Friday in the month in which date falls |
| startOfQuarter(date) | date | Date of the start of the calendar quarter in which date falls |
| endOfQuarter(date) | date | Date of the end of the calendar quarter in which date falls |
| startOfYear(date) | date | Date of the start of the year (1st Jan) in which date falls |
| endOfYear(date) | date | Date of the end of the year (31st Dec) in which date falls |
| quarterNumber(date) | number | Number of the calendar quarter in which date falls (1-4) |
| addDays(date, nDays) | date | The date nDays on from date. nDays can be negative to give an earlier date. |
| addWeeks(date, nWeeks) | date | The date nWeeks on from date. nWeeks can be negative to give an earlier date. |
| addMonths(date, nMonths) | date | The date nMonths on from date. nMonths can be negative to give an earlier date. |
| addYears(date, nYears) | date | The date nYears on from date. nYears can be negative to give an earlier date. |
| Function | Type | Description |
|---|---|---|
| abs(number) | numeric | Absolute Value of number |
| round(number, nNumDecimalPlaces) | numeric | number Rounded to nNumDecimalPlaces |
| trunc(number, nNumDecimalPlaces) | numeric | number truncated to nNumDecimalPlaces (without rounding) |
| numberFormat(number, nNumDecimalPlaces) | string | Display Number number with thousand separator (comma) and nNumDecimalPlaces decimal places |
| currencyFormat(number, nNumDecimalPlaces) | string | Display Number number with currency symbol, thousand separator (comma) and nNumDecimalPlaces decimal places |
| max(number1, number2 [,…]) | numeric | Maximum of all the parameters |
| min(number1, number2 [,…]) | numeric | Minimum of all the parameters |
| sqrt(number) | numeric | Square Root of number |
| Function | Type | Description |
|---|---|---|
| left(string, nCharCount) | string | The leftmost nCharCount characters of string |
| right(string, nCharCount) | string | The rightmost nCharCount characters of string |
| substr(string, nStartPos, nCharCount) | string | The nCharCount characters of string starting at position nStartPos |
| substitute(haystack, oldNeedle, newNeedle) | string | String haystack with all instances of oldNeedle replaced with newNeedle |
| find(needle, haystack [, nStartPos]) | numeric | The starting position of string needle within string haystack, optionally starting from position nStartPos |
| countWithin(needle, haystack) | numeric | Number of times string needle appears within string haystack |
| upper(string) | string | string converted to all upper-case |
| lower(string) | string | string converted to all lower-case |
| proper(string) | string | string converted to proper-case (first letter of each word capitalised) |
| pluralword(word [,amount]) | string | Convert 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) | string | Convert 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) | numeric | Number of characters in string |
| trim(string) | string | string with whitespace trimmed from left and right |
| value(string) | numeric | Numeric value of string |
| contains(haystack, needle) | logical | String haystack contains string needle (case-insensitive) |
| concat(string1, string2 [, …]) | string | Concatenation of 2 or more string parameters |
| firstName(fullName) | string | Get FirstName from fullName of a person |
| lastName(fullName) | string | Get LastName from fullName of a person |
| splitCamelCase(camelCaseText) | string | Split camel-case string into separate words e.g. “AssetType” becomes “Asset Type” |
| Function | Type | Description |
|---|---|---|
| if(logical, answerIfTrue, answerIfFalse) | any | If logical is true, then answerIfTrue, otherwise answerIfFalse |
| and(logical1, logical2 [,…]) | logical | True if all the parameters are true |
| or(logical1, logical2 [,…]) | logical | True if at least 1 parameter is true |
| xor(logical1, logical2) | logical | eXclusive OR: true if either logical1 or logical1 is true, but not both |
| not(logical) | logical | True if logical is not true. Note alternative syntax is simply !logical |
| empty(parameter) | logical | True if parameter is empty e.g. an empty string, a non-record, zero, or false |
| isBlank(parameter) | logical | True if parameter is empty (this is an alternative name for the empty() function) |
| notEmpty(parameter) | logical | True if parameter is not empty (the inverse of the empty() function) |
| Function | Type | Description |
|---|---|---|
| match(lookupValue, aSourceArray) | numeric | Numeric index of lookupValue in aSourceArray e.g. SET options[] = “Weeks” SET options[] = “Quarters” SET options[] = “Years” match(“Weeks”, options) = 1 |
| index(aSourceArray, nIndex) | any | Item from aSourceArray with nIndex e.g. SET options[] = “Weeks” SET options[] = “Quarters” SET options[] = “Years” index(options, 2) = “Quarters” |
| lookup(lookupValue, aSourceArray, aTargetArray) | number | Get numeric index of lookupValue in aSourceArray, then lookup equivalent item from aTargetArray. e.g. SET options[] = “Weeks” SET options[] = “Quarters” SET options[] = “Years” SET values[] = 52 SET values[] = 4SET values[] = 1 lookup(“Weeks”, options, values) = 52 |
| Function | Type | Description |
|---|---|---|
| getRecord(recordType, recordName|recordID) | record | Get record of recordType with name of recordName or recordID of recordID |
| recordExists(recordType, recordName|recordID) | logical | True if record exists of recordType with name of recordName or recordID of recordID |
| searchForRecord(recordType, searchTerm [,…searchTerm]) | record | True 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) | logical | True if record is a real record. Usually used in processing the results from a Form in an Action Script |
| getFieldValue(record, fieldName) | any | Get 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(getSetting(“currentOrg”), “Name”) where you have saved an Organisation record in persistent setting currentOrg |
| isFieldUpdated(fieldName) | logical | True if fieldName was updated in the just-completed add/edit of this record. Note that calling this function only makes sense within an Automation 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() | record | Get Organisation record for our organisation |
| countActiveCategoryValues(recordType) | number | The number of active category values of recordType |
| setDefaultRecord(record) | update | Set 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) | record | Get the user’s current default record of this recordType. This will retrieve a record previously saved using the setDefaultRecord() function. |
| clearDefaultRecord(recordType) | update | Clear 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. |
| Function | Type | Description |
|---|---|---|
| callAutomation(automationName[, paramsArray [,record]]) | update | Call Automation named automationName on current record, or on record if specified, optionally passing a paramsArray. Automation may optionally return a response value |
| callWebhook(webhookName[, paramsArray [,record]]) | update | Call Webhook named webhookName on current record, or on record if specified, optionally passing a paramsArray. The optional paramsArray is provided as an input to the BodyScript action script on the Webhook record which is used to generate the full body payload of the Webhook call. If there is no BodyScript then this paramsArray will be passed unchanged as the body payload of the Webhook call. |
| triggerEvent(eventName [,record]) | update | Trigger Event named eventName on current record, or on record if specified |
| writeLog(logText [,record]) | update | Write text logText to Developer Log linked to current record, or linked to record if specified |
| Function | Type | Description |
|---|---|---|
| countOf(recordType [,filter[, ignoreCurrentFilter]]) | number | The 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]]]) | number | The 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]]]) | number | The 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]]]) | number | The 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]]]) | number | The 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]) | number | The 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]]) | number | The 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]]) | number | The 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]]) | number | The 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]]) | number | The 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”) |
| Function | Type | Description |
|---|---|---|
| fileText(fileName [, directoryName]) | string | Contents 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) | any | Get 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]) | update | Move 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. |
| Function | Type | Description |
|---|---|---|
| getpagecontent(pageName, [evaluateTemplate]) | string | Get 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. |
| Function | Type | Description |
|---|---|---|
| displayRecordCard([DisplayFields]) | text | Generate 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”) |
| displayRecordLink(record [, linkText [, iconName]]) | displayRecordLink(recordType, name [, linkText [, iconName]]) | text | Generate a link to either record itself or the record of recordType named name, optionally overriding text to linkText. Optionally override the default icon to iconName |
| displayFilterLink(recordType, filter, linkText [, iconName]) | text | Generate a link to display filtered view of records of recordType, filtered with filter, and display text of linkText. Optionally override the default icon to iconName |
| displayPageLink(name [, linkText [, iconName]]) | text | Generate a link to a Page named name, optionally overriding text to linkText. Optionally override the default icon to iconName |
| displayInternalLink(internalUrl, linkText [, iconName]) | text | Generate a link to a specified internal url, with display text of linkText and optionally an icon iconName |
| displayIcon(iconName [, colour]) | text | Generate display html of a fixed-width icon, optionally with a specific html colour (named colour or hex value preceded with #) |
| displayStepLink(stepName[, linkText] | record, stepName[, linkText]) | text | Generate a link to the step named stepName for either the current record or the record record passed as the first parameter. Optionally replace the default link text with linkText. |
| displayTableStart([heading1 [, heading2 …]]) | text | Generate 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 …]) | text | Generate 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) | text | Generate the HTML for a Display Table row with single cell having content of content. |
| displayTableEnd() | text | Generate 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. |
| displayEmbeddedVideo(youtubeVideoID [, cssWidth]) | text | Generate the HTML for an embedded YouTube video. Pass the ID of the video clip and optionally the width as a CSS string (defaults to “100%”). The height of the video clip will automatically resize based on its width. |
| Function | Type | Description |
|---|---|---|
| matchToFilter([record,] filter) | logical | True 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) | update | Set 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 |
| Function | Type | Description |
|---|---|---|
| getWebPage(url) | object | Get a Web Page Scraper object for a given url. Returns an object which should be passed to other Web Page Scraper functions to get the desired results from the page |
| getWebPageNodes(oWebPage, cssSelector) | list | Get a list of Web Page Nodes from a Web Page Scraper (return value of getWebPage() function) using a CSS Selector. The CSS Selector may contain any valid CSS2 syntax. It does not support :not and other pseudo-selectors from CSS3 |
| getWebPageNodeText(oWebPage, cssSelector) | getWebPageNodeText(oWebPageNode) | string | Get the text of a node from a Web Page Scraper (return value of getWebPage() function) using a CSS Selector, or the text of a node already extracted typically in a FOR EACH ITEM OF iteration of getWebPageNodes(). The CSS Selector may contain any valid CSS2 syntax. It does not support :not and other pseudo-selectors from CSS3 |
| getWebPageNodeAttribute(oWebPage, cssSelector, attributeName) | getWebPageNodeAttribute(oWebPageNode, attributeName) | list | Get the value of an attribute of a node either from a Web Page Scraper (return value of getWebPage() function) using a CSS Selector or from a node already extracted typically in a FOR EACH ITEM OF iteration of getWebPageNodes(). The CSS Selector may contain any valid CSS2 syntax. It does not support :not and other pseudo-selectors from CSS3 |
| getListItemCount(oList) | number | Get number of items in a list. |
| Function | Type | Description |
|---|---|---|
| UKTaxYear(date) | string | The UK HMRC Tax Year Code in “yy-yy” format for date |
| UKTaxMonth(date) | string | The UK HMRC Tax Month Code in “nn” format for date |
| UKTaxWeek(date) | string | The UK HMRC Tax Week Code in “nn” format for date |
| Function | Type | Description |
|---|---|---|
| getSetting(settingName) | any | Get the value of persistent setting settingName |
| setSetting(settingName, value) | update | Set the value of persistent setting settingName to value |
| Function | Type | Description |
|---|---|---|
| getCurrentUser() | record | The current user record |
| currentUserIsAdmin() | logical | True if current user is an administrator |
| currentUserIsDeveloper() | logical | True if current user is a developer |
| currentUserInGroup(groupName) | logical | True if current user is a member of group groupName |
| countUserSessions() | number | Number of times the current user has logged in |
| notifyUser(template) | update | Send current user a notification message. The template is evaluated as a template against the current record and a closable message displayed |
| Function | Type | Description |
|---|---|---|
| accountName() | string | Name of this account (usually the organisation name) |
| accountHasMultipleUsers() | logical | True if there is more than one user on the account |
| accountHasGroups() | logical | True if there is at least one Group on the account |
| appName() | string | Name of this application |
| appSiteLink() | string | Html link to this application’s website |
| nextBillingDate() | date | Date of next invoice/payment |