Import Calculated Candidate Field Associations Overview

Product: Workbench

Process

Candidate form calculated fields can be used to create and store formulas by using CFAs that run when users enter values in predefined fields. For example, calculated fields are useful in deriving the components of a candidate’s salary, such as Base Salary, House Rental Allowance, Travel Allowance, etc. This eliminates the need to have users complete a manual calculation, thus reducing the possibility for error.

In this example, the candidate’s Annual Salary is entered by the user and the option to calculate is selected. This triggers the CFA to run and complete the calculated values, based on the formulas loaded into Workbench. Red check marks signify that the calculation ran properly:

Successful calculations.
If the CFA runs and your calculated fields show a red x next to them, there is a potential issue that has stopped the calculation from running. Some reasons for the failure might be:
  • A value in the candidate form field that is used in the formula for the child fields is blank.
  • A candidate form field or a calculation field that is used in the formula for another calculation fields is selected to be hidden due to question branching/User type/language in Workbench.
  • A candidate form field that is used in the formula for the child fields is either inactivated or deleted.
  • A value of a candidate form field that is used in the formula for a child field has text string.
  • The system cannot render a value based on the formula that is imported for a child field.
  • A candidate form field or calculation field that is used in the formula for another calculation field is placed after this field on the candidate form.
  • If the value of a form field was not populated due to incorrect formula or a failed calculation, and if any other child field is calculated based on that value, then the other form field is also displayed as blank with a cross mark.

If a Calculation CFA is present on a candidate form, and that CFA is run, then the form automatically has a Recalculate button, allowing the user to “Recalculate” the values instead of having to reselect the parent option value.

The recalculate option.

Importing CFAs to create calculated fields is available for Workbench users with Power User access or for users that have Tier 5 access with the proper client setting enabled. The steps to successfully update CFAs by using a Workbench import are covered in this documentation.

As with all configuration and import tasks in Workbench, it is recommended that customers first complete the work in Staging before making any updates in Production. This helps identify and resolve any potential issues before completing the task in your Production environment.

Preparing the Spreadsheet
  1. Export the CFA by selecting Tools > Forms > Candidate Forms. Select Administer Form Fields for the candidate form that houses the CFA to upload. Select Export Field Association.
  2. Select Tools > Task Manager to retrieve your export and open the file. Save it to your computer as an Excel file type (.xls).
  3. Your import spreadsheet can only have two tabs: Properties & Associations. Do NOT change the name of the tabs
  4. You do not need to make any changes to the Properties tab unless you are creating a new association. If so, change the Parent Question cell to include the database Field Name of the parent question.

If you are creating a brand new CFA, you might create a new spreadsheet by following the sample Calculation CFA import spreadsheet format.

Properties Tab:

The properties tab

Associations Tab: (Do not include the first row in the final import):

The associations tab
CFA Calculation Rules
  • The Child fields can only be a type of numeric or text on the candidate form. The Child fields hold the end result/calculated value.
  • The Parent field must be a pull-down field (radio button or single-select). This Parent field initiates the calculation.
  • In the file, the formula must be entered in the “Child Text” column in the following format: # #.
  • Either numeric values or database field names can be used to construct the formula.
  • The database field name must match exactly to the database field name in Workbench. (Capitalization does matter.).
  • The database field name needs to be enclosed in @ symbols. For example: #[@DBFieldName@*10/100]#.
  • The following arithmetical operators are permitted in the formula: “+”, “-“, “*”, “/”, and “%”.
  • The following comparative operators are also permitted: Min, Max, and Between. These are used in a formula to validate the value entered in a child field.
  • The Min Operator checks if the child field value is greater than or equal to the value given in the formula. The syntax for a formula by using the “Min” operator needs to be as follows: #[Min(@DBFieldName1@)]# or #[Min(<value>)]#.
  • The Max Operator checks if the child field value is less than or equal to the value given in the formula. The syntax for a formula by using the “Max” operator needs to be as follows: #[Max(@DBFieldName1@)]# or #[Max(<value>)]#.
  • The Between Operator checks if the child field value falls within the range of values that are provided in the formula, inclusive of the lower, and upper limit values. The syntax for a formula by using the “Between” operator needs to be as follows: #[Between(@DBFieldName1@,@DBFieldName2@)]# or #[Between(<value1>, <value2>)]#.
  • These operators can also be used to calculate and restrict the output value in a child field at the same time. The syntax for such formulae needs to be as follows: #[Max(@DBFieldName@):1000+1400-1250]#.
  • Candidate form fields, against which the child field is validated, needs to be present on the candidate form in an active status otherwise the validation is unsuccessful.
  • Open parenthesis “(“ and closed parenthesis “)” are permitted to specify the order of operation.
  • Multiple candidate form fields can be used in a single formula. For example: #[@DBFieldName1@+@DBFieldName2*@DBFieldName3@]#
  • Candidate form fields that are used in the formula of a calculation field or calculation field that is used in the formula for another calculation field should be placed before this field to calculate and render the values successfully. For example, if the field name ‘Basic’ is used in the formula to calculate the value for the field ‘HRA’ on the ‘Offer Form’, then the field ‘Basic’ should be placed before the field ‘HRA’ on the Offer Form.
  • The values in the child fields of type numeric are rounded to the nearest integer and the values in the child fields of type text are rounded to two decimal points. Clients who require decimal values in the form fields need to configure text type fields for calculated fields.
Preparing the CFA Calculation spreadsheet for import
  1. The export includes both active and inactive parent and child options. Delete the rows for associations you are not updating.
  2. On the Associations tab, add a new column to the front of the spreadsheet (it is the new Column A). Name the new column Import Action. The only two acceptable values that are Update and Delete.
  3. For CFAs: The Child Type column is always Candidate.
Email WatsonTalentTraining@us.ibm.com if you have a suggestion for improvement, or encounter an issue on this documentation. Include the product and page title in your email.