Local Tables

Tables store data in Epicor CPQ. They are also known as "Local Tables" to differentiate them from Database Tables.  They are a common source of data for both Option Filters and Queries in configurators, scenes, workflows, and other code.  

Understanding Table Storage Options

Tabular data (a grid of data, stored in columns and rows) can be stored in various ways in Epicor CPQ.   

• "Local" Tables 
  Tables are quick to create and edit under Resources > Tables in the admin interface. No database or SQL skills are required. They are easy to update: just edit each cell through the admin interface, or copy and paste thousands of rows from your favorite spreadsheet. Known as "local" tables, these are downloaded to your user's device when the configurator or scene loads, so they are in-memory as your customer uses the configurator and their response time is fast.  However, they should not be big (your user would be confused by the long loading time). They should not contain sensitive data.
  
  
• Database Tables
  Database tables require a separate tool to create and edit, and knowledge of SQL can be helpful. They require more effort to create or update, but accept different kinds of source data: you can upload CSV or Tab-delimited data into them.  These tables are not downloaded to your user's device: rather, they remain in the cloud and only the specific data required is sent when needed to your user's device.  In this way they can handle very large tables (millions of cells), and they can store sensitive information. Learn more about database table uses and benefits.
  
  
• "Local" Tables sourced from Database Tables
  If you have information coming from an integration stored in a database table, but it's not sensitive and you need it with fast response times, use this technique.  By sourcing a local table to a database table, it will automatically refresh when the database table changes. 

Use this comparison of the table storage options to see which is best for your needs.

Places to Use Table Data	Local Tables (no source)	Local Tables (database source)	Database Tables
Places to use Table data
I'm not sure where to start.  Where's the best place to begin storing tabular data?
Store choices to appear in option filters.
Store results for queries written in Snap used on the user's device, such as in a configurator or scene.
Store results for queries written in Snap used in the cloud, such as in a workflow rule, pricing rule, or safe function.
Store options for auto-complete fields (help your user find an item from a long list, such as a city or state for a shipping address)
Store cost or other sensitive data for pricing rules (calculate the Bill of Materials in your configurator)
Table Maintenance
Manually update the data by copy/pasting cells from Excel.
Manually update the data by uploading a file, such as a CSV, SQL, or tab-delimited datafile.
Automatically update the data using my company's integration platform from our ERP, manufacturing system, or other internet-connected database.
Types of Data
Store text, numbers, date/time values, colors, boolean values, or links to media.
Store sensitive or proprietary information (such as component cost or supplier), so a configurator can use the data, but it remains secure.
Store public information, such as part dimensions or descriptions, for fast retrieval so the configurator responds quickly.

This technique is possible, but may have slower performance or security concerns, depending on the data stored in the table.

Managing Local Tables

In the Admin interface, navigate to Resources > Tables. 

• Tocreate a new table, click the "+Add" button at the bottom of the window.
• Toedit an existing table, click the name of that table in the list.
• Todelete orclone a table, check the box next to the table/s you want to delete or clone, then click the appropriate button at the bottom of the window.

Creating or Editing Local Tables

To add data to your local table, you must first create the columns, giving each a name and a type of data to store.

• To add a column, click the "Add Column" button and specify the name and type. When naming your column, use only letters and numbers and don't start with a number.
• To edit an existing column in your table, right-click that column and choose "Edit Column" from the pop-up menu.
  

  When changing the datatype of any column:

  • Remember to change any code in safe functions or configurators which use the table. They will be expecting the old datatype of that column, and will usually report an error until fixed.
  • Remember to refresh your admin interface (press SHIFT-F9, or close and re-open that browser tab). The admin interface caches table structures to make your coding easier, and after a schema change (like changing the datatype) it may be out-of-date and guide you incorrectly.
• To reorder columns, first save your table.  Then, click a column header: you'll see the column highlight, and your cursor will turn into a hand.  Drag the column left or right and drop it into place.  If your re-ordering didn't give you the results you want, simply refresh your screen to restore the table to its last saved state, and try again.  The order of columns has no bearing on Snap code or performance: it is purely for your ease of use as an Administrator.

Column Types

You can add columns of the following types:

Column Type	Description
Text	Any text value
Number	Any numeric value
Boolean	True or False
Color	Color columns show a color selector in the cells, however the real backing value is a hex color. You can also just use a normal text column with color hex values when colors are needed.  Note that if a valid hex color or HTML color code is stored, the cell will display that color as a visual confirmation.
Date	Any date/time value.
Media	Reference an image from your Media folder. Media columns will show the image and a button to select the image from your media, however you can also use a normal text column and specify the images as relative paths.
Translated Text	Allows you to specify that this column is actually the translation of another column in a specific language.  This is useful for localizing option filters in configurators (see below).

How to copy data from Excel

To copy data from Excel, you must first add the columns with the appropriate data types to your table.  Then simply copy the data in excel and paste into the table using Ctrl + V or the right click menu.  All necessary rows to hold your data will be added.  Any extra rows longer than the data you paste remain untouched in the table.

After pasting information into the table from another tool (like an Excel table), click the "Refresh" button to have all your freshly-pasted data reviewed and confirmed by the table design.

Most operating systems or web browsers have a limit to how much the clipboard can hold. After pasting thousands of rows, confirm that both your first row and last row appear.  If data is truncated in your paste, then simply paste in smaller batches.

Localizing Tables

Table columns can represent another column translated into a specific language.  (Review the localization page to learn more about this feature.) 

To add a translated column, follow these steps:

1. Click on the 'Add Column' button.
2. In the Add Column dialog, for 'Type' select 'Translated Text'.
3. Select the language this column will contain.
4. Select the text column that this column would translate.

For example, to add a Japanese translation for the 'Material' column in a table, complete the steps above in a dialog box like this:

Then enter the Japanese text into the new "Material (Japanese)" column that appears.  When a user prefers Japanese, then any option filter showing the material column will automatically display the Japanese version to them.  

Filling a local table with data from a database table

To setup the local table to be automatically filled by data from a database table, do the following:

1. Create a database table.
2. In the Admin interface, under the Resources > Tables menu, create a new local table or select an existing one.
3. Set the source of the table to 'Database Table' instead of 'None'.
4. Select the database table or view you would like to connect to.  Ensure the size of the database table you selected does not exceed the size limit shown in the help bubble.
5. Decide how the updates will be applied across your environments.
   • If you want to automatically update this local table only in your dev environment, uncheck the "Update from source in all environments" checkbox.  The following will occur:
     • Immediately after saving this table in your dev environment, it will be updated with data and schema from the matching database table in your dev environment.
     • The table in your test and production environments will be untouched. 
     • You can deploy this local table in any future deployment like any other table.  A matching database table in test or prod is not required.
   • If you want to automatically update this local table across all your environments, check the box.  The following will occur:
     • Immediately after saving this table in your dev environment, it will be updated with data and schema from the matching database table in your dev environment.
     • When you deploy this local table to test, this table in your test environment will be updated from the matching database table in your test environment. (if that database table does not yet exist in your test environment, you'll see an error in the logs for that environment.)
     • When you deploy this local table to prod, again the matching database table in your prod environment will be used to populate this local table.  If that database table doesn't exist in your prod environment, you'll see an error in the logs.
6. Save the table. 

Views behave differently.
Local tables can be sourced by a database table or a database view.  When filled by a database view, however, the local table does not automatically refresh whenever it is used.  You must start that refresh manually.  One technique to trigger the refresh is to set the source of the table from 'Database' to 'None', save it, and then return the source back to 'Database'.  This will trigger the refresh.  You can also trigger the refresh through the Rest API for tables.

Local tables limited in size.
Local tables sourced by a database table or view have a maximum size limit. The specific limit is shown in the help bubble next to the list of database sources, and each source in the list also has its size shown.   If your source data on the server exceeds this limit, some data may not fit into the local table and will be truncated.  Warning messages will appear in the log files if a local table approaches this limit during deployment, or if data is truncated because the limit was exceeded during deployment.