You are here: Data Tables > Natural Keys and Data Table Imports

Natural Keys and Data Table Imports

OVERVIEW

CPQ Cloud has included Natural Keys within Data Tables to improve efficiency. As a result, the bulk import files that modify Data Table data now only require the table records that are to be changed.

Natural Keys can be a single column or a combination of several columns that produce a unique identifier for each record, or row. Natural Keys create a unique set of data (contained within rows) by selecting specific values (contained within columns) to identify that data.

The ability to do delta Data Table uploads means that only altered Data Table rows are updated, rather than requiring a user to delete and re-upload an entire table to make a small change. This reduces the maintenance downtime of Data Tables. Users no longer need to purge and reload all Data Table rows to edit a subset of rows.

Natural Keys are supported in: Data Tables, Web Services API, CSV Upload and Bulk Upload.

ADMINISTRATION

ClosedAdding Natural Keys

CPQ Cloud has included Natural Keys within Data Tables to improve efficiency. As a result of Natural Keys, the bulk upload files that modify Data Table data now only require the table records that are to be changed.

Natural Key can be a single column or a combination of several columns that produce a unique identifier for each record, or row. The creation of these Natural Keys reduces the maintenance downtime of Data Tables by including only Data Table changes upon upload. Users no longer need to purge and reload all Data Table rows to edit a subset of rows.

  1. Click the Schema tab.

  2. Check the checkboxes in the Key column to identify which Natural Keys will be unique identifiers.

  3. Ensure that the added natural keys are valid. Columns with valid Natural Keys look like this:

  4. Click Save when you are done.

ClosedUnderstanding Natural Keys and .CSV Import

For Data Tables with Natural Keys enabled, a column called _update_action is created in imported .CSV Data Tables. This column allows admins to delete Data Table rows.

    The Update column is only available on Data Tables enabled with Delta Data Table Uploads (Natural Keys).

Valid values for this column include:

ClosedTo create the _update_action column in your .CSV file:

  1. Create a .CSV file with your data headings, according to the instructions in the section "Preparing a .CSV file for Import" in Import and Export of Data Tables.
  2. Import the .CSV file as a Data Table into CPQ Cloud.
  3. Apply Natural Keys, according to the instructions above.
  4. Export the .CSV file.
  5. Open the .CSV file.

    By default, there is a new first column, _update_action. The column can be moved.

  6. As desired, change the value in this new column and import the .CSV file into CPQ Cloud.

    Depending on the value, the data in CPQ Cloud will be modified or deleted.


TROUBLESHOOTING

ClosedHandling Invalid Data

If a Natural Key is not unique, for example, if two or more rows contain identical value(s) in the column(s) designated as Natural Keys, the record is considered invalid. Invalid cell(s) will be highlighted in red.

There are two scenarios where invalid data cells would be highlighted in red: 


ClosedHandling Invalid Data & Imports

If you attempt to import duplicate records (where data with the same Natural Keys already exist in the Table) during an XML/CSV import, the duplicate records will be deleted and the new records will replace the pre-existing ones.

If your Data Table does not have Natural Keys, the duplicate data is added as a new row.

Use the logs to evaluate the success of your import. For more information, see Import Status and Error Logs.


NOTES

RELATED TOPICS

Related Topics Link IconSee Also