Getting started with formulas for calculated properties

Calculated properties are custom properties whose values are calculated from a formula. The formula can include references to other properties, thereby creating dependencies between your properties. Library administrators can create calculated properties for all property types and in all types of library.

Before you use this article, you should be familiar with the basics. Learn more: Calculated properties: the basics

This article explains how to implement simple use cases with code examples you can copy.  Use this article alongside the article Calculated properties: add entity references to your formulas to build simple formulas.

 

 Before you start
 Create a unique document sequence ID
           What is the sequence ID?
           Create a sequence ID for all documents in a document class
           Create a document sequence ID with a fixed prefix
           Create a document sequence ID with a prefix issued from a String property
           Create a document sequence ID with a prefix issued from a Category property
           Create a document sequence ID prefixed with the document creation year
 Use a date with a specific format
           Use a Date system property with the format YYYY/MM/DD
           Use a Date custom property with the format MM/DD/YYYY
 Calculate a "next review" date 
           Calculate a "next review" date as a fixed period after the "Application date"
           Calculate a "next review" date as "Review period" (number of months) after the "Application date"
 Calculate a duration between two dates
Use workflow states to calculate properties
Calculate the value of a category from another manually-filled category
 Use simple math calculations

Before you start

This article describes how to create some simple formulas for calculated properties. Learn more about how to create properties in the library administration:

You can use existing AODocs entities in your formulas. Learn more: Calculated properties: the basics.

A block of text formatted like this and containing the name of your property is inserted into your formula:

property`Document type`

This block of text is called an entity reference. If you copy the examples provided in this article, make sure you don't select the entity references. When you insert your own properties into your formulas, unique entity references are generated, containing the names of your properties. 


Create a unique document sequence ID

What is the sequence ID?

In AODocs, each version of a document is given a set of four numbers, composed of the following:

  • Library sequence ID
    The first library created in your domain has the library sequence ID 1, the second has 2, etc.
  • Document class sequence ID
    The first document class in a library has the document class sequence ID 1, the second has2, etc.
  • Document sequence ID
    The first document created in a document class has the document sequence ID 1, the second has 2, etc.
  • Version sequence ID
    The first version of a document has the version sequence ID 1, the second version has 2, etc.

For example, the full sequence ID 34, 3, 897, 2 corresponds to the 2nd version of the 897th document of the 3rd class of the 34th library in the domain.

Important:
– Full sequence IDs are unique per document version within a domain. 
– Sequence IDs are generated sequentially. However, if an error occurs, a given number may be skipped.
– Sequence IDs are generated when you create your document class, document, or version. The constantly increasing sequence IDs don't take account of any classes, documents or versions you permanently delete.  

Sequence IDs are useful because they allow you to generate unique IDs that you can use to reference your documents. For example, you can save the document sequence ID in a calculated property. This gives you automatically generated and easily accessible document references.

You can use library, document class and document sequence IDs in formulas for calculated properties.

Create a sequence ID for all documents in a document class

Use case: generate a unique ID per document within a document class.

1. Create a new String type calculated property. Name it "Document ID", for example.

2. In the lower left panel of the formula builder, press Sequence ID. The list of sequence IDs is displayed in the right panel. Press the Add button next to Document sequence ID. A simplified entity reference is inserted into the body of the formula:

sequenceId`document`

Optional:

If you want a document sequence ID with a specific number of digits, add .padStart(5,'0') to the end of your formula:

sequenceId`document`.padStart(5,'0')

where 5 is the number of digits you want.

Create a document sequence ID with a fixed prefix

Use case: Give the prefix "SOP" to all the document sequence IDs of your "Standard Operating Procedure" document class.

1. Create a new String type calculated property. Name it "Document ID", for example.

2. Add this to the formula:

'SOP'+' - '+

3. In the lower left panel of the formula builder, press Sequence ID. The list of sequence IDs is displayed in the right panel. Press the Add button next to Document sequence ID. A simplified entity reference is inserted into the body of the formula:

'SOP'+' - '+sequenceId`document`

Optional:

If you want the number part of the document sequence ID to have a specific number of digits, add .padStart(5,'0') to the end of your formula:

'SOP'+' - '+sequenceId`document`.padStart(5,'0')

where 5 is the number of digits you want.

Create a document sequence ID with a prefix issued from a String property

Use case: Make all the document sequence IDs of your "Project" document class start with the value of the "Project name" property (type String).

Example of the final formula:

property`Project name`+' - '+sequenceId`document`

1. Create a new String type calculated property. Name it "Document ID", for example.

2. In the lower left panel of the formula builder, open the Properties list under From document. Select String / Text in the list. Then press Add next to the required String property in the right panel (in our example, "Project name"). A simplified entity reference like this is inserted into the body of the formula:

property`Project name`

2. Add this to the formula:

+' - '+

3. In the lower left panel of the formula builder, press Sequence ID. The list of sequence IDs is displayed in the right panel. Press the Add button next to Document sequence ID. A simplified entity reference is inserted into the body of the formula:

property`Project name`+' - '+sequenceId`document`

Optional:

If you want the number part of the ID to have a specific number of digits, add .padStart(5,'0') to the end of your formula, where 5 is the number of digits you want.

Important: If there is no value in the String type property ("Project name" in our example), its entity reference is replaced with null in the calculated property value. You can write this formula to replace the entity reference by a value of your choice ("Default", in our example): 
(property`Project name` ? property`Project name` : 'Default') +' - '+sequenceId`document`
For example: 
Screenshot_2020-06-16_at_11.38.32.png

Create a document sequence ID with a prefix issued from a Category property

Use case: Make all the document sequence IDs of your "Document" document class start with the value of the "Document type" property (type Category).

Note: This formula only works with mono-value, single-level properties. Learn more: What are categories?

Example of the final formula:

property`Document type`.name +' - '+sequenceId`document`

1. Create a new String type calculated property. Name it "Document ID", for example.

2. In the lower left panel of the formula builder, open the Properties list under From document. Select Categories in the list. Then press Add next to the required category in the right panel (in our example, "Document type"). A simplified entity reference like this is inserted into the body of the formula:

property`Document type`

3. Add this to the formula:

.name +' - '+

4. In the lower left panel of the formula builder, press Sequence ID. The list of sequence IDs is displayed in the right panel. Press the Add button next to Document sequence ID. A simplified entity reference is inserted into the body of the formula:

+sequenceId`document`

Optional:

If you want all the sequence IDs of your "Document" document class to start with the short name of the value of the "Document type" property category, replace .name by .shortName (case sensitive). Final result:

property`Document type`.shortName +' - '+sequenceId`document`

If you want the number part of the document sequence ID to have a specific number of digits, add .padStart(5,'0') to the end of your formula:

property`Document type`.shortName +' - '+sequenceId`document`.padStart(5,'0')

where 5 is the number of digits you want.

Important: If there is no value in the Category property, an error occurs when the formula is calculated, preventing the document from being saved. Use this formula to replace the category's entity reference by a value of your choice ("No doc type" in our example):
((property`Document type` && property`Document type`.name)
? property`Document type`.name : 'No doc type')+" - "+sequenceId`document`

For example:
image02.png

Create a document sequence ID prefixed with the document creation year

1. Create a new String type calculated property. Name it "Document ID", for example.

2. Write the following formula:

new Date(systemProperty`creationDate`).getFullYear()+' - '+sequenceId`document`

Optional:

If you want the number part of the ID to have a specific number of digits, add .padStart(5,'0') to the end of your formula, where 5 is the number of digits you want.


Use a date with a specific format

Use a Date system property with the format YYYY/MM/DD

Use case: Use the document creation date in a calculated property with the following format: YYYY/MM/DD

Final formula:

(() => {
const d=new Date(systemProperty`creationDate`)
const yyyy=d.getFullYear()
const mm=`${d.getMonth()+1}`.padStart(2,'0')
const dd=`${d.getDate()}`.padStart(2,'0')
return `${yyyy}/${mm}/${dd}`
})()

Use a Date custom property with the format MM/DD/YYYY

Use case: Use the value of the "Publication date" property in your calculated property with the following format: MM/DD/YYYY

Example of the final formula:

(() => {
const d=new Date(property`Publication date`)
const yyyy=d.getFullYear()
const mm=`${d.getMonth()+1}`.padStart(2,'0')
const dd=`${d.getDate()}`.padStart(2,'0')
return `${mm}/${dd}/${yyyy}`
})() 

1. Create a new String type calculated property. Name it "Formatted publication date", for example.

2. Add this to the formula:

(() => {
const d=new Date(

3. In the lower left panel of the formula builder, open the Properties list under From document. Select Date / Time in the list. Then press Add next to the required Date property in the right panel (in our example, "Publication date"). A simplified entity reference like this is inserted into the body of the formula:

property`Publication date`

4. Write the rest of the formula:

)
const yyyy=d.getFullYear()
const mm=`${d.getMonth()+1}`.padStart(2,'0')
const dd=`${d.getDate()}`.padStart(2,'0')
return `${mm}/${dd}/${yyyy}`
})()


Calculate a "next review" date 

This formula lets you to generate a date in a calculated property from another date property to which a given number of months is added. 

Calculate a "next review" date as a fixed period after the "Application date"

Use case: Calculate a "next review" date, 12 months after the "Application date".

Example of the final formula:

(() => {
var date = new Date(property`Application date`);
date.setMonth(date.getMonth()+12);
return date;
})()

1. Create a new Date type calculated property. Name it "Next review date", for example.

2. Add this to the formula:

(() => {
var date = new Date(

3. In the lower left panel of the formula builder, open the Properties list under From document. Select Date / Time in the list. Then press Add next to the required Date property in the right panel (in our example, "Application date"). A simplified entity reference like this is inserted into the body of the formula:

property`Application date`

4. Write the rest of the formula:

);
date.setMonth(date.getMonth()+12);
return date;
})()

Calculate a "next review" date as "Review period" (number of months) after the "Application date"

Use case: Generate a "next review" date calculated from the number of months in the "Review period (in months)" property after the "Application date".

Example of the final formula:

(() => {
var date = new Date(property`Application date`);
date.setMonth(date.getMonth()+property`Review period`);
return date;
})()

1. Create a new Date type calculated property. Name it "Next review date", for example.

2. Add this to the formula:

(() => {
var date = new Date(

3. In the lower left panel of the formula builder, open the Properties list under From document. Select Date / Time in the list. Then press Add next to the required Date property in the right panel (in our example, "Application date"). A simplified entity reference like this is inserted into the body of the formula:

property`Application date`

4. Add this to the formula:

);
date.setMonth(date.getMonth()+

3. In the lower left panel of the formula builder, open the Properties list under From document. Select Number in the list. Then press Add next to the required Number property in the right panel (in our example, "Review period"). A simplified entity reference like this is inserted into the body of the formula:

property`Review period`

6. Write the rest of the formula:

);
return date;
})()


Calculate a duration between two dates

Use case: Calculate how many days between the "Start Date" and "End Date", defined in your document as Date type properties.

Note: The value calculated can be negative. 

Example of the final formula:

(() => {
var startDate = new Date(property`Start Date`);
var returnDate = new Date(property`End date`);
return (returnDate - startDate) / (1000 * 3600 * 24);
})()

1. Create a new Integer type calculated property. Name it "Duration (in days)", for example.

2. Add this to the formula:

(() => {
var startDate = new Date(

5. In the lower left panel of the formula builder, open the Properties list under From document. Select Date / Time in the list. Then press Add next to the required Date property in the right panel (in our example, "Start date"). A simplified entity reference like this is inserted into the body of the formula:

property`Start Date`

4. Add this to the formula:

);
var returnDate = new Date(

5. In the lower left panel of the formula builder, open the Properties list under From document. Select Date / Time in the list. Then press Add next to the required Date property in the right panel (in our example, "End date"). A simplified entity reference like this is inserted into the body of the formula:

property`End date`

6. Write the following at the end of the formula:

);
return (returnDate - startDate) / (1000 * 3600 * 24);
})()


Use workflow states to calculate properties

Use case: Record the date when your document is published. The date your document transitions to the “Published” workflow state is recorded in a calculated Date property called "Publication Date".

When you save your document, if its workflow state is:

  • “Published” – the system property "stateChangeDate" is updated with the current date (date of the transition to "Published"), and the value of this system property is recorded in your calculated property "Publication Date"
  • not “Published” – the property "Publication Date" is not recalculated

Note: The property "Publication Date" is empty if the document has never been published.

Example of the final formula:

systemProperty`stateID` === state`Published`
    ? new Date(new Date(systemProperty`stateChangeDate`).getFullYear(), new Date(systemProperty`stateChangeDate`).getMonth(), new Date(systemProperty`stateChangeDate`).getDate())
    : property`Publication Date`

1. Create a new Date type property. Name it "Publication Date", for example.

2. Add this to the formula:

systemProperty`stateID` ===

3. In the lower left panel of the formula builder, select Workflow states under From library. Then press Add next to the required workflow state in the right panel (in our example, "Published"). 

4. Add this to the formula:

? new Date(new Date(systemProperty`stateChangeDate`).getFullYear(), new Date(systemProperty`stateChangeDate`).getMonth(), new Date(systemProperty`stateChangeDate`).getDate()) :

5. In the lower left panel of the formula builder, open the Properties list under From document. Select Date in the list. Then press Add next to the required Date property in the right panel (in our example, "Publication"). A simplified entity reference like this is inserted into the body of the formula:

property`Publication Date`

Important: Make sure you use the exact name of the workflow state, not the name of the user action used to run the workflow transition to the workflow state. Learn more: Create workflow states.


Calculate the value of a category from another manually-filled category

You want to calculate the value of a Category property called "Language".

Use a simple mapping rule to determine the value of the calculated Category property, based on the value of another Category property called “Country“ that is manually filled in:

  • if "Country" is "Mexico" or "Argentina", the language should be "Spanish"
  • otherwise, the language should be "English"

Formula:

(property`Country`.id === '#{categoryValue:Rxe2uPo058jkVseG5c/Rxe4SdfwpuSgQFgdt2}'
|| property`Country`.id === '#{categoryValue:Rxe2uPo058jkVseG5c/Rxe4Ufi000001fHOrq}')
      ? '#{categoryValue:RzziEzj11GVIe7MURY/RzziI4o000001WM4ld}'
      : '#{categoryValue:RzziEzj11GVIe7MURY/RzziG5l000000trdlK}'

where:

  • '#{categoryValue:Rxe2uPo058jkVseG5c/Rxe4SdfwpuSgQFgdt2}' (on line 1) refers to the category value "Mexico" of the category defined at the library level and used in the "Country" property
  • '#{categoryValue:Rxe2uPo058jkVseG5c/Rxe4Ufi000001fHOrq}' (on line 2) refers to the category value "Argentina" of the category defined at the library level and used in the "Country" property

and

  • '#{categoryValue:RzziEzj11GVIe7MURY/RzziI4o000001WM4ld}' (on line 3) refers to the category value "Spanish" of the category defined at the library level and used in the "Language" property
  • '#{categoryValue:RzziEzj11GVIe7MURY/RzziG5l000000trdlK}' (on line 4) refers to the category value "English" of the category defined at the library level and used in the "Language" property

language.png


Use simple math calculations

Use cases:

  • Calculate the total price of a commercial proposal, for example:
    • number of days × daily rate
    • number of pieces × price per piece
  • Calculate the VAT with a fixed tax percentage
  • Calculate the VAT with a tax percentage written in another property

It's easy to write a math calculation for the formula of a calculated properties. You can use the standard operators: +, -, *, /

To use Number type properties (Integer or Decimal), press the property name in the left panel of the formula builder, under Integer or Decimal. When you do this, a simplified entity reference like this is inserted in the body of the formula:

property`Daily rate`

The formula of a sum between two properties looks like this: 

property`Duration of the service` + property`Travel time`

If you need to use fixed numbers, simply write them in the formula:

0.2 * (property`Cost price` - property`Reduction`)

Was this article helpful? 7 out of 7 found this helpful
If you didn’t find what you were looking for, don’t hesitate to leave a comment!
Have more questions? Submit a request

Comments

6 comments
  • Hello, I have one questions regarding the sequence ID :
    I created one document class for correspondence, created some documents inside to test and ajust the parameters, then deleted them.
    Then I implemented one calculated metadata to generate a sequence number. When I started to create new documents, the chrono started at 012, which i guess is because it's taking into account the old documents, even though I permanently deleted them.
    1- Is there a way to make the sequence start at 001 ?
    2- Moreover, is there a way to start the chrono at a chosen number ? (for example if documents with a sequence number were already existing prior to the creation of the aodocs site) ?

    2
    Comment actions Permalink
  • How can we get the value of Category sub level selected? If there are 3 levels to a Category value and in the sequence id the deepest 3rd level value needs to be appended, how that can be achieved?
    eg: Cat1->Cat2->Cat3
    Sequence id: 001+Cat3

    3
    Comment actions Permalink
  • I'm trying to get a value from a category field, but the reference (for example is doc.fields['#{field:SDtg925wMUXdhJvjZZ}') is returning the object and not the selected value when creating the document. How do I get the selected value from the category field in a formula?.

    3
    Comment actions Permalink
  • Hi Morgane,

    Many thanks for your question regarding the new Calculated Properties feature.

    You’re right: as described in this article (https://support.aodocs.com/hc/en-us/articles/360044323212#h_9750c9e1-4d1a-486f-886f-40da45ea2452), within a document class, a unique document sequence ID is created for each new document, and these IDs constantly increase over time, even if a previously created document gets deleted.
    So you can’t reset the document sequence ID. The only way to achieve this would be by creating a brand new document class; then the first document would have the ID #1. Similarly, you can’t set or manually change the document sequence ID for a given document class.

    Nevertheless, as a workaround, you can create a custom calculated property, which is equal to the document sequence ID, shifted by your offset.
    For example, to simulate a sequence reset, you can subtract the number of initially deleted documents (11 in your example), so this custom sequence number would start at 001 (note the use of “padStart(3, ‘0’)” to ensure the value is at least 3 characters long):
    (parseInt(sequenceId`document`) - 11).toString().padStart(3, '0')

    To answer your second point: to consider previously existing documents, you can simply increase the document sequence ID by your offset (for example, 123):
    (parseInt(sequenceId`document`) + 123).toString().padStart(3, '0')

    However, keep in mind that some indexes might be skipped in the case of errors, and this would lead to missing values in the sequence of values, as explained in the article.

    If you need additional help, feel free to contact the AODocs Support team by email at support@aodocs.com or open a ticket (https://support.aodocs.com/agent/dashboard). We’d be happy to look for solutions!

    0
    Comment actions Permalink
  • Hi Debashree,

    Many thanks for sharing your use case relating to the new Calculated Properties feature.

    To display the “name” element of the value picked from your Category (named “Location”, for example), use the syntax explained in this article (property`Location`.name): https://support.aodocs.com/hc/en-us/articles/360051793172#Cateogy_format.

    Additionally, the following formula checks whether a value has actually been defined for the Category (which we strongly advise, otherwise an error will occur), makes sure the value belongs to the third level thanks to the “level” property (starting at 0), and displays a custom message if not.
    Finally, you can prepend the document sequence ID using the “+” (concatenation) operator, and optionally the “padStart” method, ensuring the sequence number is at least 3 characters long.
    sequenceId`document`.toString().padStart(3, '0') + '+' +
    ((property`Location` && property`Location`.name)
    ? ((property`Location`.level + 1) == 3 ? property`Location`.name : 'No 3rd level location selected')
    : 'No location selected')

    You might find this example (https://support.aodocs.com/hc/en-us/articles/360044323212#h_3fdf95da-c6a1-4ef4-bab6-16849c286b31) interesting, since it describes how to add a piece of information to a document sequence ID, in the very specific case of a Category property.

    0
    Comment actions Permalink
  • Hi Miguel,

    Thank you so much for sharing your question related to the new Calculated Properties feature.

    You’re absolutely right: referencing the Category property (called “Location”, for example) in a calculated property will retrieve the whole (JavaScript) object:
    property`Location`

    Keep in mind that this single expression set for a calculated property will result in an error:
    “[...] unexpected returned value, only supporting primitive types”.

    The section Format of a Category object in our Knowledge Base (https://support.aodocs.com/hc/en-us/articles/360051793172#Cateogy_format) provides details about the structure of the Category object, and explains how to retrieve its elements, such as the “name”.
    Additionally, do take into account the caveat outlined in this example (https://support.aodocs.com/hc/en-us/articles/360051793172#Anchor_Example_missing_short_names) : we strongly recommend checking whether a value has actually been defined for the Category, and providing a custom message if it hasn’t:
    (property`Location` && property`Location`.name)
    ? property`Location`.name : 'No Location selected'

    0
    Comment actions Permalink

Please sign in to leave a comment.