Plan Design > Lookup Tables

For certain types of compensation plans, lookup tables can significantly reduce and simplify the rules, rate tables, and formulas that you need to define. You can use the Lookup Tables area to create, edit, and manage lookup tables, which you can then use in formulas to look up one or more Incent objects to derive amounts or rates in a plan.

You could use lookup tables for the following purposes, among others:

  • Determine a rate based on attributes such as product or geography
  • Determine an amount based on attributes such as order or a person
  • Return a yes or no result to determine eligibility based on attributes such as credit type or customer

Screen

Lookup Tables Screen

All Fields

The following table describes the information displayed when managing lookup tables:

Field Description
NameThe name of the lookup table.
Assignment TypeThe type of assignment, either Plan or Position.
Assignment NameThe name of the position or title assignment.
Person NameThe name of the person.

How to…

This section describes how to manage lookup tables.

Display Lookup Tables

Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.

  • To find specific lookup tables, type part of the name in the Search text box, and click the button. The search results appear.
    Click the  button to clear the Search text box and reset the search results.
  • To display a summary of a particular lookup table, click the name of the lookup table and click the Details tab, if necessary. The entry expands to display summary information. Click the lookup table name again to collapse the summary.
  • To display an audit summary, click the name of the lookup table and click the Audit tab. The last 15 changes appear in a table showing the modification date, the user who made the change, the associated action, and the previous and new values. Click the lookup table name again to collapse the summary.
  • To show or hide assignments in the search results, click the corresponding radio button in the Filters section.
  • Click the column headers to toggle between sorting the table names in ascending and descending order.
  • Use the page navigation buttons to navigate between the pages of lookup table results.

Create a Lookup Table

1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.

2. Click the  button. The Create Lookup Table screen appears.

3. In the Name field, type the name of the lookup table.
You can edit this name after the lookup table is created, as required, until you use the table in a formula. At that point, you cannot make changes to the name of the table.

4. (Optional) In the Description field, type a description of the lookup table.

5. In the Return Value Type field, choose the type of value returned by the lookup table using the drop-down list.

note: The type of value you choose determines how you can use the lookup table. You can choose from among the following options:

• Amount—The return value is a number, which you can use in formulas and commission rules.
• Rate—The return value is a number, which you can use in formulas, commission rules, and rate tables. To treat the rate as a percentage, divide the number by 100 in your formula.
• Rate Table Name—The return value is the name of a rate table.
• Formula Name—The return value is the name of a formula, which you can use in formulas and commission rules.
• Pay Curve Name—The return value is the name of a pay curve.
• Plan Signature Type—The return value is a DocuSign eSignature type.

The DocuSign eSignature return types are:

• DocuSign E-Signature
• DocuSign E-Signature with Access Code
• DocuSign WET Signature
• DocuSign WET Signature with Access Code

6. In the Define Result Dimensions field, specify the dimension to use in the lookup table.

For each dimension, select the dimension object, field, and type (value or range, depending on the field). Selecting value instructs the lookup table to perform an exact match; selecting range matches a range of values that you define.

If you select range as the type, you can optionally restrict the range by specifying to exclude the highest value in the range. For example, if you define a range from 1 to 10 and the item being evaluated has a value of 9 or 9.9, this is considered to be within the range. A value of 10, however, is not considered within the range.

Click the  button to add a dimension. Alternatively, click the  button to remove the corresponding dimension. You can define up to 10 dimensions that are evaluated to determine the return value.

As you add and remove dimensions, the corresponding fields appear in the Version section.

7. Upload the contents of the lookup table.

Do the following:

a. Click the  button to upload content. The Create Upload Template dialog appears.
b. Click the  button. Save the Microsoft Excel file to your local machine.
c. Enter the lookup table contents into the template file, and save the file.
d. Click the  button, click the  button, and navigate to the template file that you created in the previous step.

Select the upload parameters. You can choose to allow the creation of new rows, allow updates to existing rows, allow updates and new row creation, or allow deletes to all existing rows and create new rows.
f. Click the  button. Incent displays any errors encountered.

To update the lookup table contents, click the  button, and save the Microsoft Excel file to your local machine. Modify the lookup table contents in the file you downloaded, save the file, and upload the file.

8. Click the button_save_close2 button to save the lookup table and return to the search results.
Alternatively, click the button_save_add_assignment2 button to save the lookup table and add assignments. Click the Cancel button to dismiss the screen without creating the lookup table.
If you choose to add assignments, the Select Assignments dialog appears.

9. Select an assignment and click the  button. The assignment type and name appears under the Return Value Type field.

10. Click the button_save_close2 button to save the lookup table. Alternatively, click the Cancel button to dismiss the screen without creating the lookup table.

Add an Assignment to a Lookup Table

  1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.
  2. Hover over a lookup table in the list, and click the Add Assignment button that appears. The Select Assignments screen appears.
  3. Select an assignment and click the  button. The assignment type and name appears under the Return Value Type field.
  4. Click the button_save_close2 button to save the lookup table. Alternatively, click the Cancel button to dismiss the screen without adding the assignment to the lookup table.

Edit an Assignment

  1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.
  2. Make sure that the Show Assignments radio button is selected in the Filters section.
  3. Hover over a lookup table assignment in the list, and click the Edit button that appears. The Edit Lookup Table Assignment screen appears.
  4. Modify the lookup table, as needed, or add a new version.
  5. Click the button_save_close2 button to save the lookup table assignment. Alternatively, click the Cancel button to dismiss the screen without saving your changes.

Delete an Assignment

  1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.
  2. Hover over a lookup table assignment in the list, and click the Delete button that appears. A confirmation dialog appears.
  3. Click the button_ok4 button to delete the lookup table assignment.
    Alternatively, click the Cancel button to dismiss the dialog without deleting the lookup table assignment.

Edit a Lookup Table

1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.

2. Hover over a lookup table name in the list, and click the Edit button that appears. The Edit Lookup Table dialog appears.

3. Modify the fields, as required, or add a new version.

You can edit the name of the lookup table (in the Name field) only if the lookup table is not being used in a formula.

To show the existing versions, click the Show Version Details button.  When adding a new version, specify an effective start date and description for the version. You can also optionally delete an existing version.

4. To update the lookup table contents, click the  button, and save the Microsoft Excel file to your local machine. Modify the lookup table contents in the file you downloaded, save the file, and upload the file by clicking the  button.

5. Click the button_save_close2 button to save the lookup table and return to the search results.
Alternatively, click the button_save_add_assignment2 button to save the lookup table and add assignments. Click the Cancel button to dismiss the screen without creating the lookup table.

If you choose to add assignments, the Select Assignments dialog appears.

6. Select an assignment and click the  button. The assignment type and name appears under the Return Value Type field.

7. Click the button_save_close2 button to save the lookup table. Alternatively, click the Cancel button to dismiss the screen without creating the lookup table.

Copy a Lookup Table

1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.

2. Hover over a lookup table name in the list, and click the Copy button that appears. The Copy Lookup Table dialog appears.

3. In the Name field, modify the name of the copied lookup table.

4. Modify any other fields, as required.

To update the lookup table contents, click the  button, and save the Microsoft Excel file to your local machine. Modify the lookup table contents in the file you downloaded, save the file, and upload the file by clicking the  button.

5. Click the button_save_close2 button to save the lookup table and return to the search results.
Alternatively, click the button_save_add_assignment2 button to save the lookup table and add assignments. Click the Cancel button to dismiss the screen without creating the lookup table.

If you choose to add assignments, the Select Assignments dialog appears.

6. Select an assignment and click the  button. The assignment type and name appears under the Return Value Type field.

7. Click the button_save_close2 button to save the lookup table. Alternatively, click the Cancel button to dismiss the screen without creating the lookup table.

Delete a Lookup Table

1. Click the Plan Design tab, then click Lookup Tables in the secondary menu. The Lookup Tables screen appears.

2. Hover over a lookup table name in the list, and click the Delete button that appears. A confirmation dialog appears.

Note that you cannot delete a lookup table that has assignments. In this case, delete all assignments for the lookup table first.

3. Click the screen-shot-2016-11-30-at-1-09-22-pm button to delete the lookup table.
Alternatively, click the screen-shot-2016-11-30-at-1-01-49-pm button to dismiss the dialog without deleting the lookup table.