User Defined Tables allow you to load your own data into Pepperi for use in the Pepperi Rule Engine for calculations and validations.

Data in User Defined Tables is used in order to assign values to Custom Fields created in Pepperi.

This article will guide you on how to load User Defined Tables to Pepperi:

1. Define the table

2. Create a CSV file

3. Load the CSV file to Pepperi

4. View/edit the User Defined Table data

The User Defined Tables capability is part of the Pepperi Ultimate package. If it isn't enabled in your Pepperi Web Admin contact us.

First, some examples of User Defined Tables:

Ex. 1 Minimum order amounts for each customer - a table with the Account ID number and the "Value" column contains the minimum order amount.

Ex. 2 Bulk prices for items that vary by quantities ordered -  a table with the Item code, the quantity eligible for discount, and the price paid for ordering that quantity. The first column contains the Pepperi field Item Code and the "Value" column contains delimited value pairs - the delimiter used is the one indicated in your internal delimiter setting in the Pepperi Settings -> ERP Configuration -> Configuration.

Purchase 100 or more of Item1 and pay 5.99, 500 or more and pay 3.99

Purchase 500 or more of Item2 and pay 1.99, 1000 or more and pay 0.79, 2000 or more and pay 0.59

etc..

Ex. 3 Percentage of commission for Sales Reps - a table with  Sales Rep ID and the percentage of commission the sales rep should get.

Ex. 4 Discount based on customer type - this table indicates a discount based on customer type. It's efficient to load the discount using User Defined Tables because when a customer's discount needs to be updated just modify the discount once in the User Defined Table and it applies to all customers of that type, rather than updating an individual AccountDiscount field per customer in the Account data.

The table contains AccountType as the MainKey and the values are delimiter separated      Discount name;Discount Percentage

To load User Defined Tables:

1. Define the table

Go to Settings -> Configuration Files -> User Defined Tables

Click "+ User Defined Tables"

API Name will be the reference to the User Defined Table in the CSV file.

Main Key is a Pepperi field referenced in the table.

Secondary Key may also be used if there is a reference to another Pepperi field.

Memory Management

• None - UDTs with main key other than Account will be loaded to memory when opening the application and stay there for the duration of the time the app is running.

  Account specific data from UDT with main key = Account will be loaded to memory when entering the account (i.e. to the account dashboard or Order Center) and stay there for the duration of the time the app is running. When re-entering the account, the data will be fetched directly from memory.

• Volatile - Same as None except that when exiting the Account, the Account specific data will be removed from memory. When re-entering the account, the data will be loaded again to the memory.
  This is useful when your UDT data Primary key is Account ID, and the user is assigned to a large number of accounts and you want to avoid loading huge UDT tables to memory. 
• Dormant - UDT won’t get loaded to memory. Access is only via API via the disk, and not the memory cache. This makes it a bit slower than using memory, but reduces memory consumption. This is useful when your UDT Primary key is NOT Account ID, and you need to access only small amounts of data in this UDT. For example, if your Primary key is Item ID or "Any".  You should not access a dormant UDT from a line level rule because the access to this UDT is slower.

The fields available for the Main and Secondary keys are: Sales Rep ID, Account External ID, and Catalog Name.

In this example, the table loaded is used to evaluate a rule in the Pepperi Rule Engine about the total amount of an order.  Each store has a minimum order amount that should be enforced.

If the amount is less than the min order amount indicated in the table, then the order will be blocked.

If the order amount is greater than or equal to the min order amount, then the order may be submitted.

Account External ID is selected as the Main Key. There is no secondary key because an additional Pepperi field is not being used.

Click Save.

The list of User Defined Tables is presented. Select the table and click on "Export File". A CSV template called "mapdata.csv" for the file will be downloaded.

2. Create a CSV file using Excel/Notepad++ (download Notepad++ here )

Open the CSV file and fill in the data:

3. Load the CSV file to Pepperi

Go to Settings -> ERP Integration -> File upload and Logs

Click Upload. Select API Map Data from the menu and browse to the file.

Upload the file. Check the log to make sure the lines were loaded successfully.

If there are failed lines, download the Error log to find out why they failed (Accessing the Error Log).

4. View/edit the User Defined Table data

To view the data that was loaded click on the table name in Settings -> Configuration Files -> User Defined Tables.

Export the table to modify it by clicking "Export file". Tables can also be updated automatically by FTP.

5. Use the data in the table in a Custom Field. 

For example, create a decimal number field that will get the minimum order amount for the store's order from the table.

Select "UDT" as the type of Decimal Number field.

Indicate which table to query, and the Main and Secondary keys (in this case there is no secondary key). 

Get data from column number - indicate which position in the "Values" column of the table should be returned. The positions start from 0, then 1, then 2, etc.

In this example, there is only one value in the "Values" column, with no delimiter, so the column number is 0.

The value of this custom field will be the value retrieved from position 0. 

 In a table with delimited values:

You would create 2 custom field2 of type "Number" to retrieve the amounts and 2 of type "Decimal" to retrieve the prices. 

Amount Fields:

Amount1 : Position 0 = 100

Amount 2: Position 2 = 500

Price Fields:

Price1: Position 1 = 5.99

Price2: Position 3 = 3.99

Then another calculated "Decimal" field to check which price to return:

if (Unit Qty < Amount1) then return ItemUnitPriceAfterDiscount;

if (UnitQty > Amount1 && UnitQty < Amount 2) then return Price1;

if (UnitQty > Amount2) then return Price2;