Calculated Fields on Candidate Forms: Workbench CFA Import – Preparing the Spreadsheet

Answer

It is recommended that you always obtain the properly formatted spreadsheet prior to completing your Candidate Field Association (CFA) import in Workbench. To do this follow the below steps:

Candidate Field Association Export:
· Export the CFA: Tools > Forms > Candidate Forms > Administer Form Fields (for the candidate form that houses the CFA you will be uploading) > Export Field Association
· Go into Task Manager (Tools > Task Manager) to retrieve your export & open the file. Save it to your computer as an Excel file type (.xls)
· Your import spreadsheet can only have two tabs: Properties & Associations. Do NOT change the name of the tabs
· 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 may create a new spreadsheet by following the sample Calculation CFA import spreadsheet format below.
Properties Tab:


Associations Tab (Top grey row is instructional only – do not include in final import):




CFA Calculation Rules
· The Child field(s) may only be a type of numeric or text on the candidate form. (The Child fields will hold the end result/calculated value.)
· The Parent field must be a dropdown field (radio button or single-select). (This Parent field will initiate 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 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 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 provided in the formula, inclusive of the lower and upper limit values. The syntax for a formula using the “Between” operator needs to be as follows: #[Between(@DBFieldName1@,@DBFieldName2@)]# or #[Between(<value1>, <value2>)]#.
· These operators can also be used to calculate as well as 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 will be 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 used in the formula of a calculation field or calculation field used in the formula for another calculation field should be placed before this field in order 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 will be 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 will be your new Column A). Name the new column “Import Action”. The only two acceptable values that will be in this column will be “Update” and “Delete”.
3. For CFAs: The “Child Type” column will always be “Candidate”.


JUMP TO: