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.

Comments (22)

  1. Matthew says:

    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.  

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

  3. Paula says:

    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!

  4. Patrick Roth [MSFT] says:

    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.

  5. Debra says:

    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

  6. David Musgrave says:

    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

  7. 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

  8. David Musgrave says:

    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

  9. Pragadees says:

    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.

  10. David Musgrave says:

    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

  11. Pragadees says:

    Thanks for the quick response David.

    1. How much it cost ?

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

  12. Patrick Roth [MSFT] says:

    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.

  13. pragadees says:

    Thanks Patrick…

  14. Dmiters says:

    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.

  15. David Musgrave says:

    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.

    David

  16. Adam says:

    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! – Adam

  17. David Musgrave says:

    Hi Adam

    Batches are not kept after posting, so you would need to store them against the transaction rather than the batch.

    David

  18. Adam says:

    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!

    Adam

  19. 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

    David

  20. Fahey9 says:

    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

  21. 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.

    David