Understanding Notes and the Note Index field
I 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:
- 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)
. This will prevent any more cross linked notes from being created.
- 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
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.
Comments
Anonymous
June 03, 2009
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.Anonymous
June 04, 2009
Dave Musgrave has more information for us on notes and the note index fields in Dynamics GP includingAnonymous
October 04, 2009
Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2009/10/more-on-ole-attachments-and-record.htmlAnonymous
November 16, 2009
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!Anonymous
November 16, 2009
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.Anonymous
November 15, 2010
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? ThanksAnonymous
November 15, 2010
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 DavidAnonymous
December 26, 2012
The comment has been removedAnonymous
December 26, 2012
The comment has been removedAnonymous
March 12, 2014
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.Anonymous
March 12, 2014
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. DavidAnonymous
March 12, 2014
Thanks for the quick response David.
- How much it cost ?
- If we identify the record how can we correct it?
- Anonymous
March 13, 2014
- nothing
- 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.
Anonymous
March 17, 2014
Thanks Patrick...Anonymous
April 14, 2014
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.Anonymous
April 14, 2014
Hi Dmiters Did you keep history when you closed the year? Are you talking about recurring transactions which can share the same Note Index? If you answered No and Yes, you could have caused the note records for the recurring transactions to be removed. DavidAnonymous
July 30, 2014
We are still on GP2010, and have started using OLE Notes which we apply at the Batch-level as well as the transaction-level in some cases...However, we are realizing during testing that the Notes (display icon selected, but not linked) are disappearing after the Batch/Trx is posted. Is there a way to apply the notes to where they will "follow" the transactions in the batch after they've been posted? Any assistance or advice would be much obliged! Thanks! - AdamAnonymous
August 03, 2014
Hi Adam Batches are not kept after posting, so you would need to store them against the transaction rather than the batch. DavidAnonymous
September 24, 2015
Hi David. I appreciate your response to my last question. OLE Notes & Attachments have been working well for us during the past year. However, we had an unusual occurrence where documents attached to transactions & recurring batch disappeared or were removed...I understand Dmiters, but we keep history. Is there anyway to prevent these attachments from being removed when used with Recurring Batches? Or is there anyway to possibly restore the ones that disappeared? Thank you sir! AdamAnonymous
October 01, 2015
Hi Adam Did you look at the following article about Recurring Batches and Record Notes? blogs.msdn.com/.../recurring-transactions-and-record-notes.aspx You would need to use GP Power Tools now as the Support Debugging Tool is discontinued. http://winthropdc.com/GPPT DavidAnonymous
October 20, 2015
David, I appreciate the response and lead to the site & GP Power Tools. I'm presuming the recurring batch & record notes issue with Receivable also translates to Payables, which is actually where our issue resides. Moreover, I have been reviewing the GP Power Tools manual, as well as navigating your site...I did not realize the tools had to be purchased, and will put in a request w/ my director for them. Thanks you for all that you do for GP & the community! Have a good day, sir!
- Adam
Anonymous
October 28, 2015
Hi Adam The triggers on the article blogs.msdn.com/.../recurring-transactions-and-record-notes.aspx are specific for the RM windows mentioned. Equivalent triggers would be needed against the PM windows but the concept is the same. Let me know if you need further assistance once you have GP Power Tools. DavidAnonymous
January 07, 2019
Hi let say I am inserting the data from backend on PM10000 table, what if I not put the NOTEINDEX , I saw that if I can keep NOTEINDEX to zero it still work, where exactly I get an issue?As my understanding is that Note Index needed where we really use Notes, without using why to worry in table for updating this value.- Anonymous
January 07, 2019
Hi AkberYou should not be updating data directly as this bypasses any business logic. Have you thought about using eConnect or Webservices (older SOAP based or newer REST based services are available).The next note index value comes from the SY_Company_MSTR (SY01500) table but you should probably use the stored procedure smGetNextNoteIndex.Some windows can handle a missing note index and will obtain one, but other windows cannot handle it and you can end up with many transactions sharing the single note index value of 0 and having cross linked notes.Hope this helpsDavid
- Anonymous