Creating Custom Tables

Custom tables in CPQ systems are used to store and manage additional data that doesn't necessarily fit into the standard data structure of the system. Data in custom tables is accessed using table tags. Custom tables give you the flexibility to include extra information that's unique to your business needs. This data from non-standard tables created during the configuration process can be added, edited, and deleted via the Custom Tables menu found in Setup → Product Catalog → Custom Tables.

Custom tables allow users to perform the following actions:

• Define New: Create a new table by defining table and column names.
• Import New: Create a new table by importing an Excel file.
• Edit: Add new columns to an existing table
• Delete: Delete an existing table
• Export Template: Export and 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 (in the Audit Trails section): See when a table was modified and who modified it

Custom Table assists in maintaining products if an API is used or if values in the Excel spreadsheet are changed and uploaded again into SAP CPQ.

Records can be added or edited by exporting and importing table templates or table entries. We'll cover both methods in the next exercises.

There are three options for custom table exports:

• Export entries listed
• Export all entries that match entered criteria
• Export all entries

Importing table entries is used for appending/updating data in the existing table.

Custom tables are exported as an unzipped or zipped Excel file. The exported file includes the ID CpqTableEntryId when table contains some entries.

The system ID CpqTableEntryId is the primary auto increment key added to each record in the table. This system ID is used when updating data, such as editing a record and exporting and re-importing records. A CpqTableEntryId ID is necessary when updating data and not required for new entries. New entries don't need a column for this ID.

If there's a need to display table data in any part of CPQ, it's required to use table tags for this purpose. The Table Syntax Builder is a tool used to create table tags quickly and easily. It provides a user-friendly interface for selecting the required data and automatically generates the appropriate table syntax. These tags can be used in any part of SAP CPQ administration.

The Table Syntax Builder is found in the Formula Builder and allows you to perform the following: 

• Choose a table and column from the result that needs to be returned:

  • Define lookup type (exact first match, minimum, maximum, average, count) and order for sorting results.
  • Dynamically build conditions by selecting columns and operators.
  • For text data type, offered operators are : =, begins with, end with, matches.
  • For numbers and dates, offered operators are : =, >, >=, <, <=, Match Lower/Upper (inclusive/exclusive).
  • Test the expression to see if it parses correctly.

• The Syntax for the Table tag is <*TABLE(SQL Select Statement)*>.

• The custom table function is one of the most common and used SAP CPQ functionality that is implemented as part of other functionality. Some of them are: 

  • Autocomplete attribute
  • Container attribute
  • Quote Tables

Let's create a custom table in the next exercise.