Calculations

Introduction

There are many places in PYXI where you can include Calculations. These can be as simple or as sophisticated as you need.

Calculations are used in Calculated Fields, Templates and in various other places in your configuration, in particular being used to control various behaviour of Record Types such as the Display Name, display Icon, and record edit and delete permissions.

Calculations are evaluated in the context of a particular record. This depends on the circumstances in which the calculation is used.

  • Calculations in Calculated Fields are evaluated on the record to which that Field relates
  • Calculations in Templates are evaluated on the record for which the template is being evaluated
  • Calculations included in Record Type settings are evaluated on each record of that Record Type as appropriate to the context e.g. the allowDeleteCalc will be evaluated against an individual record of that Record Type to determine whether it is permissible for the user to delete that record

The simplest form of Calculation is the name of a single field of the current record. More sophisticated calculations can use Functions – each argument to a function is itself a Calculation. Functions can, of course, be nested to any required level, although if an individual calculation contains lots of nesting, this is usually a good indication that you should consider breaking the logic down into separate Calculated Fields, or calculate and store some of the values in fields using Rules.

Developing and Testing Calculations

As a Developer user, you will find a Playpen option at the bottom-left of every record page. This Playpen provides you with a facility to test Calculations – these are evaluated against the record ‘on the fly’ and the result shown to you.
If you need help on a particular function, type its name and the opening parenthesis – the error message will now show that this function is incomplete, but if you hover over the function name, you will see a pop-up window with the complete help description for the function, including details of its parameters.

Elements in a Calculation

Calculations can be made up from any of the following:

  • Numbers – Any number
  • Text – Enclose any text in single-quotes (‘), double-quotes (“) or back-ticks (`). Note that whichever of these you use dictates which ones can be used inside the text.
  • Logical – true or false, usually being either the result of a Function or the value of a Field of the record. Note that many uses of a calculation require the result to be logical – e.g. the allowDeleteCalc on Record Types expects a result of true or false to determine whether it is permissible to delete that record.
  • Dates – All dates are represented as text in the format “yyyy-mm-dd” e.g. “2018-12-31” represents December 31st, 2018. (Note that you can choose how to display a date using a Date Function)
  • Fields – References to fields of the current record. Do not wrap the name in quotes. You can refer to both Read/Write and Read-Only fields. Fields include those built-in for each Record Type plus any Fields you have specified (including, of course, calculated Fields).
  • Linked Record Field References – You can refer to the fields of a ‘linked record’ specifying the linked record field name, followed by a colon (:), followed by the field name in that record. For example, if the current record type is Person, you can refer to the website of the company attached to this person by entering Organisation:URL, bearing in mind that Organisation is a linked Field of Person that links to an Organisation record.

Expressions and Operators

As well as using any of the above elements on their own, or as parameters to a Function, you can also use all the basic mathematical operators that you would expect.

OperatorDescriptionDetails
=equals Generally, this evaluates true if both operands (the values either side of the operator) are the same. However, there ar a number of special cases where the operands are of certain types, as follows:
  • If one operand is a record and the other is text, then the text value will be compared against the display name of the record.
  • If one operand is a Category value and the other is text, then the text value will be compared against the name of the category value.
!= or <>not equalsThe logical inverse of equals discussed above
>greater thanApplicable only when both operands are the same type e.g. numeric, date or text
>=greater than or equal toApplicable only when both operands are the same type e.g. numeric, date or text
<less thanApplicable only when both operands are the same type e.g. numeric, date or text
<=less than or equal toApplicable only when both operands are the same type e.g. numeric, date or text
+plus / concatenateThe results from this operator depend on the types of operand, as follows:
  • If the first operand is a date and the second is a number (positive or negative), then the result is that number of days added to the date i.e. the equivalent of the addDays() Function.
  • If both operands are not text or a number, then the answer is zero
  • If one of the operands is text, then the two operands are concatenated as a text result e.g. equivalent to the concat() Function.
  • Otherwise, if both operands are numbers, then the result is the numeric sum of the operands.
minusThe results from this operator depend on the types of operand, as follows:
  • If the first operand is a date and the second is a number (positive or negative), then the result is that number of days deducted from the date i.e. the equivalent of the addDays() Function passed the negative of the number.
  • If both operands are dates then the result is the number of days between them i.e. the equivalent of the daysBetween() Function.
  • If both operands are not numbers, then the answer is zero
  • Otherwise, the result is the numeric difference between the operands.
*multiplyIf both operands are numeric, the result is one multiplied by the other, otherwise zero
/divideIf both operands are numeric, the result is one divided by the other, otherwise zero. Note that if the divisor is zero, the function gracefully returns a result of zero rather than an error.
^powerIf both operands are numeric, the result is the first operand raised to the power of the second operand (base 10)
!notLogical not – returns the logical inverse of the operand following.