Add calculated data to a custom form with the legacy form builder
In a custom form, you can add a calculated custom field that uses existing data to generate new data when the custom form is attached to an object.
To do this, you create a statement that uses data expressions and the names of existing fields, which can be custom fields, calculated custom data fields, and built-in Adobe Workfront fields.
This statement calculates the data you enter and displays the result in the new calculated custom field.
A calculated custom field can contain:
-
A simple reference to a single built-in field.
note info INFO Example: To calculate the revenue generated by projects and tasks, you could create a calculated custom field that contains the built-in field Actual Revenue. When someone attaches the custom form to a project or task, the revenue for the project or task displays in the field. -
An expression that references one or more fields. These can be custom fields, other calculated custom fields, and built-in fields.
note info INFO Example: To calculate the profit generated by projects and tasks, you could create a calculated custom field called Profit containing a mathematical expression that subtracts cost from revenue. To do this, you could use the mathematical expression SUB (subtract) with the built-in Workfront fields Actual Cost and Actual Revenue. In the steps below, you can see how this example can be carried out.
For information about creating custom forms for your organization and understanding the type of fields you can associate with them, see Create or edit a custom form.
Access requirements
You must have the following to perform the steps in this article:
*To find out what plan, license type, or access level configurations you have, contact your Workfront administrator. For more information about access requirements, see Access requirements in Workfront documentation.
Add a calculated field to a custom form add-a-calculated-field-to-a-custom-form
You can use both built-in Workfront fields and custom fields that you already created in the expression of a calculated custom field.
-
Begin creating or editing a custom form, as described in Create or edit a custom form.
-
On the Add a Field tab, click Calculated.
In the display area on the right, the field displays a fill-in value of 12345. This is an indicator to remind you that the field is a calculated custom field while you are creating or editing the custom form. When the form is attached to an object and users are filling it out, they see the result of the calculation in the field, and not the 12345 value.
-
Specify the following information for the calculated field:
table 0-row-2 1-row-2 2-row-2 3-row-2 layout-auto html-authored no-header Label Type a label for the field. This is what users see when they use the custom form. The field Name, which fills in automatically and matches the Label, is referenced by Workfront in reports. This is a required field. Name By default, the Name of a field is the same as the Label. However, you can modify the Name of a field to be different than the Label of a field. The field Name is referenced by Workfront in reports. This is a required field. Instructions Add text to provide additional information about the field and the formula in it.
You can also paste here the formula used in the calculation of the custom field. In this case, we recommend that you first update the Calculation of the custom field, and then copy the final expression from the Calculation field and paste it in the Instructions field.
This can be useful in the following ways:
-
As a reminder of what the formula is and how it works. This is especially helpful if you plan to use this calculated custom field on multiple forms.
-
As a tooltip users can see when they hover over the field. You can add any text here that you want them to see in the tooltip.
If you don't want users to see the formula in the tooltip, which might be confusing for them, do not add it to the Instructions field. Instead, use the "Display formula in instructions" setting to display or hide the formula, as it is further described in this article Build the calculation for your calculated custom field in this article.
For information about using the same calculated custom field on a new form, see Reuse an existing calculated custom field in a custom form.
Format The format in which you want the results of the field to be stored and displayed.
If you plan to use the field in mathematical calculations, always use a Number or a Currency format. When you select Number or Currency, the system automatically truncates numbers that start with 0.
IMPORTANT:
Before you choose a format, consider the correct format for the new field. The format field cannot be edited after the custom form is saved. And selecting the wrong format could impact future calculations and aggregate values in report and list groupings.
NOTE: Calculated fields with a Currency format should not include quotation marks. (For example, use 800.00 and not "800.00.") Using quotation marks can cause unexpected consequences due to nuances with language formatting for currency types.
-
-
Continue to update the custom field information, as described in the section Build the calculation for your calculated custom field in this article.
Build the calculation for your calculated custom field build-the-calculation-for-your-calculated-custom-field
-
Start creating the calculated custom field, as described in the section Add a calculated field to a custom form in this article.
-
Click Maximize to open the Calculation Editor and build your calculation.
note info INFO Example: Using the example in the introduction to this article, you could create a calculated custom field called Profit in a custom form for projects and tasks. This field could contain a calculation that displays the difference between Actual Revenue and Actual Cost: SUB({actualRevenue},{actualCost})In this example, SUBis the expression, and the referenced fields areactualRevenueandactualCost.A calculation usually starts with an expression, followed by parentheses containing the fields you want to reference when the custom form is attached to an object. For information about the expressions that are available, see Overview of calculated data expressions.
Each field must be surrounded by curly brackets, as explained in the section Syntax required in calculated custom fields in this article. When you start typing the name of a field, the system makes suggestions and you can select one to insert it into your calculation.
note note NOTE You cannot reference fields of the following types in a calculation: - Text Field with Formatting
- Descriptive Text.
For information about the custom field types, see Add a custom field to a custom form. -
Click in the large text box in the Calculation Editor box, then click either search or expand and click an option from the Expressions and Fields sections to the right of the text box. This adds them to the calculation.
You can also start typing an expression or field in the large text box, then select it when it displays. Each item displays with an “F” for field or an “E” for expression.
If you type an opening parenthesis, the closing parenthesis is added automatically.
note tip TIP You can do any of the following to get help with your calculation: - Hover over an expression in your calculation to see a description, an example showing how it can be used, and a “Learn More” link to more information in the article Overview of calculated data expressions.
- Use the color coding to identify the components you have added. Expressions display in blue and fields display in green.
- Find calculation errors, highlighted in pink, as you go. You can hover over a highlighted error to display a brief description of its cause.
- In the Preview on an existing object area below your calculation, start typing the name of a Workfront object, select it when it displays in the list. This gives you a preview of what the field will look like when the form is attached to the object.
- Reference expressions in a long calculation using the line numbers that display on the left.
- Hover over an expression in your calculation to see a description, an example showing how it can be used, and a “Learn More” link to more information in the article Overview of calculated data expressions.
-
Click Minimize when you are finished creating the calculation for the calculated custom field.
note note NOTE In the display area on the right, the field displays a fill-in value of 12345. This is an indicator to remind you that the field is a calculated custom field while you are creating or editing the custom form. When the form is attached to an object and users are filling it out, they see the result of the calculation in the field, and not the 12345 value. -
(Optional) Use any of the following options to further configure your calculated custom field:
table 0-row-2 1-row-2 2-row-2 layout-auto html-authored no-header Add Logic You can add Display Logic to determine whether the calculated field displays, based on at least one choice that a user makes in a preceding multiple choice field (Dropdown, Checkboxes, or Radio Buttons) when filling out the form. For more information, see Add display logic and skip logic to a custom form.
This is available only when at least one checkbox, radio button, or a drop-down field precedes the calculated custom field on the form.
Skip Logic is unavailable for calculated custom fields.
Update previous calculations When you are editing an existing calculated custom field, you can select this option to trigger an update in the calculation when you save the custom form. This happens only once when you save the custom form. The option returns to its disabled state after you do so. Display formula in instructions Leave this option enabled if you want the users who fill out the custom form to see the field's formula when they hover over the field. For more information, see the information about Instructions earlier in this table. -
Click Done when all changes are complete on the calculated custom field.
Or, click Apply to apply your changes to the form so far if you want to continue adding custom fields to the form.
Or, click Save + Close when all changes are complete on the custom form.
-
To verify that your calculated custom field works correctly, attach the custom form to an object, then review the result in the calculated custom field.
For instructions on attaching a custom form, see Add a custom form to an object.
If you want to continue building your custom form in other ways, you can continue on to one of the following articles:
Syntax required in calculated custom fields
Each field used in a custom calculated field must use the syntax explained below, with curly brackets around each field name. When you start typing the name of a field, the system makes suggestions and you can select one to insert it into your calculation. If you enter data in a calculation incorrectly, a warning messages alerts you. You cannot save the form unless you edit your calculation to contain valid fields and a valid calculated expression.
Surround field names with curly brackets
- If you want the calculation to reference a built-in field, the name of the field must be surrounded by curly brackets and it must be formatted as it appears in the Workfront database. You cannot use the name of the fields as they display in the Workfront interface.
For example: {actualRevenue}
Field names are case-sensitive and must appear in camel case format, as they appear in the Workfront system.
- If you want the calculation to reference a custom field, the name of the field must be surrounded by curly brackets, and preceded by
DE:within the brackets. Custom fields are case sensitive and must be formatted as they appear in the Workfront interface.
For example: {DE:Profit}
The system lists all of the custom fields you can choose from when you type DE:.
-
If you want the calculation to reference a field that will pull data from the parent object when the custom form is attached to an object, you must precede the field name with the object type of the parent object, also in curly brackets.
For example, if the custom form is configured to work with tasks, and you want the field to calculate the actual revenue of the parent object when the form is attached to a task, you need to indicate
projectas the object type of the field:{project}.{actualRevenue}Or, if it’s a custom field:
{project}.{DE:profit}If you’re not sure what the object type of the parent object will be because the custom form is configured for multiple object types, you can use the wildcard filter variable
$$OBJCODEto allow the calculation to work for each of the possible types. For more information, see Calculated custom fields in multi-object custom forms in this article.
Separate items with periods
When you reference a related object in a calculated custom field, you must separate object names and attributes with periods.
For example, in a task-type custom form, to display the name of the Portfolio Owner in a calculated custom field, you would type the following:
{project}.{porfolio}.{owner}
This system retrieves the information in the following steps (in this order):
- From the object of the custom form (a task), then
- Access the parent of the task, or another related object (project), then
- Access the parent, or another related object of the project (a portfolio), and then
- Access the next related object to the portfolio (the portfolio’s owner).
Name syntax for referencing a custom field
When you reference another custom field in a calculated custom field, you need to enter the name of the field as it displays in the Workfront interface.
For example, to reference the selected option in a custom field labeled Executive sponsor, you would type the following:
{DE:Executive sponsor}
:name at the end.{DE:Executive sponsor:name}Calculated custom fields in multi-object custom forms calculated-custom-fields-in-multi-object-custom-forms
In a multi-object custom form, the selected object types must be compatible with all fields referenced in the form’s calculated custom fields. If there is an incompatibility, a message alerts you to make adjustments.
{assignedTo}.{name}When this occurs, you can do one of the following:
- Remove one of the two incompatible items from the custom form—either the object type or the referenced calculated custom field.
- Keep both items and use the wildcard filter variable
$$OBJCODEas a condition in an IF expression to create two different versions of the In Charge field. This allows the field to function successfully, no matter which type of object the form is attached to.
$$OBJCODE as shown below to reference the Owner field when the custom form is attached to a project, and the Assigned To: Name field when the form is attached to a task:IF($$OBJCODE="PROJ",{owner}.{name},{assignedTo}.{name})For more information about variables like $$OBJCODE, see Wildcard filter variables overview.
Automatic updates of calculated custom fields
Calculated custom fields on an object recalculate automatically when the following things happen:
-
Something on the object changes, such as a daily timeline calculation.
-
Someone edits another field that is referenced by a calculated custom field on the object.
-
The calculated expression is empty and the field contains a value—this sets the value to null.
note note NOTE In a custom form attached to an object, date and time statements in calculated custom fields are calculated and saved according to the Coordinated Universal Time (UTC), not according to the time zone configurations set for your organization’s instance and your user profile. However, calculations in a custom form are displayed based on each user’s individual time zone.