Line Items Grouping

The Line Items Grouping extension aggregates line items using SQL criteria, useful when a downstream system requires a single unique line item per invoice.

Line items grouping extension allows for grouping line items based on given SQL criteria. This is handy when the downstream system has some restrictions such as only one unique line item per invoice.

Installation

Line items grouping service is provided by Rossum.ai in the form of webhook. To start using the extension, follow these steps:

  1. Login to your Rossum account.

  2. Navigate to Extensions → My extensions.

  3. Click on Create extension.

  4. Fill the following fields:

    1. Name: Line items grouping

    2. Trigger events: 
      Document content: Initialize, Started,
 Updated

    3. Queues where the extension should be executed

    4. Extension type: Webhook

    5. URL (see Available endpoints below)

  5. Click Create the webhook.

  6. Fill Configuration field (visit Configuration examples page).

Configuration Examples 4

Configure SQL-based line item grouping to aggregate and transform extracted table data before export, including conditional grouping by item type.

Note:

Datapoint line_item_groupedmust 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
Note:

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

Alternative Python Solution 1

Use this Python serverless function to group line items by tax rate, aggregating monetary values across rows that share the same rate.

Consider using the following simple Python code (as a serverless function) that replaces the whole functionality of this extension (no need for any webhook):

import pandas as pd
from txscript import TxScript, is_empty, default_to, is_set


def sum_values(values):
    """Sums values if there are any, otherwise returns an empty string (not zero)."""
    return sum(v for v in values if is_set(v)) if any(is_set(v) for v in values) else ''


def rossum_hook_request_handler(payload):
    t = TxScript.from_payload(payload)

    # Reset the target table:
    t.field.line_items_grouped = []

    # Collect all relevant data:
    data = []
    for row in t.field.line_items:
        data.append({
            "item_rate_grouped": row.item_rate.attr.value,  # Must use attr.value because of the `groupby` call!
            "item_description_grouped": row.item_description,
            "item_total_base_grouped": row.item_total_base,
            "item_tax_grouped": row.item_tax,
            "item_amount_total_grouped": row.item_amount_total,
        })

    # Group the data if any:
    if len(data) > 0:
        # https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.html
        t.field.line_items_grouped = (
            pd.DataFrame(data)
            .groupby('item_rate_grouped')
            .agg({
                "item_description_grouped": "first",
                "item_total_base_grouped": sum_values,
                "item_tax_grouped": sum_values,
                "item_amount_total_grouped": sum_values
            })
            .reset_index().to_dict("records")
        )

    return t.hook_response()