Lookup tables

Zilliant CPQ enables administrators to create and manage custom lookup tables. You can link these tables to extension points with custom Groovy scripts to adapt business processes or calculations, for example to calculate prices, add information in the configurator, or provide product and account list restrictions.

You can populate lookup tables manually, replicate them from ERP systems, or use information from a spreadsheet in Excel format. Additionally, you can use Lookup Table APIs to populate lookup tables with data from external systems.

Create a lookup table

1. From the application left navigation area, select Lookup, then select .

2. Enter values or make selections to add the lookup table:

   • ERP Id—(Optional.) ERP ID of the lookup table.

   • Name—Name of the lookup table.

   • Has Validity—(Optional.) Determines whether the lookup table is valid. If set to YES, additional fields validFrom and validTo appear.

   • Delta Price Table—(Optional.) Defines the lookup table as a delta price table. If selected, the PRICE_ITEM_TYPE and DELTA_PRICE fields appear.

     Note

     To avoid adding unnecessary columns to the lookup table, select this option only if needed.

3. Select Create.

4. Add columns to the lookup table.

5. Select to activate the lookup table.

Add a lookup table column

1. From the application left navigation area, select Lookup.

2. Select a lookup table.

3. If the lookup table is active, select to deactivate it.

   Note

   Before deactivating a lookup table, an administrator must delete all existing records in it.

4. Select .

5. Enter values or make selections to define the column:

   • Name—Lookup column name.
   • Sequence Id—Column's order number in the table.
   • Type—Column type. Available types are Currency, Data, Percent Record, Price Record, and Rate Record.
   • Unique Key—If you switch on the toggle, only unique values are allowed in this column. Users can't add more than one record with the same value for this column.

6. Select Create.

7. (Optional.) Further configure the column.

Configure a lookup table column

1. From the application left navigation area, select Lookup.

2. Select a lookup table.

3. If the lookup table is active, select to deactivate it.

   Note

   Before deactivating a lookup table, an administrator has to delete all existing records in it.

4. Select or add a column.

5. Enter values or make selections to configure the column:

   • Name—Lookup column name.
   • Column Id—Column's order number in the table.
   • Record Type—Column type. Available types are Currency, Data, Fetched, Price, Valid From and Valid To, Assert, Percent, and Rate.
   • Data Type—(Optional.) Data type for the column value.
   • Lookup Field Searchable—(Optional.) Determines whether this lookup column is used as a key or part of a combined key to search.
   • Lookup Field Unique Key—(Optional.) If you switch on the toggle, only unique values are allowed in this column. Users can't add more than one record with the same value for this column.
   • Currency Field—(Optional.) Use for lookup table synchronization with the ERP system.
   • ERP Id—(Optional.) ERP ID of the lookup column. Use for lookup table synchronization with the ERP system.
   • Business Type—(Optional.) Business type of the attribute. Supports lookup record data creation.
   • Attribute—(Optional.) The attribute name to search. Generally, this is the attribute that represents the last part of the search expression. This information helps support the lookup record data creation.
   • Search Expression—(Optional.) IMCExpression to retrieve data from the lookup table. Must be defined if the Lookup Field Searchable toggle is switched on.

6. Select Save.

Add price records in a lookup table

1. From the application left navigation area, select Lookup.

2. Select or create a lookup table. The lookup table must contain a column of the Price Record type.

3. If the lookup table is deactivated, select to activate it.

4. On the Records tab, select a record.

5. On the pricing tab, select . The name of the tab matches the name of the lookup table column of the Price Record type.

6. Enter values or make selections to define a price:

   • Amount—Enter a total price.
   • Currency—Select a currency.
   • Quantity—Enter a quantity of products.
   • Unit—Select a unit of measure.

7. Select Create.

8. (Optional.) Select to define another price record.

   Note

   Each price record must have a unique combination of currency and unit of measure.

Configure user permissions to work with lookup tables

Configure user permissions to allow selected roles to read, edit, or delete lookup table records.

1. From the application left navigation area, select Lookup.
2. Select a lookup table from the list, then select Permissions.
3. Set the Restrict switch to YES.
4. For each section, assign a user role to allow viewing, editing, or deleting records in a lookup table.

Configure lookup table from API calls

API calls improve lookup table functionality, enable its smooth integration with external data sources, and enhance the process of data retrieval and data manipulation.

Use API endpoints to:

• Support create, read, update, and delete operations and batch operations to streamline data management processes in lookup tables.
• Have a structured and organized response data representation.
• Implement robust filter and search capabilities based on custom criteria.