Tables

Custom tables are used to help define the structure of your model in Incentives. Custom tables are customizable.

Because you can add multiple primary key columns to a custom table, pick lists cannot pick from custom tables.

Custom tables can be created for multiple purposes. One common use of a custom table is to contain data for reference values that vary by one or more key members, or that exist in the form of a lookup. As a reference for compensation calculations, you can add a custom table to hold compensation rates that vary by product and by quarter, or multipliers that define benefits based on a percent of quota attainment. For example, a custom table might contain the format displayed in the following table.

In Incentives, create data tables to store transactional data, such as lists of sales or orders.

A data table contains a date field, but you can add other fields as needed. When periods are locked, all data tables that are associated with the locked calendar are locked by the Date field in the table (other table types are not locked). If more than one Date field exists in the data table, you must select a Lock by Date when the table is created to specify which field to use to lock the table.

Hierarchy tables contain relationships that are necessary to analyze and manage organizational data in Incentives.

After a model is created, the Account, Payee, and Time hierarchy tables are created automatically.

More hierarchy tables can be added after the model is created. Common hierarchy examples include product, customer, and territory hierarchies.

Hierarchies use parent-child relationships to organize structural information. In a parent-child hierarchy, two table columns define the hierarchical relationships among the data records. The primary key column uniquely identifies each record and a parent column identifies the parent of each record. Each parent can have many children, but each child can have only one parent.

There are two ways you can set up your hierarchy tables:

For example, you can manually add payee information to a hierarchy table and create the structure as you add records. Alternatively, you could source payee data from an external table and use the hierarchy table to create an organizational structure for your payees. No matter the approach, your hierarchy table may resemble the following example, where the Parent column contains the PayeeID for each payee's manager.

If we were to view this table in the hierarchy structure view, it would look something like this:

The following table describes the hierarchy tables that are added automatically when you create a model.

Structural tables help to define the structure of your model in Incentives. Structural tables are customizable.

Because you can add only one primary key column in a structural table, pick lists can pick from structural tables.

System tables are automatically added in Incentives. System tables include the Title, Salutation, Currency, Group Member, Country, Status, and PayeeEffectiveDatedInfo tables.

System tables are added to contain common attributes for fields in the Payee table. The Title, Salutation, and Payee Currency fields in the Payee table are populated with information from their respective system tables. Therefore, all possible titles, salutations, and currencies that are needed by the Payee table must be listed in the system tables. More fields can be added to system tables as needed.

In Incentives, you can use a view table to display results that are contained in two different sources.

Views do not contain their own unique data sets. Use a view to join tables together to create a view of two tables.

With views, joins can contain tables and the results of calculations. When you add calculations, you cannot use views as data sources. Instead, add columns to the original data source during the calculation creation process. Because views cannot be used as data sources, they also cannot be made into global tables. Views are not accessible from the Sales Portal.

When you add a view, you join two or more tables based on a point of commonality. For example, consider a situation where you join a Product table to a Payee table to display the products that were sold by each payee. If you restrict your results based on the payee ID, you receive the following results:

In Incentives, you can define the structure of tables using several different column types.

The following types of columns can be used to create your table schema.

The table schema for all tables is globally available throughout a model in Incentives, regardless of the component that contains the table.

You can add a table in a component that requires a column from another component. For example, your Payee table is in one component and you add a custom table in another component. Your custom table contains a payee ID column that pick lists from the Payee table. Because the table schema is universally accessible, tables can pick from other tables that are in different components.

In Incentives, a primary key ensures that each record in a table is unique, allowing each row to be distinctly identified. It is essential for building a reliable, efficient, and well-organized database schema and is vital for maintaining the database's structure and integrity.

Duplicate records with the same primary key combination are not allowed during import. If this occurs, the entire import batch is rejected for review and correction. For example, if the payee ID is your primary key, no two rows in the table can have the same payee ID. In some cases, tables can have multiple primary keys. If you create a custom table where both the payee ID and date columns are defined as primary keys, no two rows can have the same combination of payee ID and date.

In Varicent Incentives, add tables in the Composer module to contain data that is required for your model.

If you selected pick list as a field type in a table in Varicent Incentives, you must define the source to populate the pick list.

All linked fields are attached to a source table. Review the structure of all source tables before you exit.

In Varicent Incentives, you can add a data table that can be used to hold transactional data.

You can add a view table to display results from two different sources in Varicent Incentives.

In Varicent Incentives, you can edit the sources, detail columns, restrictions, and column names of a view table in the Composer module.

In Varicent Incentives, you can add a hierarchy table that can be used to structure your data into hierarchical relationships. This is useful when creating a organization chart showing manager to payee relationships, or when organizing territory data.

You can view your hierarchy data in a nested structure. This lets you visualize the parent and child relationships in your data. You can also add, edit, and delete rows in this view, as well as reparent child rows.

In Varicent Incentives, global tables are visible as source from any component in your model.

You can change an existing table that's already being used in connections into a global table. But, a table can't be made global if it has the same name as a connection, or if it's a view table.

In Incentives, you can view, add, or remove columns from a table in the Composer module.

In Varicent Incentives, you can delete a table from the Composer module as long as it doesn't have any dependencies.

In Varicent Incentives, you can duplicate tables in the Composer module, and rename them by adding a suffix.

You can view a list of all calculations, web forms, input forms, or other tables that use the selected table as a source in the Varicent Incentives model.

In Varicent Incentives, you can add a new column to your table.

You can change the default edit date that's displayed when you add a record to a table that contains a date field in Varicent Incentives. The default edit date can be either today's date or the first day of the fiscal year.

In Varicent Incentives, you can filter and delete multiple rows from a table at one time if the rows are not locked, and if the table is not a View table.

When you delete rows from a table or clear an entire table, all child table records that have pick listed columns that are linked to the table are also deleted.

When you try to delete rows that are pick listed by other tables, a warning message is displayed that identifies the tables whose records are deleted. You can view the dependent tables that are affected by clearing the table.

This action will be recorded on the Audit page, along with the start and finish time of the row deletion operation.

This operation deletes the selected rows in the table as well as all referencing rows from other tables. This action cannot be undone.

In Varicent Incentives, you can clear the data from calculations and data type tables that are associated with a period in a specific calendar. Data in data tables and calculations is removed up to and including the selected period.

You can purge periods on both data tables and calculations, or on calculations only. In data tables, rows with dates in the Locked by date field that are at or before the last date in the selected period are removed. In calculations, rows that have a partition at or before the selected period are removed. The periods must be locked before you purge calculation data or the purged records will be added the next time that the model is calculated. This action includes any locked data, but doesn't include data in history tables.

In Varicent Incentives, you can delete the entire contents of a table without deleting the table itself.

If you're clearing a large table, you must run a model optimize first.

You receive a warning message when you try to clear a table that contains a key column that is pick listed by other tables. You can choose to continue with the deletion or cancel the action. You can click the Details button to view the dependent tables that are affected by the table clearing. The warning message can be turned off in the Admin options.

To use the clearing tables feature, you must have view and edit permissions for the Composer module, as well as the component in which the table resides. If the table is in the top level of the Composer module, then you must have Top → Edit permission. You must also have view and edit permissions for the structure of the table that you want to clear.

Table clearing can be scheduled by using the Scheduler module.

Use the data transformation feature to find and replace data in any table in Varicent Incentives.

This procedure is useful when there is an error in source data that affects multiple rows of data in a table. Rather than manually adjusting each row, you can make the change simultaneously across multiple rows.

For each row where the Look in column cell value equals the Find value, the corresponding cell in the Make change in column contains the Change to value.

In Incentives, you can add effective dates to records in hierarchy, custom, and structural tables.

The effective start date is automatically added with the user-identified columns as part of the table's primary key.

An Effective start column and an Effective end column are automatically added to the table.

In Incentives, you can convert any hierarchy, custom, or structural table to a table with effective dates.

Two effective date columns, Effective_Start and Effective_End, are added to a table when it's converted. You can convert a table by using existing date columns as the effective start and effective end dates. The values in the start date column must be earlier than the values in the end date column. The process of converting a table to an effective dated table cannot be reversed. If a table pick lists from a parent table, the table must be converted before the parent table can be converted.

Tables can't be converted if they're used in the following ways:

The Audit page displays an event record that indicates that the table was successfully converted to a table with effective dates. If a column is removed during the conversion, an event is displayed on the Audit page.

In Varicent Incentives, you can view data in an effective dated table as of a defined date.

When you first open a table, the selected effective date is based on the Locked Through date in the calendar. The effective date defaults to the first calendar day after the latest Locked Through date. When you open a table and set a new effective date, the application saves the effective date to be used across all tables for your individual session.

Data that is valid on the selected date is displayed.

In Varicent Incentives, you can view all versions of a record in an effective dated table.

In Varicent Incentives, you can add multiple records that are the same but have different start and end dates. Incentives ensures that each version doesn't overlap with another version of the same record.

When a record is added, Incentives matches the start date of the record with the effective date that you selected. The application also checks to see if future versions of the same record exist. If so, the application displays those records for you.

If you click View All, all the records in the table are displayed.

In Varicent Incentives, you can select a date on which the record is no longer effective.

The effective end date is the date on which you want the record to no longer be effective.

The application refreshes the window and displays the record's effective end date.

In Varicent Incentives, you can remove one or more versions of a record in a table with effective dates.

If you remove a single version of a record, a gap in time for the record might occur. If a gap occurs, the effective dates of other records can be adjusted to fill the gap.

The History Viewer in Incentives allows you to view how a table, with history tracking enabled, has changed over time.

From the History Viewer, you can choose which date range you want to view your table's history for, or export the table's history.

You can delete the recorded history of a table in Incentives.

If you want to purge the history of a data table, you must specify a date range. Only items that fall in the data range selected are removed. Administrators must grant users permission to purge history.

In Incentives, you can clear the history for specified tables, including data from any locked periods.

In Varicent Incentives, you can define the table columns that are accessible and editable to specified users on Sales Portal.

In Varicent Incentives, you can restrict the table rows that are displayed on Sales Portal.

For example, you might want to make sure that payees can view and edit only their own information. In this case, you can restrict results to a subset of all data in a particular table.

In Varicent Incentives, if you enable direct editing for a table, users can make changes to the table through Sales Portal without administrator approval.

All users with web access to the selected table can edit column information without requiring approval. Typically, all edits that are made in the Sales Portal must be approved by an administrator before the change can be stored permanently in the database.

Enable direct editing with caution. If it is not enabled, administrators can approve each edit individually, or approve all edits at once.

In Varicent Incentives, after web users have submitted changes to tables on Sales Portal, administrators can approve or reject those changes through the Composer module. If the table name is in bold letters, edits must be reviewed by an administrator.

You must first enable access for the REST API and specific Composer objects. Currently, you can enable access only to data stores and tables.

The object is now available in Portal Access for assignment.

After enabling a table or data store for TS-API access in Composer, you must assign a Portal Access tree to the enabled object to allow web users access to the object.

For more information on creating Portal Access group and trees, see Portal Access Module.

As soon as a tree has been assigned to the table or data store, the object is accessible to web users associated with the Portal Access tree.

You can restrict the data in TS-API enabled tables so that web users can view only their own data in the table or data store.

The same View Restriction attribute that is used for Web Data Edit can be used to restrict data access for the REST API endpoints. If the View Restriction feature is enabled for a table, then when web users access the table, any rows not applying to the current web user are filtered out. The View Restriction feature is only available to Views if Web Viewable is also enabled.

In Varicent Incentives, you can define what tables and table fields are viewable and editable to users by adding an input form to the table in the Composer module.

After an input form is added in Varicent Incentives, the administrators with appropriate access can view the input form.

In Varicent Incentives, you can create validation rules for data submitted through input forms.

 Incentives administrators with appropriate access can add data or make changes to the specified fields in input forms. When a change is made through the input form, it is applied directly to the table.