Getting Invoice information when there's multiple Order No per Invoice

[Iain Warde]

I'm currently using the following 3 calls to get invoice information from our Sage 200 Professional product (2023-r2):

sop_order_return_invoice_credit_views - grabs the latest invoices (filtering on "sop_invoice_credit_document_date")
sop_order_return_line_invoice_credit_views - grabs the items for the invoice (filtering on "sop_invoice_credit_document_no")
sop_orders - grabs the item information from the order that's in the invoice (filtering on "document_no")

We have invoices where it contains multiple orders (consolidated invoices) but when I look for these in the API, the response for sop_order_return_invoice_credit_views only shows me one order number:

[sop_order_return_id] => 48896214
[sop_order_return_document_type_id] => 0
[sop_order_return_document_no] => 0000123786
[sop_order_return_document_date] => 2020-01-31T00:00:00Z
[sop_order_return_document_status_id] => 2
[sop_order_return_customer_document_no] => PO123456
[sop_order_return_total_net_value] => 248.07
[sop_order_return_total_tax_value] => 49.61
[sop_order_return_total_gross_value] => 297.68
[customer_id] => 111111
[customer_reference] => TEST
[customer_name] => Test Company Name
[customer_short_name] => Test Co
[sop_invoice_credit_id] => 48956244
[sop_invoice_credit_document_no] => 0000105622
[sop_invoice_credit_type_id] => 0
[sop_invoice_credit_document_date] => 2020-01-31T00:00:00Z
[sop_invoice_credit_document_status_id] => 2
[sop_invoice_credit_total_net_value] => 23613.15
[sop_invoice_credit_total_tax_value] => 4722.63
[sop_invoice_credit_total_gross_value] => 28335.78

As you can see the "sop_order_return_document_no" is a single response and the order totals are a lot smaller than the invoice totals.

When I use the sop_order_return_line_invoice_credit_views all, I see all items associated with that invoice, but they aren't separated by the orders they actually relate to (the first one is associated with that order, the second and third are from another order):

    [0] => Array
        (
            [sop_invoice_credit_id] => 48956250
            [sop_order_return_line_id] => 48491609
            [sop_invoice_credit_document_no] => 0000105622
            [sop_invoice_credit_type_id] => 0
            [sop_invoice_credit_document_date] => 2020-01-31T00:00:00Z
            [sop_invoice_credit_document_status_id] => 2
            [sop_invoice_credit_line_invoice_credit_quantity] => 1
        )

    [1] => Array
        (
            [sop_invoice_credit_id] => 48956251
            [sop_order_return_line_id] => 48487058
            [sop_invoice_credit_document_no] => 0000105622
            [sop_invoice_credit_type_id] => 0
            [sop_invoice_credit_document_date] => 2020-01-31T00:00:00Z
            [sop_invoice_credit_document_status_id] => 2
            [sop_invoice_credit_line_invoice_credit_quantity] => 25
        )

    [2] => Array
        (
            [sop_invoice_credit_id] => 48956252
            [sop_order_return_line_id] => 48487065
            [sop_invoice_credit_document_no] => 0000105622
            [sop_invoice_credit_type_id] => 0
            [sop_invoice_credit_document_date] => 2020-01-31T00:00:00Z
            [sop_invoice_credit_document_status_id] => 2
            [sop_invoice_credit_line_invoice_credit_quantity] => 8
        )
... (only showing the first 3 as examples, there's 500 items coming back from this call!)

So when I come to use the final call for sop_orders, it only shows me the products that are associated with the order number from the first call and the others are ignored as it cannot find them.

QUESTIONS:

1. Is it possible to see ALL order numbers associated to a single invoice?
2. Is it possible to search for orders using the lines.product_id field within the sop_orders call?

I tried the second question but not sure I'm using the filters right here:

sop_orders?$filter=lines.product_id eq '48491609'&$expand=lines

However, the response was:

The child type 'lines.product_id' in a cast was not an entity type. Casts can only be performed on entity types.

This makes me think we can't search for anything that's within the expanded objects like "lines" and "delivery_address"...