Extension Tables (Quick Reference)

Product extension tables and customer extension tables are tables where you store data that is associated with a product/customer, but that shouldn’t 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 persisted, extension tables may be stored on the same physical tables as other extension tables. To determine which extension table a row belongs to, the field name`holds the name of the product extension that the row belongs to. Therefore, to query an extension table, you need to include a filter that matches the `name field to the extension you want to query.

ProductExtension.groovy

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.

CustomerExtension.groovy

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 performance of your code.

Cost.groovy

@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
    ).find()?.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 than a decimal value.