Understanding Notes and the Note Index field

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.