How are those two tables related i can not find any way even tho they supposed to be related, right? Even the Item Entry Relation seems not to be connected.
quote:
Originally posted by annar
How are those two tables related i can not find any way even tho they supposed to be related, right? Even the Item Entry Relation seems not to be connected.
Both Item Ledger Entry and Item Entry Relation are related to Registered Shipment Line (111). To say it better - Registered Shipment Line is related to the corresponding Item Ledger Entry. There is no relation between Item Ledger Entry and Sales Invoice Line. There is a relation between Sales Line and Shipment Line, but only for non posted invoices and orders shipped through the Post function in the Sales Order form. Anna
The Item Entry Relation table only applies for items that are tracked (lot/serial). For non-tracked items the “Item Shpt. Entry No.” field on the Sales Shipment Line table directly points to the item ledger entry associated with the shipment. For sales invoices there is no direct relationship to item ledger entries. In fact because it is possible to have several shipments and several invoices against a single sales order where the quantities invoiced do not match the quantities shipped there is no easy relationship beween the sales invoice lines and the item ledger entries. However, for tracked items, there is the Value Entry Relation table that relates sales invoice lines to value entries which in turn are associated with item ledger entries.
Relation between Sales Invoice Line and ILE can be found through Value entries . There is a column in value entry table for Item ledger entry No.
The actual goal is to get “Serial No.” to the posted invoice. Is there any shortcut to do that? The invoice doesnt seem to have any relation to Item Ledger Entry, where the “Serial No.” information is, at least according to the navigation.
to Bobby - but how is Value Entry related to Sales Invoice Line?
quote:
Originally posted by annar
The actual goal is to get “Serial No.” to the posted invoice. Is there any shortcut to do that? The invoice doesnt seem to have any relation to Item Ledger Entry, where the “Serial No.” information is, at least according to the navigation.
I’ll paste here some code from a customized invoice report, hoping it might be of help. The code is from the OnAfterGetRecord trigger for “Sales Line” DataItem. VARIABLE InL: integer Tx60a30LottoInLN: text, 60 (dim 30) RcValueEntryRel: record, “Value Entry Relation” RcValueEntry: record, “Value Entry” RcItemLedgerEntry: record, “Item Ledger Entry” FnRowID1: local function return value (text, 250) CLEAR(Tx60a30LottoInLN); InL := 0; RcValueEntryRel.SETRANGE("Source RowId",FnRowID1); IF RcValueEntryRel.FIND('-') THEN BEGIN REPEAT IF RcValueEntry.GET(RcValueEntryRel."Value Entry No.") THEN IF RcItemLedgerEntry.GET(RcValueEntry."Item Ledger Entry No.") THEN BEGIN InL := InL + 1; Tx60a30LottoInLN[InL] := RcItemLedgerEntry."Lot No."; END; UNTIL RcValueEntryRel.NEXT <= 0; END; InNrL := InL; ------------- Local function FnRowID1: EXIT("Item Tracking Management".ComposeRowID(DATABASE::"Sales Invoice Line", 0,"Sales Invoice Line"."Document No.",'',0,"Sales Invoice Line"."Line No.")); Anna
yes, it’s for sales line, not sales invoice line. getting serial no. for non-posted sales documents was quite easy, getting them after the posting is the hard part.
quote:
Originally posted by annar
yes, it’s for sales line, not sales invoice line. getting serial no. for non-posted sales documents was quite easy, getting them after the posting is the hard part.
Mhmm … sorry, I mistyped! [:I] The code is actually from DataItem “Sales Invoice Line”. The local function FnRowID1 calls a function in codeunit “Item Tracking Management”, which composes the key for “Value Entry Relation” table, using “Sales Invoice Line” fields; “Value Entry Relation” holds the “Item Ledger Entry” record number, where you can find the lot number. [:)] I hope this sounds more understandable. Anna
It worked! Thank You! :>