Custom worksheet fields

A custom worksheet is a special type of custom field. Think of it like a spreadsheet. You can create as many rows as you need and configure them to calculate your data based on formulas and functions. Worksheet formulas can reference other cells in the worksheet and calculate them using a wide range of functions and operations.

This feature is only available for customers using the Enterprise+ plan. To create, modify, and delete custom worksheet fields, you must be an administrator with customization permissions, though owners and contributors can enter data in worksheet fields. 

Click any of the following links to skip ahead: 

features-board_feature-cost-mag.png

Create a custom worksheet field

Custom worksheet fields, just like our other custom fields, are added to layouts that are assigned to workspaces. To add a new custom worksheet field:

  1. Navigate to Settings > Account > Custom fields.
  2. Choose a record type where you would like your custom field to appear. (e.g. initiatives, releases, features, etc.)
  3. Click the Add custom field button and choose Worksheet.
  4. Add a Name for your worksheet.
  5. Click the + Add row button. (To remove a row, right click on a row and choose Delete row.) Worksheet rows consist of three columns: Type, Field name, and Value
    • Type determines how the value is formatted in the worksheet. Possible types are Text, Number, Percent, Currency, Date, or Boolean. Consider what kind of value you want to display and select the appropriate type.
    • Field name is simply a label for the value. However, it is important to note that this name will be used in value equations and reporting. Updating the name of this field will not update the equations that reference the field.
    • Value can be blank, predefined, or an equation. Blank values can be completed later when viewing the worksheet on a record. Predefined values cannot be edited on records, but they can be referenced in equations. Equations can reference any other worksheet field and present calculated values based on custom formulae. Advanced equations can reference fields on records linked to the custom worksheet's record using a special field identifier syntax.  
  6. The type of value for each row in your worksheet can be changed by clicking on the icons that appear in the Type column. See table below.
    Screen_Shot_2019-04-17_at_9.png
  7. To add an equation to your worksheet, begin typing in the Value column. This will launch the Equation builder. Add fields to your equation by clicking them. Include operators between your fields for simple calculations (addition +, subtraction - , multiplication * , or division / ). The equation builder also includes a number of more complex functions that can be used to calculate your field value. Hover over each function to see syntax and examples. Once you have written your equation, click Save.
    aha-settings-custom-worksheet-equation-builder.png
    Note: When you are searching for a field, function, or operator, you can select a result by moving up or down with the arrow keys. You can add your selection using the Return/Enter key.

    See our complete list of functions and complete list of fields support articles. Some advanced equation fields allow you to filter arrays with logical operators
  8. To add a fixed variable to your worksheet, type the variable (e.g. 400, 1/1/2019, TRUE, etc.) in the Value cell.
  9. To add a section heading to your worksheet, click Add heading and drag it to a location within your worksheet. You can name the heading by clicking the section heading text.
  10. Once you have created your worksheet, you can choose to add it to a product or use it in an existing layout.
    Note: Custom worksheets must belong to at least one custom field layout to be accessible by other users.

The six data types supported by custom worksheets include:

Value format Formatting options
String Text only
Number .0
.00
No decimal places
Show 1,000 separator
Percent .0
.00
No decimal places
Show 1000 separator
Currency U.S. dollar
Euro
Pound
Yen
Boolean True or False only
Date MM-DD-YYYY
DD-MM-YYYY
YYYY-MM-DD
MM-YYYY

Note: Custom worksheets are supported by custom tables. To add a custom worksheet to a custom table, navigate to Settings > Account > Custom tables, select a custom table, then follow the steps above, starting at step 3.

Top

Use custom worksheet fields

Once you have created your custom worksheet field and added it to your layout, you can start adding data to your records. To add data, click into the worksheet row and begin adding values. The equation will execute in real time as you populate your worksheet.

Tip: You can import custom worksheet data from a CSV file. Each worksheet sub-field will appear as a custom field to be imported. 

Aha-custom-worksheets.gif

Hover over a calculated field to see the calculation. You cannot add data to fields that hold an equation.

Worksheets are greyed out until data is added to at least one field on the worksheet. Any fixed variables that you may have added to your worksheet will carry a blank value until data has been added to the worksheet. If you change your equation after data has been calculated on a custom worksheet, the worksheet will recalculate with the updated equation.

Top

Report on custom worksheet fields

Aha-list-report-custom-worksheets__1_.png

Values that you have added to worksheets can be added to new or existing analytics reports. To report on custom worksheets with a new list report:

  1. Create a list report and then add custom worksheet fields by clicking the Add data button.
  2. Choose the record type where your worksheet(s) are and then click Add column.
  3. Your worksheet values will appear in the Custom fields section of the dropdown, prefixed with the name of your custom worksheet.

Top 


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