How to Show Notes from a Related Table
True confession: Sometimes, someone asks me to code something in Microsoft Dynamics NAV and it involves some odd things. Things that are so odd that I don’t find any good documentation online about how they work. When this happens, I make a note about it and write a blog entry. Partially for the good of everyone else in the universe, so that they can learn from what I did and replicate it if necessary. But mostly, I write the blog entry for myself, so that when the customer likes that weird thing I did six months ago and wants to expand it to another section of NAV, I’ve written down how I did it and what went wrong so that I don’t have to figure out the same stuff twice. This is one of those blog entries. Hopefully you, dear reader, can benefit from my selfish documentation.
A few days ago, I had a request from a client. They wanted to show the notes for the sell-to customer on a sales order by pressing a button on the sales order page. I did some work on it and made it happen, and it was unusual enough that it bears documenting in the public blogosphere.
If this had been the sell-to customer comments, it would have been easy—really easy, actually. But it was not the comments; instead, it was the notes for the customer. Customer comments are stored in the Comment Line table, and there’s already a NAV page to display them. But notes are different. Notes are stored in the Record Link table, as I’ve mentioned previously. And the Record Link table is not a typical NAV table, so I had do some special things to get the notes to display.
To start with, there’s no default NAV page that you can use to show the Record Links related to any table. There is also no NAV function for a table that you can call to show the related Notes or Record Links; display of those is handled via special system objects that can’t be modified. I therefore had to make my own page to show the notes, and my own function to display them.
First, the page (download my example page). I started by using the Page Wizard to create a simple page with all the fields from the Record Links table in it. This page got modified heavily to get the end result, which I’ve attached for your use. The initial page was done just so I could double check that I was getting the right Record Links for the Customer. I called this page “Custom Notes” because it shows Notes, and it was a custom page. Also, my creativity flows into other outlets. I added a filter to the SourceTableView so that it would only show Record Links where the Type was equal to Note, and then I was ready to make some changes to the Customer table.
In the Customer table, I added a new function called “ShowCustomerNotes” that would call the Custom Notes page. Copied out of my text version of the object, the code for it looks like this:
ShowCustomerNotes()
//Local variables are as follows:
// --CustomNotes, Page Custom Notes
// --lCustomer, Record Customer
// --lNote, Record Record Links
// --RecRef, RecordRef
IF NOT lCustomer.GET("No.")
OR ("Account No." = '') THEN BEGIN
EXIT;
END;
lCustomer.SETRECFILTER;
CLEAR(RecRef);
RecRef.GETTABLE(lCustomer);
RecRef.FINDFIRST;
lNote.RESET;
lNote.SETCURRENTKEY("Record ID");
lNote.SETRANGE("Record ID",RecRef.RECORDID);
CLEAR(CustomNotes);
CustomNotes.SETTABLEVIEW(lNote);
CustomNotes.RUNMODAL;
I made it a function so that I could call it from just about anywhere. I use a local instance of the Customer table just to verify that the record exists and so that when I set the filter, I don’t have to think about what’s going on with the rest of the Customer record.
The Record Link table can be related to any table/record in the database, and the way NAV handles that is via the Record Link.Record ID field. The Record ID field is the same value that you get if you convert a record to a RecRef and then call the RECORDID property, so that’s what I’m doing with RecordRef in there.
The call to the Custom Notes page should be self-explanatory.
After I added the function to the Customer table, I added an Action to my Sales Order page to grab the Sell-to Customer and then call Customer.ShowCustomerNotes. I also added an action to the Sales Orders list page to do the same thing, in case they wanted to see the notes from there.
I then verified that the ShowCustomerNotes function worked properly. (Spoiler alert: The code I originally wrote was not quite as smooth as the finished product displayed above. Also, Darth Vader is Luke’s father, and Princess Leia is Luke’s twin sister.)
Once my Custom Notes page was being called correctly, I had to add some code to make sure that users could actually see the Notes. Fortunately for me, this was a view-only page, so I didn’t have to do anything to let users edit notes; that would involve more code and could be its own blog entry. (If you’re feeling creative and you needed the notes to be editable, you could probably start with my custom page and add some functions to make it work, though.)
The Custom Notes page has some special stuff that I had to add to make it work. To start with, I stripped out all the fields that I’d added to start with. I added a new field that displays a text variable called DisplayNote, and DisplayNote actually holds the text of the note. This is done because the Note field in the Record Link table is a BLOB field, and you can’t directly display BLOB fields in NAV; instead, you have to read the contents of the field and convert it into something usable.
The code that converts the Note field from a BLOB into a text variable is found in the OnAfterGetRecord trigger. It does a CALCFIELDS on the Note field, because BLOBs aren’t pulled into a record variable unless you explicitly calculate them. After that, it uses the CREATEINSTREAM function to read the contents of the Note into an InStream variable. The loop I’m using is WHILE NOT TheInstream.EOS, as that will go all the way to the end of the stream. I originally tried using TheInstream.READTEXT, but that ran into issues when I had line breaks in my notes.
The WHILE NOT TheInstream.EOS loop reads the whole Note value into the NoteText variable, and I originally just plopped that right into the DisplayNote variable. There was a problem with that, though: When the note showed it on the page, there were a bunch of weird ASCII characters at the beginning of every line. Well, not quite every line—there were some notes in my test data set that had 10 characters or 13 characters, and those displayed just fine.
At the end of the OnAfterGetRecord trigger, the NoteText variable (holding our Note converted into text form) is sent into the FormatDisplayNote function. The purpose of FormatDisplayNote is to strip out those weird ASCII characters. As it turns out, NAV does this thing with text stored in BLOB fields where it appends the length of the string to the beginning of the string when you convert it, and if you don’t handle that, those weird ASCII characters show up. Why didn’t they show up for the 10 and 13 character notes, you ask? Well, they did show up, actually—but characters 10 and 13 are carriage return and line feed, so they weren’t rendered in the page in a way that I could see. I had a lot of 10 character notes in my test data set, so it was very confusing.
Eventually, I got it all worked out and I managed to make my client happy. And I learned some stuff about how NAV handles Notes, and how to deal with them when necessary. If you’re implementing this yourself, be aware that it might do some funny things with line breaks in the middle of the Notes, so watch out for that one. (It wasn’t an issue for me since the client’s Notes didn’t tend to have line breaks.)
I’d like to thank Matt Traxinger and Faithie Robertson for helping me put all this together. Matt really helped me put 2 and 2 together on the weird ASCII character thing, which was the key to making it all look right.
If you have any further questions about this or other development issues, contact one of our development experts at ArcherPoint. If you enjoyed this blog, you might like to check out our collection of Developer Blogs.