In the process of importing data from MSSQL into NAV I needed to import a comment text. I wanted to import the text into the Note BLOB field in the Record Link table. It was not obvious how to do this but after some work I successfully imported all my comments.

I first start to create the record link with the following code
[code htmlscript=”false”]IF Note <> ” THEN BEGIN
LinkID := DestTbl.ADDLINK(”,DestTbl.TABLECAPTION);
RecordLink.GET(LinkID);
RecordLink.Type := RecordLink.Type::Note;
RecordLink.Note.CREATEOUTSTREAM(OutStr);
OutStr.WRITE(Text2NoteText(Note));
RecordLink.MODIFY;
END;[/code]
After my first round I found out that I needed to include the length of the Note as a Char in the beginning of the note. Then I noticed that my Icelandic characters where not correctly imported and after some study found out that the content of the BLOB needs to be UFT-8 encoded. This led me to create the Text2NoteText function.
[code htmlscript=”false”]Text2NoteText(NoteToImport : Text[1024]) NAVNode : Text[1024]
NAVNode := UTF8Encode(NoteToImport);
NoteLength := STRLEN(NAVNode);
IF NoteLength <= 255 THEN BEGIN
Char1 := NoteLength;
NAVNode := FORMAT(Char1) + NAVNode;
Char2 := 1;
END ELSE BEGIN
Char1 := 128 + (NoteLength – 256) MOD 128;
Char2 := 2 + (NoteLength – 256) DIV 128;
NAVNode := FORMAT(Char1) + FORMAT(Char2) + NAVNode;
END;[/code]
And the UFT8Encode function
[code htmlscript=”false”]UTF8Encode(String2Encode : Text[1024]) EncodedString : Text[1024]
MakeVars;
String2Encode := CONVERTSTR(String2Encode,NavStr,AsciiStr);
FOR Index := 1 TO STRLEN(String2Encode) DO BEGIN
Char2 := String2Encode[Index];
IF Char2 <= 127 THEN
EncodedString := EncodedString + FORMAT(Char2)
ELSE IF Char2 <= 192 THEN BEGIN
Char1 := 194;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END ELSE BEGIN
Char1 := 195;
Char2 := Char2 – 64;
EncodedString := EncodedString + FORMAT(Char1) + FORMAT(Char2);
END;
END;[/code]
The MakeVars function creates the NavStr and AsciiStr that I need for the conversion. This is similar to function InitCharTables in Codeunit 424 except I am not using Excel to convert the strings.

Attached is a codeunit with these functions.

RTCNoteTool

 

15 thoughts on “Import a text to RTC Note in the Record Link Table

  1. Replied to by Kine_
    Just to add:
    the notes could be written by BinaryWriter through DotNet interop (of course, working only under RTC or webservices) in this way:
    “Record Link”.Note.CREATEOUTSTREAM(StreamOutObj);
    Encoding := Encoding.UTF8;
    BinaryWriter := BinaryWriter.BinaryWriter(StreamOutObj,Encoding);
    BinaryWriter.Write(String);
    Variables:
    Name DataType Subtype Length
    BinaryWriter DotNet ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.IO.BinaryWriter
    Encoding DotNet ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Text.Encoding
    StreamOutObj OutStream
    String Text 1000
    Reading is than similar:
    “Record Link”.CALCFIELDS(Note);
    IF “Record Link”.Note.HASVALUE THEN BEGIN
    “Record Link”.Note.CREATEINSTREAM(StreamInObj);
    Encoding := Encoding.UTF8;
    BinaryReader := BinaryReader.BinaryReader(StreamInObj,Encoding);
    NoteInText.ADDTEXT(BinaryReader.ReadString);
    END;
    NoteInText is BigText…

    1. ngsilva says:

      Hi, If I try to use this solution in a string with 1000 chars in the note I only get 500.
      Any idea of this behaviour?
      thanks

  2. You should be able to use

    CONTEXTURL + ‘&target=Page

    in the URL when adding Note

  3. José says:

    Thanks Gunnar, but I need just the opposite.
    Users enter the notes in the “Record Link” table using RTC. Now I need to write a report that would retrieve those notes but they are messed up (accents, special characters, and so).
    Do you have a UTF8Decode function (instead of UTF8Encode, that comes along with your “RTC Note Tool” codeunit?

  4. Hi José

    I have not created that function, although it could be created based on the encode function. Have not needed to use it. If you are using RTC client you can use dotnet interop from the first comment to this post.

    I have only created the UFT8 to ISO function for a whole file here. http://www.dynamics.is/?p=617

  5. Darren says:

    HI there,
    I was excited to leverage this code, but it doesn’t seem to be inserting the right value into the Notes field for 2013 R2…is there any update to this for 2013 that might be appropriate?

    Thanks,
    Darren

    1. Sure, with DotNet. Here is an example.

      SignSetup.”Web Comment Stored”::”In Fact Box Notes”:
      WITH RecordLink DO BEGIN
      ServerTempFileName := ThreeTierMgt.ServerTempFileName(”);
      StreamWriter := ServerFile.CreateText(ServerTempFileName);
      ReadPos := 0;
      WHILE ReadPos < BigComment.LENGTH DO BEGIN BigComment.GETSUBTEXT(CommentText,ReadPos + 1,MAXSTRLEN(CommentText)); ReadPos := ReadPos + STRLEN(CommentText); StreamWriter.Write(CommentText); END; StreamWriter.Close; GET(Queue2.ADDLINK('',Queue2.TABLECAPTION)); Type := RecordLink.Type::Note; "User ID" := CurrentUserID; Note.CREATEOUTSTREAM(OutStr); Encoding := Encoding.UTF8; BinaryWriter := BinaryWriter.BinaryWriter(OutStr,Encoding); BinaryWriter.Write(ServerFile.ReadAllText(ServerTempFileName)); MODIFY; ServerFile.Delete(ServerTempFileName); END; Name DataType Subtype RecordLink Record Record Link BinaryWriter DotNet System.IO.BinaryWriter.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' Encoding DotNet System.Text.Encoding.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' ServerFile DotNet System.IO.File.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' StreamWriter DotNet System.IO.StreamWriter.'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' OutStr OutStream

  6. Szabi says:

    Hi,
    can you try the case when the input string lengths is greater than 1024 character. In this case I have to write a loop until end of bigtext.
    I have to split the bigtext to parts than convert them (Text2NoteText) and concatenate into one bigtext and pass it to the Note field.
    Result is only the first part will shown in Note field on RTC.

    Example:
    T1 := Text2NoteText(‘First part of sentence.’);
    T2 := Text2NoteText(‘Second part of sentence.’);
    T12 := T1 + T2;

    Result should be: ‘First part of sentence.Second part of sentence.’
    Result in RTC: ‘First part of sentence.’

    I tried to delete chars from the end of/beginning of sentences but it does not solve the issue.

    Can you help me in this case please?

    Thanks,
    Szabi

    1. Szabi says:

      Solved. It was my fault. The samle ocde by Gunnar is working well !!!

  7. Thanks for sharing this. An additional thought: Text2NoteText can be reduced to

    NAVNode := NoteToImport;
    NoteLength := STRLEN(NAVNode);
    Char2 := NoteLength DIV 128;
    IF NoteLength <= 127 THEN
    Char1 := NoteLength
    ELSE
    Char1 := 128 + NoteLength MOD 128;
    NAVNode := COPYSTR(FORMAT(Char1) + FORMAT(Char2) + NAVNode,1,1024);

  8. Prajeesh says:

    Hi Gunner,

    I am facing an Issue with Notification System Page in the Roles Center (NAV 13 R2). The Problem was regarding the Language Problem with Bulgarian Language Pack.

    I have designed a Custom Page, which is almost a Replica of the Notification Page. In this Page there is not filter on the Boolean Variable(Notify) and there by solving the Issue by creating a New Field which is option.

    But i am facing issue regarding the Linking of the Corresponding Page for the Notification from my custom Page.

    I think we can use the URL1 which will open the corresponding Page for me. Can you help me or guide me how can i Execute or run a URL so that it will open the corresponding Page for me.

    I can give this code in the Drill Down Trigger of a field. Thanks.

  9. LESOURD says:

    hello
    I try to use your solution to create notes by code but I meet problems with the special characters.
    Could you send me the function MakeVars I try to write it similar to function InitCharTables in Codeunit 424 but it doesn’t work.
    Kind regards

    1. Did you look at the solutions in the comments? There you can see a dotnet way that is much better.

      1. Lesourd says:

        Thanks but I do not know how to use dotnet.
        What I need is the code to build the variables NavStr,AsciiStr.
        Kind regards

Leave a Reply

%d bloggers like this: