You’re offline. This is a read only version of the page.
5
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.
That is not an available field in the 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?
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.
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.
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?
Did you check Entry Type and Document Type?
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,151
Super User 2024 Season 2
#2
Martin Dráb
229,963
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (