Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

Understanding Notes and the Note Index field

Understanding Notes and the Note Index field

Rate This
  • Comments 16

David MeegoI was asked recently about the Note Index field and how it is used and in particular about the Note Index field in SY01500 table in the DYNAMICS system database.  This post will explain how the field is used as well as explain how it is possible to create the conditions that will generate cross linked notes.  Understanding how the conditions can be created will allow you to avoid that situation from occurring.

The Note Index (NOTEINDX) field in each record of the SY_Company_MSTR (SY01500) table stores the next Note Index to be used for the that company.  Every Master and Transaction record has a Note Index value assigned to it when it is created.  This Note Index is then used as the primary key for the SY_Record_Notes_MSTR (SY03900) table when a record note is actually created.

Note: The Note Index is assigned when the record is created and not when a note itself is created. There are many Note Index values assigned which do not have a matching note in the Record Notes Master table.

To get the next Note Index for a transaction or master record from the Company Master table and increment the 'Note Index' of table SY_Company_MSTR (SY01500.NOTEINDX), we can use either Dexterity code or SQL code depending on what tools we are using to create the new records.

For Dexterity we would use: call Get_Next_Note_Index, 'Note Index';

For SQL Scripting we would call the stored procedure: smGetNextNoteIndex

For an example on how to call this stored procedure please see Michael Johnson's post Get the Next NOTEINDX.


Now that we have covered the theory, I will cover a problem that I have seen at a number of sites and will explain how it can occur.

The problem is known as "Cross Linked Notes". This is when two independent (master or transaction) records in a company have been assigned the same Note Index value. A cross linked note is normally found because there is incorrect data showing in a note when a new note is added.  Any changes in the note of the first record is shown in the note of the second record. This is because both records are linked to the one note record.  There is only a single note record being use from two locations.

Fixing cross linked notes is a two fold process:

  1. You must fix the company's Next Note Index so that it is higher than any Note Index values in that company. For this purpose, please see script in the Knowledge Base (KB) article Cross-linked or incorrect notes indexes in Microsoft Great Plains (KB 872678) Secure Link. This will prevent any more cross linked notes from being created.
     
  2. If you have cross linked notes already in your data, there is a NoteFix tool which can be obtained via a support incident that will help you identify the cross linked notes and decide which record the note actually belongs to.

So how does the problem of Cross Linked Notes occur.....

The next Note Index is stored in the Company Master table in the DYNAMICS System database, while the Note Index fields are in the individual Company databases.  The primary cause of cross linked notes is when databases are restored independently. 

For example:  Company A current has a next Note Index of 1,000.  So values 1 to 1,000 have already been assigned to data records.  Due to a problem with security, I restore yesterday's backup of the DYNAMICS database.  Now the next Note Index for Company A is set back to yesterday's value, say 900.  Any new master or transaction records will now be assigned a Note Index of 900 and then 901 and so on.  The result is that the values 900 to 999 will be assigned twice to two independent records.  The scene is now set for cross linked notes to occur.

So, if a DYNAMICS database is restored without each of the Company databases, you will need to reset the next Note Index using the script in KB 872678 (link above) against each company.  If a Company database is restored without the matching DYNAMICS database, you will need to run the script against that company.

Another cause of cross linked notes can be importing of data from one company to another.  When the new records are imported they could have Note Index values which have already been assigned to other records.  It would be best to import the data with no Note Index values and then use SQL code with the stored procedure mentioned above to add the Note Index back using the current company's sequence. 

For other posts on the topic of Notes and OLE Attachments see the following posts:

OLE Attachments and Record Notes

Copying Record Notes and OLE Attachments between Companies

All about the Dexterity OLE Container

Fixing missing Note Index values

Hope you find this useful.

David

02-Feb-2010: Added links to related posts.

05-Feb-2010: Added link to post on Fixing missing Note Index values.

  • David,

    Thank you for the follow up.  My first GP mentor made me aware of this issue so the "Cross Linked Notes" issue fortunately has never happened to me.

    In my use, I commonly have to remember to update this field when performing table imports.  

  • Dave Musgrave has more information for us on notes and the note index fields in Dynamics GP including

  • Posting from the Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2009/10/more-on-ole-attachments-and-record.html

  • David,

    I have the NoteFix tool but found that it reports duplicates that are not really duplicates.  I have seen this mainly on the POP and paybles tables.  If my client is mainly concerned with correcting customer note records, is it safe to run the tool on for that table only?  If we run it for all, there are over 4000 duplicate records reported!

  • You could - but that would assume that your customer notes are only duplicated among themselves (ie 2 customers have same note index) and not where a customer and vendor note have the same note index.

    You might consider inserting all the tables except for the pm/pop tables and rm/sop tables and run it that way for now.  True there might still be issues but at least you won't get so many duplicates until I can figure out how I can resolve this.

  • Is this the same information if Collections Managment is installed?  We are under the impression that Collection Management has it's own Record Notes table?   Is this true?  

    Thanks

  • Hi Debra

    You are correct Collections Management does have its own notes system.  The CN_Notes_Text (CN00300) table stored the notes text using a Note Index to link to the CN_Notes (CN00100) table.

    These notes are independent of the standard notes described in this post.

    Thanks

    David

  • David,

    Are notes can be created for each line items in the transaction? for example, one note at header level and one for each detail line.

    Thanks

  • Hi Jovial.

    There is a single transaction note for the entire document.

    However, the SOP and POP modules have line level comments (4 lines of up to 50 characters).

    David

  • Hi David,

    In My scenario I have a cross linked notes already in my data. After executing the script still i am getting the same problem. Existing transactions i have, so how to identify which record the note actually belongs to.

  • Hi Pragadees

    You need the NoteFix tool from Support. Please log a case.

    Only your users will be able to say which record the note actually belongs to.

    There is no programmatic method.

    David

  • Thanks for the quick response David.

    1. How much it cost ?

    2. If we identify the record how can we correct it?

  • 1. nothing

    2. the application has a UI where you can view the cross linked records and the note and choose to decide which note to keep and which to fix.

  • Thanks Patrick...

  • I had all my notes in gp and was able to access just fine. Then I closed the year and when I look at the historical there all gone.

Page 1 of 2 (16 items) 12
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post