Item Ledger Entry relation to Line No.

anil_mujagic
anil_mujagic Member Posts: 91
edited 2008-12-04 in NAV/Navision Classic Client
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
Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041

Comments

  • lduddridge
    lduddridge Member Posts: 9
    Hello,

    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.
  • anil_mujagic
    anil_mujagic Member Posts: 91
    lduddridge wrote:
    Hello,

    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 know there is a "Document No." field, but my question was about Line No. in that document.
    Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041
  • lduddridge
    lduddridge Member Posts: 9
    Try using the "Entry No." on the Item Ledger Entry.

    I believe this lines up with the Ship Entry No you were talking about earlier.
  • anil_mujagic
    anil_mujagic Member Posts: 91
    lduddridge wrote:
    Try using the "Entry No." on the Item Ledger Entry.

    I believe this lines up with the Ship Entry No you were talking about earlier.
    Sorry to ask you this, but did you read my post entirely? I know that you want to help but, you are telling me something that I already explained in my post.

    Does anyone else of you guys has some suggestion?
    Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041
  • lduddridge
    lduddridge Member Posts: 9
    NOOOOOO! I am sooooooooo sorry dude.

    I think I got a bit selective about what I read!
  • lduddridge
    lduddridge Member Posts: 9
    can you use item number? or do you have a chance of having more than one line of the same item in the same document number?
  • anil_mujagic
    anil_mujagic Member Posts: 91
    lduddridge wrote:
    NOOOOOO! I am sooooooooo sorry dude.

    I think I got a bit selective about what I read!
    No problem :wink: , but i still need a solution... anyone please?
    Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041
  • anil_mujagic
    anil_mujagic Member Posts: 91
    lduddridge wrote:
    can you use item number? or do you have a chance of having more than one line of the same item in the same document number?
    Actually that is the problem. I have situations where there are two lines with same Item No. but with different discount, and I need to know that discount in related Item Ledger Entry.
    Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041
  • AlexWiley
    AlexWiley Member Posts: 230
    Well, if there were different discounts then there would be different end sales amount, so can't you tie to the value entry table, have it validate item number, quantity, and sales amount actual to the Sales Cr. Memo Line table and then have it pull the line number off that second table?

    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.
  • Alex_Chow
    Alex_Chow Member Posts: 5,063
    anil_mujagic wrote:
    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

    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.
    Confessions of a Dynamics NAV Consultant = my blog
    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
  • anil_mujagic
    anil_mujagic Member Posts: 91
    I temporary solved the problem by grouping item ledger entry by Item No.
    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 :lol:

    In the mean time thank you for your suggestions guys!
    Try my NAV Object Comparer: http://www.mibuso.com/dlinfo.asp?FileID=1041

Categories