Adding calculated field formulas

You use formulas to return values for calculated custom fields. Formulas can use input values from numeric, text, date/time, and check box fields and values mapped to list menu items.

Calculated fields use ECMAScript formulas to generate output values. If you are familiar with the ECMAScript language, you can create more complex formulas to calculate values. See Understanding calculated field formula syntax.

1. Click Edit in the Formula area when adding a calculated custom field.

The Edit Formula dialog box opens.

2. Use the formula toolbar to enter a formula.

  • Click Insert Field to insert a field value. Select the field and click OK.
Some fields cannot be used for calculated field input because the values can change even if items are not modified, such as Folders and Has Emails. Other field values may change when administrative changes occur, such as updating time tracking project options, but can still be used in calculated field formulas. See Fields affected by administrative changes.

If fields that can be affected by administrative changes are used in calculated custom field formulas, values are only recalculated when individual items are modified and saved. You can also create an escalation rule to schedule field recalculations. See Adding escalation rules.
  • Click Insert Mapped Field to insert a list field value. Select the field and click OK. Mapped fields use the input list mappings configured in step 3.
  • Click Insert Function to insert a function that performs more complex actions on an input field to return values. See Inserting functions in calculated field formulas. The last line of a formula must evaluate to a value of the same type as the calculated field output. For example, if you are adding a formula to calculate numeric output, the formula must return a numeric value. List item formulas must evaluate to a numeric value, which is mapped to a list item value configured in step 4 to generate the output.

Configure Input List Mapping settings to map list items to numeric or text values. Mapped values are used as the input to evaluate when calculating output values.

  • Click Add to insert a field to map values for. If you added a mapped field in the formula editor, the field is automatically added to the list.
  • Select a list item and enter the Value to map it to.
  • Enter an Unmapped items default to value to specify the default value for unmapped input items.
  • Select an input field and click Delete to delete it.

3. Configure Output List Item Mapping rules to map the calculated output to a list item value. These options are only available for calculated fields with list item outputs.

  • Click Add to add a new calculation rule. Select the Condition the rule must meet, enter the Calculation Result, and select a Mapped Item.
  • Select an Unmapped calculation results default to value to specify the default value for calculations not mapped to an output item.
  • Click Top, Move Up, Move Down, and Bottom to change the output mapping order. Rules evaluate from top to bottom and stop when a match is found.
  • Select an output mapping rule and click Delete to delete it.

4. Click Test to test the formula. See Testing calculated field formulas.

5. Click OK to save the formula.