Sunday, November 29, 2015

How to modify the Sales Invoice Report to print Serial No./Lot No.


question-686336_1920
There are several requests on the Microsoft Dynamics Community forum to get an example or for the logic to print the serial no. or lot no. on the sales invoice report. In this post I will explain how to modify the standard sales invoice report (Report 10074: NAV 2015) to print the serial no. or lot no. on the report.
For this purpose I have added a new function in the report 10074, called GetSerialLotNo
LOCAL PROCEDURE GetSerialLotNo@1240060002();
    VAR
      ItemTrackingMgt@1240060000 : Codeunit 6500;
      TempItemLedgEntry@1240060001 : TEMPORARY Record 32;
      InvoiceRowID@1240060002 : Text[150];
      LineNo@1240060003 : Integer;
      Inserted@1240060004 : Boolean;
      SerialLot@1240060005 : Code[60];
      ValueEntryRelation@1240060006 : Record 6508;
      ValueEntry@1240060007 : Record 5802;
      ItemLedgEntry@1240060008 : Record 32;
    BEGIN
      
      IF TempSalesInvoiceLine.Type <> TempSalesInvoiceLine.Type::Item THEN
        EXIT;
      TempItemLedgEntry.RESET;
      TempItemLedgEntry.DELETEALL;
      InvoiceRowID := TempSalesInvoiceLine.RowID1;
      ValueEntryRelation.RESET;
      ValueEntryRelation.SETCURRENTKEY("Source RowId");
      ValueEntryRelation.SETRANGE("Source RowId",InvoiceRowID);
      IF ValueEntryRelation.FIND('-') THEN BEGIN
        REPEAT
          ValueEntry.GET(ValueEntryRelation."Value Entry No.");
          ItemLedgEntry.GET(ValueEntry."Item Ledger Entry No.");
          TempItemLedgEntry := ItemLedgEntry;
          TempItemLedgEntry.Quantity := ValueEntry."Invoiced Quantity";
          IF TempItemLedgEntry."Entry Type" IN [TempItemLedgEntry."Entry Type"::Purchase,TempItemLedgEntry."Entry Type"::Sale] THEN
            IF TempItemLedgEntry.Quantity <> 0 THEN
              TempItemLedgEntry.INSERT;
        UNTIL ValueEntryRelation.NEXT = 0;
      END;
      IF TempItemLedgEntry.FINDFIRST THEN
        REPEAT
          SerialLot := TempItemLedgEntry."Serial No." + ' ' + TempItemLedgEntry."Lot No.";
          WITH TempSalesInvoiceLine DO BEGIN
            INIT;
            "Document No." := "Sales Invoice Header"."No.";
            "Line No." := HighestLineNo + 10;
            HighestLineNo := "Line No.";
          END;
          IF STRLEN(SerialLot) + 1 <= MAXSTRLEN(TempSalesInvoiceLine.Description) THEN BEGIN
            TempSalesInvoiceLine.Description := SerialLot;
            TempSalesInvoiceLine."Description 2" := '';
          END ELSE BEGIN
            SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            WHILE (SpacePointer > 1) AND (SerialLot[SpacePointer] <> ' ') DO
              SpacePointer := SpacePointer - 1;
            IF SpacePointer = 1 THEN
              SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            TempSalesInvoiceLine.Description := COPYSTR(SerialLot,1,SpacePointer - 1);
            TempSalesInvoiceLine."Description 2" :=
              COPYSTR(COPYSTR(SerialLot,SpacePointer + 1),1,MAXSTRLEN(TempSalesInvoiceLine."Description 2"));
          END;
          TempSalesInvoiceLine.INSERT;
        UNTIL TempItemLedgEntry.NEXT = 0;
      
    END;



The Serial No. and Lot No. information is stored in the item ledger entry table, so the above function is used to retrieve all the item ledger entries associated to a sales invoice line, and then store those into a temporary ledger entry table, by using the temporary ledger entry table we populate TempSalesInvoiceline table, which will take care of displaying the values on the report without modifying/formatting RTC report.

To print the serial no./ lot no. we just call the new function GetSerialLotNo on the OnAfterGetRecord of the Sales Invoice Line DataItem.

Below is an example of the invoice report printing serial no.

image














Download the object from this link Sales Invoice Report

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.
Share:

Tuesday, November 10, 2015

Object Numbering Conventions In Navision


Microsoft provided object numbering convention to follow for Dynamics NAV and please check the below link for more information, it is important to follow this Conventions while making customizations as it will help to upgrade easily and support.

https://msdn.microsoft.com/en-us/library/ee414238(v=nav.90).aspx

Share:

How to Read/Write Notes in Navision using C/AL

question-686336_1920

In this post I will discuss how to read the notes and how to create the notes programmatically. When you create a note where the data is saved in the Navision ? I have seen this question being asked several times in the community forum. The answer is the notes are saved in binary format in a blob field (Note) in the Record Link table (2000000068) and the way it maps to the record is using Record ID.

Read Notes:

To read notes you need to find the “Record ID” of the record using Record Reference and then use that to filter the Record Link table and then convert value in the blob field (Note) into readable text.

In the below example the ReadNotes function takes SalesHeader as parameters and displays the first note associated with it.

Write Notes:

To create note once we again need to get the “Record ID” of the record which can be retrieved using Record Reference, and we also need to convert the text into bytes to store in the “Note” Blob Field. Since the Record Link primary key Link ID is set to Auto Increment we don’t need to find the next available “Link ID”, as INSERT statement will take care of retrieving it and assigning it.

There are two helper functions below SetText and HtmlEncode, you need these functions to write notes.

PROCEDURE ReadNotes@1240060000(SalesHeader@1240060003 : Record 36);
    VAR
      RecordLink@1240060000 : Record 2000000068;
      NoteText@1240060001 : BigText;
      Stream@1240060002 : InStream;
      RecRef@1240060004 : RecordRef;
    BEGIN
      RecRef.GETTABLE(SalesHeader);
      RecordLink.SETRANGE("Record ID",RecRef.RECORDID);
      IF RecordLink.FINDFIRST THEN BEGIN
        REPEAT
          RecordLink.CALCFIELDS(Note);
          IF RecordLink.Note.HASVALUE THEN BEGIN
            CLEAR(NoteText);
            RecordLink.Note.CREATEINSTREAM(Stream);
            NoteText.READ(Stream);
            NoteText.GETSUBTEXT(NoteText, 2);
            MESSAGE(FORMAT(NoteText));
          END;
       UNTIL RecordLink.NEXT = 0;
      END;
    END;

    PROCEDURE WriteNote@1240060001();
    VAR
      LinkID@1240060000 : Integer;
      Customer@1240060001 : Record 18;
      RecRef@1240060002 : RecordRef;
      RecordLink@1240060003 : Record 2000000068;
    BEGIN
      Customer.GET('10000');
      RecRef.GETTABLE(Customer);
      RecordLink.INIT;
      RecordLink."Link ID" := 0;
      RecordLink."Record ID" := RecRef.RECORDID;
      RecordLink.URL1 := GETURL(CLIENTTYPE::Current, COMPANYNAME, OBJECTTYPE::Page, PAGE::"Customer Card");
      RecordLink.Type := RecordLink.Type::Note;
      RecordLink.Created := CURRENTDATETIME;
      RecordLink."User ID":=USERID;
      RecordLink.Company:=COMPANYNAME;
      RecordLink.Notify := TRUE;
      SetText('Test Note For the Customer 10000',RecordLink);
      RecordLink.INSERT;
    END;

    LOCAL PROCEDURE SetText@4(NoteText@1001 : Text;VAR RecordLink@1000 : Record 2000000068);
    VAR
      SystemUTF8Encoder@1011 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Text.UTF8Encoding";
      SystemByteArray@1010 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      OStr@1008 : OutStream;
      s@1007 : Text;
      lf@1006 : Text;
      c1@1005 : Char;
      c2@1004 : Char;
      x@1003 : Integer;
      y@1002 : Integer;
      i@1009 : Integer;
    BEGIN
      s := NoteText;
      SystemUTF8Encoder := SystemUTF8Encoder.UTF8Encoding;
      SystemByteArray := SystemUTF8Encoder.GetBytes(s);

      RecordLink.Note.CREATEOUTSTREAM(OStr);
      x := SystemByteArray.Length DIV 128;
      IF x > 1 THEN
        y := SystemByteArray.Length - 128 * (x - 1)
      ELSE
        y := SystemByteArray.Length;
      c1 := y;
      OStr.WRITE(c1);
      IF x > 0 THEN BEGIN
        c2 := x;
        OStr.WRITE(c2);
      END;
      FOR i := 0 TO SystemByteArray.Length - 1 DO BEGIN
        c1 := SystemByteArray.GetValue(i);
        OStr.WRITE(c1);
      END;
    END;

    LOCAL PROCEDURE HtmlEncode@20(InText@1000 : Text[1024]) : Text[1024];
    VAR
      SystemWebHttpUtility@1001 : DotNet "'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.System.Web.HttpUtility";
    BEGIN
      SystemWebHttpUtility := SystemWebHttpUtility.HttpUtility;
      EXIT(SystemWebHttpUtility.HtmlEncode(InText));
    END;






The two above functions SetText and HtmlEncode are copied from the standard Navision Codeunit (454 Job Queue - Send Notification)


Download the object from this link Notes Management


Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.

Share:

Thursday, November 5, 2015

Tuesday, November 3, 2015