Hi,
For each Item Ledger Entry No. of type Sale I have to find the related line in sales document.
I found the field "Item Shpt. Entry No." in "Sales Shipment Line" table, and I also found the "Item Entry Relation" table.
That covers sales invoices. But, I couldn't find the way to relate Item Ledger Entry No. to "Sales Cr.Memo Line" or "Posted Service Invoice Line".
Does anyone know is there a way to find a relation?
NAV 4.0 SP3
0
Comments
There is a "Document No." column in both tables does this work for you?
I just tried it myself in SQL but the return is taking a bit of time.
I believe this lines up with the Ship Entry No you were talking about earlier.
Does anyone else of you guys has some suggestion?
I think I got a bit selective about what I read!
Otherwise, you can do it if you are using Specific Costing based on the Applies-to Entry field.
Those are the only solutions I can offer you, hope it helps.
You will need to find the related Value Entry, then look up to the Item Ledger entry from there.
The reason why it's designed like this is because it's a many to one relationship. i.e. One ILE can have many value entries and sales invoices/credit memos.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
I lost the detailed info but it turned out that it is not so important here.
For each entry I find (through first record in value entry) the document no., and in document line I calculate sum of discount amount, and sum of quantity for that item. Then I divide total amount by total quantity and I get average discount by item in that document, and then multiply that amount by quantity from item ledger entry.
This way I will not get exact discount amount for each item ledger entry, but as long as discount amount is totaled by item, I don't care. If client turns to insist on details, you will see this post active again
In the mean time thank you for your suggestions guys!