Tuesday, November 10, 2015

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:

12 comments:

Jhon Abraham said...

Great post.
Regards,
DOTNET Training Institutes in Chennai

Mathew Stephen said...

Thanks for sharing such a useful post with us.
Android course in Chennai | Java course in chennai

Peter Skogberg said...

Thank you, this one really helped me :)!

Tuicakau Kasaimatuku said...

Great Post Suresh and just what I was looking for. Much Appreciated!

Regards
tui

Arthur Mac said...

Really useful to me.I would like to learn more about Java. Continue sharing.
Thanks,
Java Training in Chennai | Java courses in Chennai | Java J2EE Training in Chennai

Hung H. said...

Thank you Kulla!

Ákos Szabó said...

Hello. i have a little problem with it.
the user who have to get the message is get it, but he can't open the page with double click on the notificaton. Can you tell me how can i call the sesond procedure when i'm trying to send a notes?

Suresh Kulla said...

Hello,

Can you please explain me in detail, what is the second procedure you are referring to ?

Thanks
Suresh

Karthika Shree said...
This comment has been removed by the author.
Karthika Shree said...

Finding the time and actual effort to create a superb article like this is great thing. I’ll learn many new stuff right here! Good luck for the next post buddy..
Java Training in Chennai

Anonymous said...

Hello!

Thank you for your great post!

Regards
Leslie


Rishi said...

Hi Suresh,

Thanks for Writing this superb blog.
Your Blogs are always helpful.