Using formulas to calculate field values

Setting Up Formulas

Amazing Fields allows you to calculate the value of fields from other fields on the same Trello card.  Formulas are currently supported for Text, Number, and Checkbox field types.  Formulas are a supporter only feature so the person setting it up needs to be a supporter.  Once setup, the formula can be used by as many users and boards as you would like.

The image below shows an example formula.  

To setup a calculation:

• Open the Amazing Fields setting panel
• Select a field you want to compute using a formula
• Select "Calculate field value from formula" to enable the formula
• Start typing in the formula you would like
• Add references to card fields by clicking on "Insert Field"

Using Formulas

Once a field is set to be calculated, you can not edit it directly.  Amazing Fields will automatically update the calculation anytime you make a change to any other fields on the card.  This includes updates the user makes directly and updates made by things like linked custom fields.

If the formula fails when evaluated then Amazing Fields will popup an alert message with the details of the failure. The most common error is that the value being calculated doesn't make sense for the field type in use. Make sure to filter values and handle edge cases in your formulas.

Supported Functions

Amazing Fields supports over 100+ of the most common functions used in spreadsheets like Excel and Google sheets.  You can see a full list in the Supported Formula Functions reference.  If you see any additional ones you would like added, please let me know.

Default Field Values

Fields may be empty either when you create a new card or if you clear the field.  For example a numeric field like "cost" has no value on new cards.  This can cause problems for field calculations because the formulas expect all fields they use to have values.  As a result, all empty fields used in a formula will be calculated based upon a default value.  This does not actually set the field to the default value, it only adjusts the calculations behind the scenes.

The default values for each field type are:

• Number: 0.0
• Text: blank value
• Checkbox: false
• Date: 1/1/1970 00:00:00
• Dropdown: 
  • single select: '' (ie. empty string)
  • multi-select: []  (ie. empty array)

Clearing Field Values

There are some cases where you need to compute a value that "clears" the field to having no value.  To do this you should return the constant value NULL . This will tell the powerup to clear the field value.  See this example where the user has a checkbox that determines if the total price should be calculated and if it is not checked the value is cleared.

Card Specific Formulas

By default fields are shared across all cards and because of this they can only pull in data from the same card they are on. But what if you want to create a formula that references data from multiple other cards to compute it's value. That is when you need a "Single Card Field" so you can setup formulas that reference other cards directly.

The Single Card Field settings is in the "Card Display" options for the field. By changing it, you say that this field is directly connected to one specific card and should only show there.

Once you have setup a field setup as a single card field, you now have access for formulas to reference card specific fields and list fields.

Both options allow you to pull in data from other cards, but they allow much different use cases.

• Card Field: When you choose a card field, you are saying that you want to pull in the value of a specific field from a single specific card. For example maybe you have a card with the number of hours you work per week. If you want to use that on other cards you reference it as a card specific value and pull it from that one single card.
• List Field: When you choose a list field, you are choosing to pull in a "list" of values from a specific field for all cards that are currently in that list. Your formula can then do things like compute SUM, AVERAGE, MIN, MAX, etc across the entire list of values. An example where this is useful would be if you had a list of all your expenses and you want to have a card that displays the total of these expenses.

Using a combination of Field, Card Field, and List Field references you can create a wide variety of formulas. You can treat the cards and fields on your board similar to the way you would use a spreadsheet to connect up all the values and compute the outputs you need for your specific workflows.

Formula Details

Formulas are evaluated using the amazing Jexl library.  The libary allows formulas that are very similar to Excel and Google Sheets but also has a few differences and extra supported operations.  For full technical details, see the Jexl page.

Unary Operators

Example:  !CompletedField

Binary Operators

Example:  (4+5)*6

Comparisons

The  in  operator can be used to check for a substring: "Cad" in "Ron Cadillac" , and it can be used to check for an array element: "coarse" in ['fine', 'medium', 'coarse'] . 

Example:  NameField == "John Snow"

Ternary operator

Conditional expressions check to see if the first segment evaluates to a truthy value. If so, the consequent segment is evaluated. Otherwise, the alternate is. If the consequent section is missing, the test result itself will be used instead.

Value Types

Groups

Parentheses work just how you'd expect them to:

Constant Values

The following values may be referenced in a formula.

Card Context

There is beta support for accessing some card details as part of the formula.  All card values are accessed through the card property.

The values availabe are:

• card.address
• card.desc
• card.start
• card.due
• card.dueComplete
• card.id
• card.idShort
• card.name
• card.listName
• card.url

Note: when using these values be aware that visibility and value formulas are only re-evaluated if these values change and the current user has write access to the board. (ie. if they can modify the Amazing Fields values). The code should evaluate on all changes, but there may be some small cases that are missed if the user is making a lot of edits simultaneously.

Common Formula Usage

• Comparisons using equals (=) are not working

When comparing two values you must use double equals ( ==)instead of single equals (=).  See the list below for the full list of comparison operations.

• Using random values (RAND(), RANDBETWEEN()) updates too often

As described above, Amazing Fields updates all field formulas anytime the user edits any field on the card and when any custom field or other data causes a card to update.  When using with formulas involving random numbers this can cause some surprises because the value will keep changing.  Because of this I don't recommend using random values to determine a field output.  

• Formula value is not evaluating immediately after editing

Unlike a spreadsheet, Amazing Fields formula values are not evaluated immediately.  They are only evaluated when you make a change to a value on the card.  This means that when you are editing a formula to try something new, you have to make a change on the card to force it to evaluate and compute the new value.

• Checking for empty date values

As described above, the default value of an empty date field is 1/1/1970 00:00:00 .  This makes writing logic to check for empty dates a bit confusing.  I am currently looking for a better method to handle this, but for now the easiest way to check if you have an "empty" date is to check if the YEAR() returns a value > 1970.  In the example below I use that technique to compute a value 2 days after the Start Date but only if the Start Date has a value.

• Contatenating two text fields into a single field

This example shows how to combine the text fields for first name and last name into a combined full name.

• How to include values from multiple cards in a formula

This example shows a field that has been set as a "Single Card Field" on a Total Expenses card. Then a formula was added that adds the "Cost" field value from three different cards. ("Operations", "Overhead", "Project Summary). This value will automatically be updated anytime the input values are changed or cards are dragged between lists.

Anytime you want to setup a formula to use values from multiple cards on your board you can use a similar type of setup.

Reminder: if you don't see the "Card Field" and "List Field" options that let you add specific card values to a formula, you need to remember to setup the "Single Card Field" setting first. This is required if you want to reference data from other cards.

• How to computing the sum of a list of values

This example shows a field that has been set as a "Single Card Field" on a Project Summary card. Then a formula was added that sums the values of the "Cost" fields from all cards in the "Project List" list on the board. This value will automatically be updated anytime the input values are changed or cards are dragged between lists.

You can use a similar method anytime you want to compute a value from a list of fields in a Trello list. See the statistical functions for the wide variety of functions you can use in your calculations.