Showing item tracking lines in layouts (invoice, orders, transfers, etc)

As you may already have notice, standard commercial layouts (invoice, credit memo, order, etc) don’t show item tracking lines.
Everytime our customers need to have this, they ask us to show the item tracking lines below each item line. Here’s how I did it, following the standard way available in the Sales – Shipment report (R208), with a few improvements, to show it line by line.

This solution works, at least, with NAV 2009, NAV 2015 and NAV2016.

First of all, in codeunit 6500 – Item Tracking Management, you need to create a new function, similar to the existing RetrieveDocumentItemTracking, but related with each line:

PROCEDURE RetrieveDocumentItemTrackingLine@115(VAR TrackingSpecBuffer@1003 : TEMPORARY Record 336;SourceID@1000 : Code[20];SourceType@1001 : Integer;SourceSubType@1002 : Option;SourceRefNo@1004 : Integer) : Integer;
    BEGIN
      //RPM,sn
      // retrieves Item Tracking for Sales Invoice Line
      TrackingSpecBuffer.DELETEALL;
      CASE SourceType OF
          DATABASE::"Purchase Header":
            RDITPurchaseLine(TrackingSpecBuffer,SourceID,SourceSubType,SourceRefNo);
          DATABASE::"Sales Header":
            RDITSalesLine(TrackingSpecBuffer,SourceID,SourceSubType,SourceRefNo);
          DATABASE::"Sales Cr.Memo Header":
            RDITSalesCrMemoLine(TrackingSpecBuffer,SourceID,SourceRefNo);
        DATABASE::"Sales Invoice Header":
          RDITSalesInvoiceLine(TrackingSpecBuffer,SourceID,SourceRefNo);
        DATABASE::"Transfer Shipment Header":
          RDITTransferShipmentLine(TrackingSpecBuffer,SourceID,SourceRefNo);
          DATABASE::"Return Receipt Header":
            RDITReturnReceiptLine(TrackingSpecBuffer,SourceID,SourceRefNo);
        ELSE
          ERROR(Text009,SourceType);
      END;

      TrackingSpecBuffer.RESET;
      EXIT(TrackingSpecBuffer.COUNT);
      //RPM,en
    END;

As you can see, I’m already preparing it for almost all documents (services documents are missing).
Then you have to create the methods to retrieve each document’s item tracking line.

LOCAL PROCEDURE RDITSalesInvoiceLine@114(VAR TempTrackingSpecBuffer@1000 : TEMPORARY Record 336;SourceID@1001 : Code[20];SourceRefNo@1005 : Integer);
    VAR
      SalesInvoiceLine@1002 : Record 113;
      Item@1003 : Record 27;
      Descr@1004 : Text[50];
    BEGIN
      //RPM,sn
      SalesInvoiceLine.SETRANGE("Document No.",SourceID);
      SalesInvoiceLine.SETRANGE("Line No.",SourceRefNo);
      IF NOT SalesInvoiceLine.ISEMPTY THEN BEGIN
        SalesInvoiceLine.FINDSET;
        REPEAT
          IF (SalesInvoiceLine.Type = SalesInvoiceLine.Type::Item) AND
             (SalesInvoiceLine."No." <> '') AND
             (SalesInvoiceLine."Quantity (Base)" <> 0)
          THEN BEGIN
            IF Item.GET(SalesInvoiceLine."No.") THEN
              Descr := Item.Description;
            FindValueEntries(TempTrackingSpecBuffer,DATABASE::"Sales Invoice Line",
              0,SalesInvoiceLine."Document No.",'',0,SalesInvoiceLine."Line No.",Descr);
          END;
        UNTIL SalesInvoiceLine.NEXT = 0;
      END;
      //RPM,en
    END;
 
    LOCAL PROCEDURE RDITTransferShipmentLine@116(VAR TempTrackingSpecBuffer@1000 : TEMPORARY Record 336;SourceID@1001 : Code[20];SourceRefNo@1005 : Integer);
    VAR
      TransferShptLine@1002 : Record 5745;
      Item@1003 : Record 27;
      Descr@1004 : Text[50];
    BEGIN
      //RPM,sn
      TransferShptLine.SETRANGE("Document No.",SourceID);
      TransferShptLine.SETRANGE("Line No.",SourceRefNo);
      IF NOT TransferShptLine.ISEMPTY THEN BEGIN
        TransferShptLine.FINDSET;
        REPEAT
          IF (TransferShptLine."Item No." <> '') AND
             (TransferShptLine."Quantity (Base)" <> 0)
          THEN BEGIN
            IF Item.GET(TransferShptLine."Item No.") THEN
              Descr := Item.Description;
            FindItemEntries(TempTrackingSpecBuffer,DATABASE::"Transfer Shipment Line",
              0,TransferShptLine."Document No.",'',0,TransferShptLine."Line No.",Descr);
          END;
        UNTIL TransferShptLine.NEXT = 0;
      END;
      //RPM,en
    END;
 
LOCAL RDITPurchaseLine(VAR TempTrackingSpecBuffer : TEMPORARY Record "Tracking Specification";SourceID : Code[20];SourceSubType : Option;SourceRefNo : Integer)
//RPM,sn
PurchaseLine.SETRANGE("Document Type",SourceSubType);
PurchaseLine.SETRANGE("Document No.",SourceID);
PurchaseLine.SETRANGE("Line No.",SourceRefNo);
IF NOT PurchaseLine.ISEMPTY THEN BEGIN
  PurchaseLine.FINDSET;
  REPEAT
 
    IF (PurchaseLine.Type = PurchaseLine.Type::Item) AND
       (PurchaseLine."Quantity (Base)" <> 0) THEN BEGIN
      IF Item.GET(PurchaseLine."No.") THEN
        Descr := Item.Description;
      FindReservEntries(TempTrackingSpecBuffer,DATABASE::"Purchase Line",PurchaseLine."Document Type",
        PurchaseLine."Document No.",'',0,PurchaseLine."Line No.",Descr);
      FindTrackingEntries(TempTrackingSpecBuffer,DATABASE::"Purchase Line",PurchaseLine."Document Type",
        PurchaseLine."Document No.",'',0,PurchaseLine."Line No.",Descr);
    END;
  UNTIL PurchaseLine.NEXT = 0;
END;
//RPM,en
 
LOCAL RDITSalesLine(VAR TempTrackingSpecBuffer : TEMPORARY Record "Tracking Specification";SourceID : Code[20];SourceSubType : Option;SourceRefNo : Integer)
//RPM,sn
SalesLine.SETRANGE("Document Type",SourceSubType);
SalesLine.SETRANGE("Document No.",SourceID);
SalesLine.SETRANGE("Line No.",SourceRefNo);
IF NOT SalesLine.ISEMPTY THEN BEGIN
  SalesLine.FINDSET;
  REPEAT
 
    IF (SalesLine.Type = SalesLine.Type::Item) AND
       (SalesLine."No." <> '') AND
       (SalesLine."Quantity (Base)" <> 0) THEN BEGIN
      IF Item.GET(SalesLine."No.") THEN
        Descr := Item.Description;
      FindReservEntries(TempTrackingSpecBuffer,DATABASE::"Sales Line",SalesLine."Document Type",
        SalesLine."Document No.",'',0,SalesLine."Line No.",Descr);
      FindTrackingEntries(TempTrackingSpecBuffer,DATABASE::"Sales Line",SalesLine."Document Type",
        SalesLine."Document No.",'',0,SalesLine."Line No.",Descr);
    END;
  UNTIL SalesLine.NEXT = 0;
END;
//RPM,en
 
RDITSalesCrMemoLine(VAR TempTrackingSpecBuffer : TEMPORARY Record "Tracking Specification";SourceID : Code[20];SourceRefNo : Integer)
//RPM,sn
SalesCrMemoLine.SETRANGE("Document No.",SourceID);
SalesCrMemoLine.SETRANGE("Line No.",SourceRefNo);
IF NOT SalesCrMemoLine.ISEMPTY THEN BEGIN
  SalesCrMemoLine.FINDSET;
  REPEAT
    IF (SalesCrMemoLine.Type = SalesCrMemoLine.Type::Item) AND
       (SalesCrMemoLine."No." <> '') AND
       (SalesCrMemoLine."Quantity (Base)" <> 0)
    THEN BEGIN
      IF Item.GET(SalesCrMemoLine."No.") THEN
        Descr := Item.Description;
      FindValueEntries(TempTrackingSpecBuffer,DATABASE::"Sales Cr.Memo Line",
        0,SalesCrMemoLine."Document No.",'',0,SalesCrMemoLine."Line No.",Descr);
    END;
  UNTIL SalesCrMemoLine.NEXT = 0;
END;
//RPM,en
 
RDITReturnReceiptLine(VAR TempTrackingSpecBuffer : TEMPORARY Record "Tracking Specification";SourceID : Code[20];SourceRefNo : Integer)
//RPM,sn
ReturnReceiptLine.SETRANGE("Document No.",SourceID);
ReturnReceiptLine.SETRANGE("Line No.",SourceRefNo);
IF NOT ReturnReceiptLine.ISEMPTY THEN BEGIN
  ReturnReceiptLine.FINDSET;
  REPEAT
    IF (ReturnReceiptLine.Type = ReturnReceiptLine.Type::Item) AND
       (ReturnReceiptLine."No." <> '') AND
       (ReturnReceiptLine."Quantity (Base)" <> 0)
    THEN BEGIN
      IF Item.GET(ReturnReceiptLine."No.") THEN
        Descr := Item.Description;
      FindItemEntries(TempTrackingSpecBuffer,DATABASE::"Return Receipt Line",
        0,ReturnReceiptLine."Document No.",'',0,ReturnReceiptLine."Line No.",Descr);
    END;
  UNTIL ReturnReceiptLine.NEXT = 0;
END;
//RPM,en

Now, let’s change the reports to include this. I’ll only show report 206 Sales – Invoice, as example, but the changes needed in each report are very similar.

First of all, create a new dataitem called ItemTrackingLine

Then create the following needed variables and text constants.

New Global Variables:
Name                       DataType       Subtype
TrackingSpecBuffer         Record         Tracking Specification
ItemTrackingMgt            Codeunit       Item Tracking Management
TrackingSpecCount          Integer

New text constants:
Name                       ConstValue
SerialNoCaptionLbl         SN:
LotNoCaptionLbl            LN:
QuantityCaptionLbl         Quantity

Add this code to the following triggers:

ItemTrackingLine - OnPreDataItem()
//RPM,sn
IF TrackingSpecCount = 0 THEN
  CurrReport.BREAK;
SETRANGE(Number,1,TrackingSpecCount);
TrackingSpecBuffer.SETCURRENTKEY("Source ID","Source Type","Source Subtype","Source Batch Name",
  "Source Prod. Order Line","Source Ref. No.");
TrackingSpecBuffer.SETRANGE("Source ID", "Sales Invoice Line"."Document No.");
TrackingSpecBuffer.SETRANGE("Source Ref. No.", "Sales Invoice Line"."Line No.");
//RPM,en

Note: When customizing other reports, make sure to use the correct table (Sales Invoice Line in this case)

ItemTrackingLine - OnAfterGetRecord()
//RPM,sn
IF Number = 1 THEN
  TrackingSpecBuffer.FINDSET
ELSE
  TrackingSpecBuffer.NEXT;
//RPM,en
 
Sales Invoice Line - OnAfterGetRecord()
IF LineNumber >= MaxLines THEN BEGIN
  LineGroup += 1;
  HeaderGroup +=1;
  LineNumber := 0;
END;
LineNumber += 1;
 
IF (Type = Type::"G/L Account") AND (NOT ShowInternalInfo) THEN
  "No." := '';
 
//RPM,sn
TrackingSpecCount := 0;
 
IF (Type = Type::Item) THEN BEGIN
  ItemTrackingMgt.SetRetrieveAsmItemTracking(TRUE);  //NAV2009 only
  TrackingSpecCount := ItemTrackingMgt.RetrieveDocumentItemTrackingLine(TrackingSpecBuffer,"Sales Invoice Header"."No.",
      DATABASE::"Sales Invoice Header",0, "Line No.");
  ItemTrackingMgt.SetRetrieveAsmItemTracking(FALSE);  //NAV2009 only
END;
// RPM,en
 
VATAmountLine.INIT;
(…)

Finally the layout!

Add a new group, below the Table_Lines_Details_Group, to show the serial no. / lot no.

I guess images are self explanatory.

Set group filter:

And set the field expression:

This is the final result:

 

Please fell free to comment this solution.