Working with Existing Fields

Referencing Existing Field Values

Existing fields can be referenced using square brackets. Note, we specify the field by it's "Label" surrounded by double quotes.

// Returns the value of the Net Amount field
["Net Amount"]

You can combine references to existing fields in all the usual ways:

["Net Amount"] + ["Tax Amount"]  
["Gross Amount"] + 100
ROUND(["Net Amount"], 0)

Referencing List Option Fields

Suppose we have a field called "Job Status" and that field is a Select field connected to a Content List. In this case the function will return the "Label" of the list option. This will always be a String:

// Returns a string e.g. "Active"
["Job Status"]

Referencing Connected Content Types

Suppose we have a Content Type called "Job" with a field called "Client" where Client is not simply a list, but a Content Type in it's own right. In this case the function will return the "Label" field of the related Client. Normally, this will be something like the "Name" field. Again, this will always be a String:

// Returns a string e.g. "ACME Corp Ltd"
["Client"]

Referencing Other Fields on Connected Content Types

In the above example, suppose we want to reference a different field on the Client. We can do this easily by chaining fields with a dot:

// Returns the value of the Telephone field on the Client e.g. "0114 123 4567"
["Client"."Telephone"]

You can even chain these references further. Suppose we don't have a telephone field on the Client itself. Instead, the Client has a "Billing Contact" and the Telephone field is on the Contact record. In this case:

// Returns the value of the Telephone field for the Billing Contact
["Client"."Billing Contact"."Telephone"]

In the above examples, we were referencing fields by following links where the links exist as fields on the Client record. "Related Content" is where the relationship goes the other way. For example, Clients can have many "Jobs". There is no Job field on the Client record but there is a Client field on each Job.

These are referenced using the RELATED keyword as follows:

// Returns an array of Job Ids
RELATED(["Jobs"])

You can reference other fields on the Job in the same way we did before:

// Returns an array of Job "Start Dates"
RELATED(["Jobs"."Start Date"])

This is mostly useful when combined with Aggregate Functions:

// Returns the total number of Jobs for this Client
COUNT(RELATED(["Jobs"]))

// Returns the "Start Date" of the first Job for this Client
MIN(RELATED(["Jobs"."Start Date"]))

// Returns the "Start Date" of the most recent Job for this Client
MAX(RELATED(["Jobs"."Start Date"]))

// Returns the total "Amount" for all Jobs for this Client
SUM(RELATED(["Jobs"."Amount"]))

Suppose we have an "Invoice" with "Invoice Items" and the Invoice Items have
a field for "Net Amount".

On the Invoice we can add a field that adds up the Net Amount of the
Invoice Items for that Invoice:

SUM(RELATED(["Invoice Items"."Net Amount"]))

On the Client we can add add a field that adds up the Net Amount of the
Invoices for that Client:

SUM(RELATED(["Invoices"."Net Amount"]))

The chaining works because the Items have a parent Invoice and the
Invoices have a parent Client.

In this case, we add a new Content Type "Invoice Dispute". The
Invoice Dispute has a field for "Disputed Invoice" which links
to the Invoice being disputed.

Note: The chaining doesn't work as before, because the Content Types
are linked in a different way. In this case, the Items have a parent
Invoice but the Invoices don't have a parent Invoice Dispute. Instead,
they link the other way, the Invoice Dispute is linked to a parent Invoice.

But, we can still display the total "Net Amount" on the Invoice Dispute:

SUM(RELATED(["Invoice Items"."Net Amount"], ["Disputed Invoice"]))

This sums up the Net Amount for the Invoice Items on the specific Invoice
selected in the Disputed Invoice field.

The second argument must be a Content Field that links to the correct
parent Content Type for this to work.

Sorting and Filtering

There are currently no options for sorting and filtering the related content to use in calculated fields. We hope to introduce these options in a future release.

Built in Fields

The following fields always exist and can be referenced whether or not you have added them as actual Content Fields:

"ID"
"Created At"
"Updated At"
"Author"
"Group"