Understanding Notes and the Note Index field

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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:

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

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.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

30 thoughts on “Understanding Notes and the Note Index field

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  16. Hi David,

    I am new to GP, and I am facing the following problem:
    In table GL00100, I can find the account 20500 and 21500, and both have a note index.
    The problem is that those notes do not exist in table SY03900.

    How can I fix that?
    Is it possible to insert those note index in table SY03900? If so, how to do it?

    Thank you

    Like

    • Hi Di

      When card or transaction records are created a note index is assigned. It will only have a record in the SY_Record_Note_MSTR table if a note has been added. The note index is assigned ready to be used, but it might not be used.

      David

      Like

      • Thank you for your explanation. It cleared the matter to me.

        In the case I mentioned above, the record has a note index, but there is no note in the SY_Record_Note_MSTR, and I want to create a note for this record.

        Is it possible?

        Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.