Custom tables
Custom tables are collections of data in your account that pricing formulas (P3L) can reference. For example, one common use case for custom tables is to store material pricing. Operations can then reference that table to calculate a cost based on the specific material and geometric properties of a part.
In this article
Formatting custom table files
Each custom table in Paperless Parts is dictated by two separate .csv files:
- Data File
- This file contains the data you would like to upload to Paperless Parts.
- Config File
- This file dictates how to read and interpret the data file. It includes the title of each column and the type of data it contains.
In order to have a custom table appear as intended in Paperless Parts, you’ll need to follow a few simple formatting rules for the config and data files.
Config files
Config files must have the following 3 columns in this order:
- column_name
- The name of the corresponding column in the data file.
- value_type
- The type of data included in said column (numeric, string, or boolean).
- is_for_unique_key
- Indicates whether the values in this column have to be unique.
- Note: This column is not required to create a custom table. If you upload a config file with only column_name and value_type, the is_for_unique_key will default to FALSE for all values.
- Indicates whether the values in this column have to be unique.
Data files
- This file should be identical to the custom table that you expect to appear in Paperless Parts (see the Workcenter table example below).
- Make sure each column name in the data file corresponds to a column_name in the config file.
- Ex: If there is a “workcenter” column in the data file, there must be a “workcenter” row in the config file.
Creating a new custom table
Workcenter table example
Let’s say you want to import the following custom table of workcenter data into Paperless Parts.
The table itself will serve as the data file. In order to format it correctly in Paperless, create the corresponding config file.
- Each column in the data table corresponds to a row in the config table dictating the column name, the type of data stored in the column, and whether or not the values in that field need to be unique.
- For instance, the workcenter row has a string type because it will include text and is marked as TRUE because there should not be two separate rates for any specific workcenter.
Note: For organizational purposes, we suggest that you add “-data” and “-config” to the name of each file while saving. For example:
- workcenter-data.csv
- workcenter-config.csv
Now that you've formatted your data and config files, upload them into your account from the Configure tab.
- Navigate to the Configure tab.
- Select Tables to view your account’s custom tables.
- Click + New Custom Table at the bottom of the page.
- Create a custom table name.
- Note: There cannot be spaces in the table name, use an underscore.
- If you would like a table to be called “workcenter rates”, make the table name “workcenter_rates”.
- This table name is what you will reference when performing a table lookup in P3L, so make sure it’s intuitive and easy to recall.
- Note: There cannot be spaces in the table name, use an underscore.
- Select the new custom table you have created to open it.
- Click Actions.
- Select Update Table.
- This will open the Update Table panel.
- Upload the Config CSV to the Upload Config CSV section.
- Upload the Data CSV to the Upload Data CSV section.
- Click Update.
The final product will look like this:
Updating an existing table
Video walkthrough
Updating table data
Note: Updating a data table does NOT update column names or structure.
- Navigate to the custom table you would like to update.
- Click Actions.
- Select Download Data.
- This will download a .csv file with all data in the custom table.
- Note: Opening the file in Numbers will remove any leading zeros (this can be important, especially if you’re downloading zipcodes). Google Sheets does this as well, unless you uncheck the Convert option when importing.
- Modify the data and save changes.
- Click Actions.
- Select Update Table.
- Upload the updated data table to the Upload Data CSV section.
Updating table configuration (column names, structure, etc.)
- Navigate to the custom table you would like to update.
- Click Actions.
- Select Download Config and Download Data.
- This will download both the config and data files as a .csv.
- Modify the config .csv and save changes.
- Note that you cannot use a number as the first character/index 0 for string-type column names. It will provide a dot operator error message notifying you of this. If the number is placed elsewhere in the string, the column name should work.
- Incorrect examples: “3_mm_sin_cust_cost” and “3mm_sin_cust_cost”
- Correct example: “sin_cust_cost_3_mm”
- Note that you cannot use a number as the first character/index 0 for string-type column names. It will provide a dot operator error message notifying you of this. If the number is placed elsewhere in the string, the column name should work.
- Modify the data .csv so that it aligns with the updated config .csv and save changes.
- Click Actions.
- Select Update Table.
- Upload the updated config .csv to the Upload Config CSV section.
- Upload the updated data .csv to the Upload Data CSV section.
- Click Update.
Best practices
- Spaces matter!
- Extra spaces in your table’s data may affect your upload or cause unintended behavior.
- Double-check if there are extra spaces in your table by opening the CSV file in a text editor.
- Double-check the spelling in your config and data files.
- You’ll be referencing the name of the table as well as column and row names when performing a table lookup in P3L, so typos can cause unexpected results/behavior.
- All files you upload must be in .csv format.
- Check on any other operations referencing the table you’re updating to ensure there are no downstream effects. It’s good practice to check if you’re:
- Changing a column name.
- Changing the P3L filters.
- Changing an existing row.
- Updates made to custom tables or P3L will not persist to any current quote items in draft unless you Refresh pricing for a particular quote item.