User defined tables can be referenced in configurator formulas for calculations of material usage, times per operation and additional prices. They can also be used in cost model equations to define margin/mark-up of costs to set price of a configurable item.
Flowchart
Table keys
The table keys are used as search criteria in the user defined tables. They are preloaded when the system is installed, but users with DC1 administrator authority may add or delete keys. Depending on the context in which the formula will be used, you can use keys that relate to customers, parent items, bills of material or bills of routing.
Table header
You can choose to specify one or two dimensions for the user defined table. For each table dimension you define a key and a search method to be used. You also specify text and number of decimals for the values in the table.
Table details
You must define the table details for the selected combination (dimension 1 and 2) of user defined keys. For each combination of keys a value can be specified. When the user defined table is referenced in a formula, the system will search for the defined key value combination and use the specified value in the calculation of the formula.
Dimensions
You can define up to two dimensions for you user defined table. This means that you select two keys instead of one on the header level.
One dimension
With only one dimension defined for the table header, all instances will be connected to the same calculation value. For example:
| Key 1 (Parent item) | Value |
|---|---|
| Desk | 1,000 |
| Chair | 1,352 |
| Bookshelf | 1,134 |
Two dimensions
With two dimensions, the calculation value will depend on the combination of two selected keys. For example:
| Key 1 (Parent item) | Key 2 (Customer) | Value |
|---|---|---|
| Desk | XXX123 | 1,000 |
| Desk | XXX456 | 1,352 |
| Desk | XXX789 | 1,134 |
Configurator attributes
If you select the configurator attribute key, you can connect a configurator attribute to one of the table dimensions. The table can then be used to calculate values that are dependent on configurator attributes.
With a configurator attribute defined for the table, the calculation value will depend on the attribute value in combination with the first key. This is, for example, the case when the consumption of paint for an item depends on the selected colour:
| Key 1 (Parent item) | Key 2 (Configurator attribute/Colour) | Value |
|---|---|---|
| Desk | Blue | 1,000 |
| Desk | Yellow | 1,126 |
| Desk | Black | 1,000 |
If this table is referenced in a formula from the bill of material for the parent item, the system will search through the list of item/colour combinations and use the value that has been defined when calculating the quantities for the BOM.
Reference in formulas
Active system defined variables, numeric attributes, user defined tables and numeric constants can be referenced in the formulas with the valid operators (+, -, *, /). When the user defined table is referenced in a formula, the system will search for the defined attribute and key value combination and use the specified value in the calculation of the formula.
User defined tables can be referenced in formulas in the following ways:
- SEARCH(“User defined table”). Searches and returns a value from the specified user defined table. The table defined the search argument (keys) and the search method to be used.
- ROUNDD(“No of dec”). Rounds down the “current” calculated value of the formula to the specified number of decimal digits (0-3).
- ROUNDU(“No of dec”). Rounds up the “current” calculated value of the formula to the specified number of decimal digits (0-3).
Example
If you use the user defined table FACTA1 to define a factor for different values of the attribute A1, you could define the formula for calculating the component quantity that is dependent on attribute A1 as:
| &01 * SEARCH(FACTA1) ROUNDU(2) * &04 |
Enquiries
- User defined table keys enquiry
- User defined tables enquiry