Custom Tables

SAP CPQ allows the use of data from non-standard tables during the configuration process. Adding, editing and deleting tables and their entries is done through Custom Tables.

customtables.jpg

Figure A - Custom Tables


Custom Tables allows you to:

  • Define New – Create a new table by defining table and columns names
  • Import New – Create a new table by importing an excel file
  • Edit Table – Add new columns to an existing table
  • Delete Table - Delete an existing table.
  • Export Table Template - Export an empty excel file with column names, so data records could be added and imported (intended for initial import or appending).
  • Export Table - Export an entire table.
  • View Entries – Read through table entries, delete them, insert new and modify existing.
  • Audit Trails - See when table was modified and who modified it.

Export table template result example is shown below:

Figure B - Exported Table Template

Define New Table

Figure C - Define New Table


The first step is to define custom table name. Table name is required.

Table name must not begin with sys_

As soon as custom table name is defined, the next step is to define columns. Column name is required fields. Administrator can also define type of each column. The following types are supported:

  • bit
  • int
  • date
  • datetime
  • nvarchar(size) [default(250)]
  • decimal(decimal places) [default(2)]

Length/Decimal places column type is valid only for NVARCHAR, in which case it represents the length of the string sequence, and the Decimal, in which case it represents decimal places.

By selecting the Personally Identifiable Information checkbox an administrator flags information as PII

PII data can be flagged as sensitive via the Sensitive data checkbox. Sensitive data has an additional layer of protection.

When user creates table via API she can provide type for each column within attribute <DbType></DbType>.

When a new table is created, primary auto increment key CpqTableEntryId is added to the table, which is later used for manipulating entries. When Add New Column is clicked, the system will add a new blank column. Clicking Undo Column removes the last created column.

Import New Table

Figure D - Import New Table


A new table can be defined by importing it. When a new table is created, the primary auto increment key CpqTableEntryId is added to the table. If a table name is not provided, the table name will be populated with the excel file name. Table Name must be unique, needs to contain only alphanumerics and cannot start with a numeric. Columns in the db table will be named after columns from the excel file, therefore the column names must be unique and may contain only alphanumerics and cannot start with a numeric.
Excel file example is shown in the figure below:

Figure E - Import Table Example Sheet


It is highly recommended that excel file contains only one sheet, but if there are more system will only consider the active one.


When you click on save, please wait until the system informs you that import has finished, or about possible errors.

Edit Table


When admin is editing existing custom table, all column and types will be read-only and they can not be changed.

Figure F - Edit existing table


View Table Entries

viewtableentries.jpg

Figure F - View Table Entries


Here you can manipulate table entries:

  • Add New Entry
  • Edit Entry
  • Copy Entry – if you want to add a new entry similar as existing one, this functionality will open same page as edit white populated data, but after saving it will create new entry in dastabase table.
  • Delete Entry
  • Search Entries
  • Custom table export

Administrator is able to choose entries:

  • Entries listed above – Entries displayed on the screen will be exported
  • All entries that match entered criteria – Entries that match entered criteria will be exported
  • All entries – All entries will be exported

All entries is default selection. Administrator is able to choose export format. Supported file formats are:

  • Excel
  • Zip

Excel file is default selection. If admin chooses ZIP file, then xls file will be compressed to zip file.

  • Import
  • This is used for appending/updating data in existing table.
  • If you just want to insert new rows, the excel file should not contain a CpqTableEntryId column. But, if you want to update and insert new rows, CpqTableEntryId is necessary for existing entries, while for new rows this cell should be left blank.

Adding/Editing Table Entries

dodajnovientri.jpg

Figure G - Adding / Editing


This page is for adding or editing entries. When Save And New is clicked, the current data will be saved, and this page will be displayed again so new entry can be added to the same table. When editing existing entry, CpqTableEntryId will be displayed.

Audit Trails

Is ability for users to see when custom table was created/modified and who performed the action. Audit Trails are written every time user creates new table trough import, API or user interface and when user modifies the table by adding column, adding, editing or deleting table entry, importing Excel to existing table or when content of table is changed in any way trough API.

Hidden Custom Tables

Hidden custom tables are custom tables that are conditionally visible in SAP CPQ setup, depending on whether the tables have been marked as Hidden in Setup by admins. When admins create a new custom table (by clicking Define New), they decide whether to mark the tables as Hidden in Setup or not. The tables can be Hidden with values True and False. The default value is always False.

Many projects require storing data on custom tables that should not be shown in SAP CPQ setup. These kinds of tables are part of clients’ SAP CPQ model but SAP CPQ system admins are not responsible for them and, as a result, they should not be visible in SAP CPQ setup.

To view this property in setup, take a look at Figures H, I and J, respectively.

Input parameters for creating a new custom field over API call have been expanded to support this property. The IPython CreateTable method has also been expanded to support this property.

Parameter “Show hidden tables”

This parameter is located on the Custom table page in SAP CPQ setup (Figure K). When the value of this parameter has been set as “True” SAP CPQ will show hidden tables as well. The parameter's default value is “False.” The value of this parameter is saved only during the user's session. In other words, the user's selection is valid only until the user logs out or unselects this parameter.

Parameter “Edit hidden custom tables”

When hidden, custom tables are displayed in the configuration they look the same as other custom tables (Figure K). Admins can edit them, delete them or change their content.

Custom Tables Logging

The timestamp and the performer of actions performed on custom tables are logged for each individual row in the columns CpqTableEntryDateModified and CpqTableEntryModifiedBy. From the administrator side, the columns are visible in Setup > Product Catalog > Custom Tables > View Entries, and from the user side, they are visible in User Menu > Manage Tables > View Entries. The columns are automatically populated with the date and time the table was modified, and the username and the full name of the person that modified it. The action itself is not logged. The columns are populated after the following actions are performed:

  • add table
  • add row
  • edit row
  • copy row
  • import entries
  • import custom table
  • add column to the table
  • any actions affecting custom tables in Deploy/Send Changes

Moreover, the new columns are also populated when the following actions are performed via SOAP API:

  • CREATETABLE
  • DELETETABLE
  • EXPORTROWS
  • EXPORTTABLE
  • INSERTROWS
  • UPDATEROWS
  • UPSERTROWS
  • DELETEROWS

However, the following actions in custom tables are logged in Audit Trail > Admin Actions:

  • add new table (via SOAP API, Excel, IP script or manually; also logged in the new columns)
  • add column to the table (also logged in the new columns)
  • changing the PII and Sensitive Data flags
  • delete row
  • delete table.

More Information

You are here: SAP Sales Cloud CPQ Online HelpAdmin Page HelpProduct AdministrationCustom Tables