Configuration examples
Datapoint line_item_grouped
must exist in the schema otherwise the extension will keep adding new line items and not removing the old ones! This datapoint name is currently hardcoded and cannot be changed.
The recommended schema datapoint:
{
"category": "multivalue",
"id": "line_items_grouped",
"label": "Line Items (grouped)",
"children": {
"category": "tuple",
"id": "line_item_grouped",
"label": "line_item_grouped",
"children": [
{
"rir_field_names": [],
"constraints": { "required": false },
"default_value": null,
"category": "datapoint",
"id": "item_code_grouped",
"label": "Code",
"type": "string"
},
{
"rir_field_names": [],
"constraints": { "required": false },
"default_value": null,
"category": "datapoint",
"id": "item_description_grouped",
"label": "Description",
"type": "string"
}
// Add more datapoints here as needed…
],
"rir_field_names": []
},
"min_occurrences": null,
"max_occurrences": null,
"default_value": null,
"rir_field_names": []
}
Additionally, the line_items
table must exist in the schema as well. This is, however, the typical default.
Group line items by item code
The following SQL groups the line items by the value in item_code
datapoint.
SELECT
MAX(item_code) as item_code_grouped,
MAX(item_description) as item_description_grouped
COALESCE(SUM(NULLIF(item_quantity, '')), '') AS item_quantity_grouped
FROM
inmemory_line_items
GROUP BY
item_code
When using SUM
function, it is important to call it like this:
COALESCE(SUM(NULLIF(item_quantity, '')), '') AS item_quantity_grouped
Using simple SUM(item_quantity)
would incorrectly turn empty datapoints into 0
which might not be desirable (imagine turning missing total amount ""
into 0
, for example).
Full configuration would look like this (the SQL can be copy-pasted but must be inline):
{
"transformations": [
{
"data_sources": [
{
"schema_id": "line_items",
"table_name": "inmemory_line_items"
}
],
"sql_statement": "SELECT\n MAX(item_code) as item_code_grouped,\n MAX(item_description) as item_description_grouped\n COALESCE(SUM(NULLIF(item_quantity, '')), '') AS item_quantity_grouped\nFROM\n inmemory_line_items\nGROUP BY\n item_code",
"output_schema_id": "line_items_grouped",
"allow_target_update": true
}
]
}
Group line items conditionally
In some cases (for example, when integrating with NetSuite), it is necessary to group only so called "inventory items" and keep "expenses" intact. This can be achieved using a bit more verbose GROUP BY
clause:
SELECT
MAX(item_type) as item_type_grouped,
COALESCE(SUM(NULLIF(item_quantity, '')), '') AS item_quantity_grouped,
MAX(item_description) as item_description_grouped,
MAX(item_ns_item_match) as item_ns_item_match_grouped
FROM
inmemory_line_items
GROUP BY
CASE
WHEN item_type = 'inventory_item' THEN item_ns_item_match
ELSE item_index -- a unique identifier for each row to prevent grouping for 'expense' type rows
END;
The item_index
from the SQL above is a formula field with the following definition (to give each row a unique number):
field._index
The SQL, of course, needs to be copied to the actual configuration which could look like this, for example:
{
"transformations": [
{
"data_sources": [
{
"schema_id": "line_items",
"table_name": "inmemory_line_items"
}
],
"sql_statement": "SELECT\n MAX(item_type) as item_type_grouped,\n COALESCE(SUM(NULLIF(item_quantity, '')), '') AS item_quantity_grouped,\n MAX(item_description) as item_description_grouped,\n MAX(item_ns_item_match) as item_ns_item_match_grouped\nFROM\n inmemory_line_items\nGROUP BY\n CASE\n WHEN item_type = 'inventory_item' THEN item_ns_item_match\n ELSE item_index -- a unique identifier for each row to prevent grouping for 'expense' type rows\n END;",
"output_schema_id": "line_items_grouped",
"allow_target_update": false
}
]
}