Unambiguous one to one relation between a ledger entry and the respective origin entry's line

[Accounting Department]

In general, there is a need to achieve the purpose described in the title.

Could you please help me with how I can achieve this purpose on the specific example of a purchase invoice line?

On the one hand, I have a get query for the  https://api.accounting.sage.com/v3.1/ledger_entries/{key}?nested_attributes=all endpoint

and, on the other, hand I have a get query for the

https://api.accounting.sage.com/v3.1/purchase_invoices/{key}?nested_attributes=all endpoint

I can establish a connection between the above two endpoints for a single invoice, if in the second query, the {key} parameter equals the "origin"-s "id" {key} parameter of the first query's result.

In the second one, I have several "invoice_lines" that are identified with unique id-s like "id": {key}.

Now I would like to establish a one-to-one connection between the first query and the proper invoice line of the second query. I was looking for something like the origin's line id parameter in the first query but unfortunately, I could not find such.

Could you please advise how can I achieve an unambiguous one-to-one match?

[Steel, Mark]

Hi thanks for your question.

In short there is no one to one relationship that can be established with the API's and attributes that we have today.

Currently, the DB stores invoice_lines in a separate table to the artefact/invoice and the API’s do not expose invoice_lines other than in the invoice_lines array of the artefact.

The closest the API’s can get you to the resulting ledger_entry is by using the transaction_id as a conduit. The sales and purchase invoices API’s return the associated transaction_id. This id is also present in the resulting ledger_entries meaning you can obtain with certainty the ledger_entries created by the sales and purchase invoice.

Example sales_invoice attached

transaction_id returned from sales_invoices GET request

"transaction": {

        "id": "0dec4b201636471fa918bb064d3f2c60",

        "displayed_as": "test viewed",

        "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

    },

 

Resulting ledger_entries from attached sales_invoice example       

{

            "id": "c6aa8d02c7424d218d59b1c618cf21e3",

            "displayed_as": "test viewed",

            "$path": "/ledger_entries/c6aa8d02c7424d218d59b1c618cf21e3",

            "transaction": {

                "id": "0dec4b201636471fa918bb064d3f2c60",

                "displayed_as": "test viewed",

                "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

            }

        },

        {

            "id": "7e8c4b0316e341e197370b9c4471bf38",

            "displayed_as": "test viewed",

            "$path": "/ledger_entries/7e8c4b0316e341e197370b9c4471bf38",

            "transaction": {

                "id": "0dec4b201636471fa918bb064d3f2c60",

                "displayed_as": "test viewed",

                "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

            }

        },

        {

            "id": "d95c03bd7b9144fc92f848b7e436bc36",

            "displayed_as": "test viewed",

            "$path": "/ledger_entries/d95c03bd7b9144fc92f848b7e436bc36",

            "transaction": {

                "id": "0dec4b201636471fa918bb064d3f2c60",

                "displayed_as": "test viewed",

                "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

            }

        },

        {

            "id": "8e4113bcdb0a4ec8b78c48a02fcc12cf",

            "displayed_as": "test viewed",

            "$path": "/ledger_entries/8e4113bcdb0a4ec8b78c48a02fcc12cf",

            "transaction": {

                "id": "0dec4b201636471fa918bb064d3f2c60",

                "displayed_as": "test viewed",

                "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

            }

        },

        {

            "id": "dfa09f78d33a4a90be3445a44a92aecd",

            "displayed_as": "test viewed",

            "$path": "/ledger_entries/dfa09f78d33a4a90be3445a44a92aecd",

            "transaction": {

                "id": "0dec4b201636471fa918bb064d3f2c60",

                "displayed_as": "test viewed",

                "$path": "/transactions/0dec4b201636471fa918bb064d3f2c60"

            }

        }

This above ledger_entries are the result of a sales_invoice with two line items. It’s important to note that the number of ledger_entries can and will change if an invoice is edited and additional line items are added to it or the invoice is corrected or voided for any reason.

Thanks

Mark

[Accounting Department]

Hi Mark,

I appreciate your prompt answer.

Thank you for confirming that ".. the API’s do not expose invoice_lines other than in the invoice_lines array of the artefact."

I think, we can agree that a ledger entry having its unique {key} identifier should correspond to one and only one artefact's line {key} identifier. And, this is perfectly working on the DB level.

Is it possible/viable for the future to change the  ledger_entries endpoint to include also the artefact's line {key} identifier (besides the already included transaction {key} and origin {key} identifiers)?

I think this would be beneficial for both: the Sage and the customers sides as well.

The customers could build custom-made applications that would make much fewer API calls having this unambiguous connection between the ledger entries and the artefacts' lines which then could promote the use of the updated_or_created_since filter at the endpoints.

 

Thanks and regards,

Tamas

(Becsak, Tamas)

[Steel, Mark]

Hi Tamas,

It's something we could consider. I'll create an internal ticket which includes your requirements for an explorative look at how this could be achieved.

There will never be a one to one relationship given the nature of ledger_entries. It would always be one(invoice_line) to many ledger entries. For example, an invoice containing a single invoice line which has 100 net going to a P&L ledger_account, 20 tax going to the tax control account and 120 going to the debtors/creditors control account. This would be one line to three ledger_entries.

Thanks

Mark
 

[Accounting Department]

Hi Mark,

First of all, thank you very much for the internal ticket.

I agree with you that it would always be a one-to-many relationship between invoice lines and ledger entries. You are correct in correcting my previous statement, which was inaccurate and confusing regarding the relationships between ledger entry lines and the original documents (such as purchase and sales invoices) and their lines.

Let me try again, and please don't hesitate to correct me if I'm still wrong somewhere:

We are discussing the relationship between two API endpoints for a specific transaction type, which can be a purchase invoice, a sales invoice, or any other type (let's abstract from the others for now).

For simplicity, I will refer to one endpoint as GL (General Ledger) and the corresponding origin endpoint as INV (Invoice).

An INV has a total that has a one-to-one relationship with the corresponding GL. No INV line ID can be assigned to the GL because it represents the total.

On the other hand, an INV line has an ID, and most frequently, it has a one-to-many relationship with the corresponding GL -s. For example, an INV line with a standard VAT creates at least one GL for the expense account and another GL for the VAT account.

I know you understood my point earlier (again, thanks for that), but let me repeat for the sake of others: I am interested in connecting the GL-s with their origins and using that to control the necessary API calls by restricting them with the "updated_or_created_since" filter.
Taking your example with 100 expense, 20 tax, and 120 control accounts, if there were a retrospective correction to, let's say, only 5 expense accounts, then we would be able to refresh with API calls only for those 5 instead of the whole.

In summary, the GL -s can either have an INV line ID or not, depending on whether they correspond to an INV total or an INV line. The absence of an INV line ID in the GL  would indicate the GL-INV connection at the total (header) level, while the presence of an INV line ID, along with the "ledger_account" in the GL, would properly connect the GL with the relevant part of the INV.

Kind regards,

Tamas