Configuration Examples

🔥 HERE BE DRAGONS 🔥

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

⚠️ WARNING ⚠️

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
    }
  ]
}