Skip to main content

OrgChart Help Guide

Reference Table Example

Audience

Audience: Administrators

Overview

Admins can create Reference Tables using Excel spreadsheets in order to cross reference fields in custom formulas or while Planning.

The following article provides step-by-step instructions for creating a Reference Table and using that Reference Table to create a formula.

Formatting a Reference Table File

Reference Tables are created using Excel files. The following format is required in order to successfully create a Reference Table in OrgChart:

  • Reference Fields - One or more fields that exist in your OrgChart data and are associated with the Target Field value.

  • Target Field - Field that doesn't yet exist in your OrgChart data, and will be imported and assigned to records based on the Reference Fields.

To create a Reference Table containing conversion rates based on a Country Code, the file should resemble the screenshot below, in which the Country Code is the Reference Field and the Conversion Rate is the Target Field.

Country_Code_and_Conversino_Rate_Excel.png

Creating a Reference Table

Admins can create Reference Tables in the Chart Settings: Fields & Formulas panel. The following section provides step-by-step instruction for creating a Reference Table.

  1. Log in to OrgChart.

  2. Click on the 5_2_Mode_Small.png icon in the Top Toolbar, and then select the Setup option. The Setup panel is displayed.

  3. Click on the Chart Settings tile.

    Setup_ChartSettings_Select.png

  4. Select the Fields and Formulas option from the left side menu.

    5_2_1_CS_FieldsandFormulas_Arrow.png

  5. Click on the green f(x) Create button in the top left corner, and then select the Static Formula option. The Formula Editor panel is displayed.

    5_2_2_Fields_and_Formulas__Select_Static_Formula.png

  6. Enter a name for the Reference Table (i.e. Salary Normalization Table) in the Formula Name text box.

  7. Click on the No Values. Click here to add. text in the Operations column, and then select the Concatenate option.

    Salary_Normalization_Concat.png

  8. Click on the Type dropdown menu, and then select the Reference Table option.

    Select_Reference_Table_Option.png

  9. Click on the 5_2_1_gear.png icon to the right of the Value dropdown menu, and then click on the + Create Reference Table option.

  10. Enter the Reference Table name (i.e. Country Code Conversion Rates) in the Table Name text box.

  11. Click on the Browse option to open the File Manager.

  12. Open the Data folder (or the folder that you'd like to upload the Reference Table to), and then click on the 5_2_UploadData_FileManager.png option in the top right corner.

  13. Search for and select the Excel file containing your Reference Table data.

  14. Once the file is uploaded, click on in the File Manager, and then click on Select.

  15. Click on the Include Headers dropdown menu, and then select which fields from the Excel file you'd like to upload to your account. By default all fields are selected.

    Country_Code_Conversion_Rates_Two_Headers.png

  16. Click on the Target Header dropdown menu, and then select the field in the Excel file that will map to an existing data field. For example, if Conversion Rate does not yet exist in your OrgChart data, Conversion Rate is your Target header.

  17. Click on Save.

  18. Close the Edit Reference Tables panel.

  19. Click on the Value dropdown menu, and then select the Reference Table that you just created.

    Select_Just_Made_Ref_Table.png

  20. Click on Save, and then Refresh.

Creating a Formula with a Reference Table

Admins can create custom formulas using Reference Tables.

The following section provides step-by-step instructions for creating a salary normalization custom formula using an existing Reference Table.

This is helpful for normalizing salaries in global organizations that deal with different currencies.

  1. If it is not already open, open the Chart Settings: Fields & Formulas panel.

  2. Click on the green f(x) Create button in the top left corner, and then select the Static Formula option. The Formula Editor panel is displayed.

    5_2_2_Fields_and_Formulas__Select_Static_Formula.png

  3. Enter a name for the formula (i.e. Salary Normalization Formula).

  4. Click on the No Values. Click here to add. text in the Operations column, and then select the Concatenate option.

  5. Click on the Type dropdown menu, and then select the Field Lookup option.

  6. Click on the Value dropdown menu, and then select the field that contains employee salary (i.e Base Salary).

  7. Click on the Add_New_Formula_Rule_Icon.png icon in the Operations column, and then select the Concatenate option.

  8. Click on the Type dropdown menu, and then select the Field Lookup option.

    Salary_Norm_Formula_Concat_1.png

  9. Click on the Type dropdown menu, and then select the Reference Table that contains the conversion rate.

    Select_Salary_Normalization_Table_in_Formula.png

  10. Click on the Add_New_Formula_Rule_Icon.png icon in the Operations column, and then select the Multiply option.

    Salary_Norm_Formula_Multiply.png

  11. Click on Save, and then Refresh.

  12. The normalization formula is now ready to be used in Salary Rollup formulas, Box Styles, Profiles, or on chart Backgrounds.

    Salary_Normalization_Formula_Applied.png