Product extension tables extensions and customer extension tables extensions are tables where you store data that is are associated with a product/customer, but that shouldn’t should not be stored directly in the product/customer master table.
For example, products have a one-to-many relation to manufacturing costs, since manufacturing costs can vary over time. Therefore, it is suitable to keep such data in a product extension table.
General Queries
In the database where all the master data is are persisted, extension the extensions tables may be stored on in the same physical tables as other extension tables. To determine which extension table a row belongs to, the field name`holds The field name holds the name of the product extension that the a row belongs to. Therefore, to query an extension table, you need to include a filter that matches the `namename field to the extension you want to query.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
List<Map<String, Object>> find(
String tableName,
int startRow,
int maxRows,
String sortBy,
List<String> fields,
Filter... filters
) {
def tableFilter = Filter.equal('sku', tableName) // ❶
return api.find(
'CX',
startRow,
maxRows,
sortBy,
fields,
tableFilter,
*filters
) as List<Map<String, Object>>
} |
❶ Only include those rows that belong to the specified product extension table.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
List<Map<String, Object>> find(
String tableName,
int startRow,
int maxRows,
String sortBy,
List<String> fields,
Filter... filters
) {
def tableFilter = Filter.equal('name', tableName) // ❶
return api.find(
'PX',
startRow,
maxRows,
sortBy,
fields,
tableFilter,
*filters
) as List<Map<String, Object>>
} |
❶ Only include those rows that belong to the specified customer extension table.
Product Cost
Product extensions are suitable to store manufacturing or purchasing costs , which will vary over time. Moreover, you might want to convert the currency ahead of time, to increase the improve performance of your code.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
@Field String TABLE_NAME_PRODUCT_COST = 'Food_ProductCost'
@Field String COLUMN_NAME_VALID_FROM = 'attribute1'
@Field String COLUMN_NAME_AVERAGE_COST = 'attribute2'
@Field String COLUMN_NAME_CURRENCY = 'attribute3'
BigDecimal findCost(
String sku,
String currency,
String date = new Date()
){
String dateAsString = date.format('yyyy-MM-dd')
Filter filter = Filter.and(
Filter.equal('name', TABLE_NAME_PRODUCT_COST), // ❶
Filter.equal('sku', sku),
Filter.equal(COLUMN_NAME_CURRENCY, currency),
Filter.lessOrEqual(COLUMN_NAME_VALID_FROM, dateAsString)// ❷
)
return api.find(
'PX',
0,
1, // ❸
COLUMN_NAME_VALID_FROM, // ❹
[COLUMN_NAME_AVERAGE_COST], // ❺
filter
).getAt(0)?.getAt(COLUMN_NAME_AVERAGE_COST) as BigDecimal // ❻
} |
❶ Only include those rows that belong to the specified product extension table.
❷ Only include costs that are valid from before the specified date.
❸ Only fetch a single result.
❹ Sort according to the valid date. The latest date will be first. Since the query only includes a single result, that result will be the most up-to-date cost.
❺ Only fetch the necessary data.
❻ In the database, all values are stored as strings. Therefore, it can happen that a string representation is returned — rather returned - rather than a decimal value.