API itemLedgerEntries Endpoint

(0) Share

Report

Posted on by Russ Matsenbaugh

Successfully loading data into on prem SQL. Trying to query against data but there seems to be a corresponding debit and credit for each transaction and no way to discern which is the original transaction. Set up a window function to partion by postingDate and itemNumber but it fails when there are multiple entries on the same date. You can't select negative values because occasionally office does reverse transfers.

Is there another endpoint that might add information?

Any direction is most appreciated.

Categories:

Dynamics 365 Business Central

All responses (7)

Answers (0)

Russ Matsenbaugh 5 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

That is not an available field in the itemLedgerEntries endpoint.
YUN ZHU 74,115 Super User 2024 Season 2 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

Hi, Can the "Positive" field help you?

Thanks.

ZHU
Inge M. Bruvik 32,748 Super User 2024 Season 1 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

If you are analysing transfer, then you need both transactions to get the complete picture.

What is the reasoning for only looking at one side of a transfer?

And how to do you define what is the correct part of a transfer to look at?
Russ Matsenbaugh 5 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

Yes, the image displayed is filtered by tranType = 'Transfer'

*fyi: this field corresponds to the API field entryType

My issue is that it produces two records and there is no way to select the correct one. It seems that the first record is always the one I want so I wrote a window function to select the Top 1. That falls apart when there is more than one transfer for the same product on the same date.

I also have this connected to a PowerBI Report and I can look at the full dataset for this table. Many of the most useful fields are missing from the API endpoint.
Russ Matsenbaugh 5 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

Entry Type is Transfer on all records.

Document Type is Transfer_x0000_Shipment or Transfer_x0000_Receipt.

There are no discernable differences between the records that allows me to identify the correct record.

Wondering if there is something hidden inside the id as the RIGHT(id, 27) appears to be the same for each record set.
Inge M. Bruvik 32,748 Super User 2024 Season 1 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

For a transfer there will always be a negative and a positive transaction. But for other transactions you will have negative or positive transactions only. Did you filter on the tranType?
Mohana Yadav 59,194 Super User 2024 Season 2 on at

Like (0)

Report

RE: API itemLedgerEntries Endpoint

Did you check Entry Type and Document Type?