Reference Aha! fields in custom worksheet equations

A custom worksheet is a type of custom field that helps you gain powerful insights in the form of a two-column spreadsheet that appears on your Aha! records. You can create as many rows as you need and configure them to automatically calculate data based on custom equations.

You can add worksheet fields to your equations by selecting the field in the equation builder. But you may want your calculations to reference data that exists elsewhere in Aha! — like a feature’s due date or original estimate or a release’s total capacity. In this support article, we will walk you through how to reference Aha! data in worksheet equations to help you answer questions like:

  • What is the total cost of this initiative based on the cost to develop its features?
  • How much of the workload in this release contributed to new features?
  • Using a variety of measurements like idea votes and Aha! score, what is the actual value of this feature?

Note: Custom worksheets are exclusively available on Enterprise+ plans.

Syntax for referencing Aha! fields in worksheet equations

To reference Aha! fields in worksheet equations, you use a custom syntax that represents the field you want to reference.

In this example, the worksheet is comparing a feature's due date with the release's release date and returning a status that will automatically update as those dates change.

Aha-advanced-equations-reference-dates.png

 

To accomplish this in a worksheet equation, a custom syntax is used to refer to the feature’s Due on field and the release’s Release date field. For the example above, the equation looks like this (see Value column).

Aha-custom-worksheet-field.png

The equations use custom syntax to give the worksheet directions to the field(s) that you want to access. Those directions are always relative to the location of the worksheet. Let's break down the equation.

Row 1’s equation is self.due_date.

  • Every field reference starts with "self." This is simply a declaration that means, “Starting from this record.”
  • Since the worksheet is on a feature, we now add the identifier for the field we want to reference.
  • The identifier for a feature’s due date is due_date.
  • self.due_date can also be read as “Starting from this record (feature), reference the Due date field on the record.”

Row 2’s equation is self.release.release_date.

  • This worksheet is located on a feature, but now we want to reference a field on the feature’s release.
  • The identifier for a release’s release date field is release_date. The syntax self.release_date wouldn’t work here because release_date is not a feature field.
  • To look at the feature's release, we will include release before release_date to indicate what field we’re referencing relative to the record we’re on.
  • self.release.release_date can also be read as “Starting from this feature, go to the feature’s release, then reference the Release date field.”
Worksheet field      
Feature complete self. due_date  
  Starting at the feature... ...return the feature's Due date field.  
Release date self. release. release_date
  Starting at the feature... ...go to the feature's release... ...and return the feature's release's Release date field.

Here are additional rules to be aware of when referencing fields:

  • Every field value starts with "self."
  • If you are referencing a field on the record, you must add the identifier for the field name after self. A complete list of fields for each record type can be found here.
    • For example: self.score
  • Field references are case sensitive. 
  • To reference a field on a linked record, add the linked record type along with the field. 
    • For example: self.release.release_date
  • You can only reference fields that are on the record or linked to the record where the worksheet resides.
  • If you are referencing a field which includes more than one value (an array), then your field name will need to be followed by open and closed brackets. If you’re unsure about whether or not brackets are required, please review the field identifier list here. Without a function, arrays will return in a comma-separated list. Arrays can be combined with functions to calculate the value on the arrays.
    • For example:
      • self.requirements[].name → this returns requirement names for all requirements on the feature
      • count(self.requirements[]) → returns the total count of requirements on the feature.
  • If you want results in an array to only include specific values, you can filter results based on certain criteria. You can read more about how to filter results here.
    • For example: count(self.requirements[self.status == “Shipped”]) returns the total number of requirements that are in the “Shipped” status.
  • If you are referencing a custom field, you must include a reference to the API Key that is defined on the Account > Custom field page.
    • For example: self.custom.API-KEY
  • To reference a specific scorecard metric or a custom worksheet value, include the name of the metric at the end of the syntax surrounded by curly brackets.
    • For example:
      • self.score{“Sales increase”} → this returns the value of the “Sales increase” metric in the record’s scorecard.
      • self.custom.API-KEY{“Name of your worksheet field”} → this returns the value of the worksheet field.

The complete list of fields that can be referenced for each record type can be found here.

Limited updates for some field types

When a field referenced in a worksheet equation is updated, the changes will be reflected automatically. However, there are some circumstances in which updates are not automatically reflected.

Field updates will not be automatically reflected in the following scenarios:

  • A change to a user's name or email will not be automatically reflected in equations referring to assignees or watchers.
  • A change to the name of a workflow status will not be automatically reflected in equations referring to status.
  • A change to the name of a workflow type will not be automatically reflected in equations referring to type.
  • A change to the name of a tag will not be automatically reflected in equations referring to tags.
  • A change to a custom field value on a related record will not be automatically reflected in equations. 

These values can be manually updated by clicking the Refresh button when hovering over a worksheet.

Examples of equations that reference field data

Example 1: Calculate a feature value from total votes and score

In this example, a feature worksheet is calculating a total prioritization score by multiplying the number of votes received for the feature’s idea with the Aha! score.

Aha-total-prioritization-score.png

  1. Navigate to Account > Custom fields.
  2. Select the Features tab and click Add custom field.
  3. Choose Worksheet and click Next.
  4. Add a unique Name for your custom field.
  5. Worksheet section
    • Row 1
      • In the Field name column, enter “Aha! score” — it’s important to choose a unique value here if you want to reference this field in another calculation at a later time.
      • In the Value column, begin typing to launch the Equation builder. Add the following syntax to reference your feature’s Aha! score: self.score
    • Row 2
      • In the Field name column, enter “Ideas votes”.
      • In the Value column, begin typing to launch the Equation builder. Add the following formula to add the total ideas votes: sum(self.ideas[].votes).
    • Row 3
      • In the Field name column, enter “Total score”.
      • In the Value column, begin typing to launch the Equation builder. Add the following formula to add the total ideas votes: field("Aha! score")*field("Ideas votes")
  6. Add the custom field to your layout. If you are already using feature layouts, select the Use in layouts radio button and then select your layout. If you don’t yet have a field layout, select the Use in products option to create a new layout for the features in your product.

Aha-worksheet-field-names-and-values.png

Example 2: Roll up feature costs to your initiatives

In this example, an initiatives worksheet is calculating the total cost of feature development and presenting it on the initiative.

Advanced-custom-worksheet-fields-field-references.png

This example assumes that feature cost is being captured via a custom field on features. The custom field’s API key is feature_cost.

    1. Navigate to Account > Custom fields.
    2. Select the Initiatives tab and click Add custom field.
    3. Choose Worksheet and click Next.
    4. Add a unique Name for your custom field.
    5. Worksheet section
      • Row 1
        • In the Field name column, enter “Cost”.
        • In the Value column, begin typing to launch the Equation builder. Add the following formula to roll up investment costs to your initiatives: sum(self.features[].custom.feature_cost)
      • Row 2
        • In the Field name column, enter “Revenue”. You can leave the Value column blank.
      • Row 3
        • Click Add row so that the worksheet has three rows.
        • In the Field name column, enter “Profit”.
        • In the Value column, add the following formula to calculate the difference between rows 1 and 2: field("Revenue") - field("Cost")
    6. Add the custom field to your layout. If you are already using initiative layouts, select the Use in layouts radio button and then select your layout. If you don’t yet have a field layout, select the Use in products option to create a new layout for the initiatives in your product.

Aha-field-name-value-profit.png

More equation examples

Use Example Equation
Reference a record’s standard field Feature worksheet referencing feature status self.status
Reference a record’s custom field

Feature worksheet referencing custom date field on feature

API key is “GTM”

self.custom.GTM
Reference a linked record’s standard field Feature worksheet referencing the release date on a feature’s release self.release.release_date
Reference a linked record’s custom field

Feature worksheet referencing a custom date field on the feature’s release

API key is “gtm_release_date”

self.release.custom.gtm_release_date
Reference a standard field on many linked records Initiative worksheet that sums all of its linked feature’s estimates sum(self.features[].original_estimate)
Reference a custom field on many linked records

Initiative worksheet that sums a custom number field for all of its linked features

API key is “feature_cost”.

sum(self.features[].custom.feature_cost)
Reference a worksheet field

Feature worksheet captures a feature’s “Total cost”. Worksheet API key is “feature_cost”.

Initiative worksheet configured to sum the “Total cost” for all its linked features.

sum(self.features[].custom.feature_cost{“Total cost”})
Reference a scorecard metric Feature worksheet referencing an individual scorecard metric named “Sales increase”. self.score{“Sales increase”}
Reference a custom scorecard metric

 Release worksheet referencing a custom scorecard metric on the release.

API key is “release_score”. Metric name is “Sales increase”.

 self.custom.release_score{“Sales increase”}

 

For any additional questions about using advanced equations with custom worksheets, please contact our customer success team at support@aha.io.


Was this article helpful?
0 out of 0 found this helpful