#GPPT Powerful Note Fix Utility added to GP Power Tools Build 30

One of the big new features for Build 30 of GP Power Tools is the Note Fix Utility.

In a perfect world the need for a utility to fix issues with Notes would not exist, but this is not a perfect world and some of the design decisions made many years ago when Great Plains Dynamics 1.0 was created in the late 1980’s were a little flawed.

Usually, the first indication to a user that there is a problem with the record notes is when they open a note, and its contents don’t seem related to the record they are currently editing. This is a sign that there are cross linked notes caused by the same Note Index being assigned to more than one record in the company. These duplicate Note Indexes are a symptom of more complex issues. It is best to actively look for and fix the issues before they result in cross linked notes.

Microsoft has a NoteFix tool which was created by Patrick Roth back for Great Plains Dynamics version 5.0 or 6.0. This tool did not leverage any SQL optimization techniques and has not been updated since about version 11.0.

My plan was to take the functionality of the existing tool and add it to GP Power Tools, so that a tool for fixing Notes would continue to remain available to the Microsoft Dynamics GP community. Once I realized that the existing tool did not leverage any SQL optimization, I decided to start the design of the tool again from scratch.

What I thought would be a week’s worth of development blew out to take an entire month of long days and late nights. There were a number of rewrites required as new issues were identified, but the end result was well worth it.

The Problems

Here is a quick explanation of how Record Notes in Microsoft Dynamics GP work. This will help you understand where things went wrong.

• Record Notes are stored in the Record Note Master (SY03900) table in the company database. They are linked to setup, cards and transaction records in a company using a Note Index field.
• Note Index values (of datatype currency) are assigned to the setup, cards and transaction records as they are created. Then if a record note is ever created it will use the Note Index value that has been assigned to it.
• The Next Note Index value is stored in the Company Master (SY01500) table in the system (usually DYNAMICS) database in the record for the current company.
• Note Index values should be unique in each company.

That all sounds great and should work nicely, except …

• Because the Next Note Index and the assigned Note Index values are stored in different databases, it is possible to restore one database separately from the other database. So, if we decide to restore the system database and not the company database for any reason, the Next Note Index will be returned to a previous value. Then as new records are created it will start giving out the same Note Index values again. Thus, creating multiple records with the same duplicate Note Indexes.
• If a duplicate Note Index has been used and a record exists in the Record Note Master, there is no way to identify which of the duplicate Note Index records the note belongs to without user interaction to see if they can identify the owner by the context of the note text.
• Records that have been imported by various “backend” means, might not have had a Note Index assigned to them at all. Note: Records created by Integration Manager, eConnect or Web Services (SOAP and REST) should assigned the Note Index value.

Then there are some questionable database design decisions which complicate the issue. These also required large rewrites of my code so they could be handled.

• The Note Index is a company based value, but it has been incorrectly used on some system level tables, such as the Currency Master table. This means the note will only show correctly when logged into the company that was used to create the note.
• Some Note Index values have been stored in fields other than the one called Note Index. Sometimes the name even refers to a Note ID rather than a Note Index, checking for the currency datatype (Using GPPT’s Resource Information window) helped confirm which were meant to be Note Indexes. A Field List was added to the Note Fix Utility to handle the multiple fields.
• Some Note Index values have been stored in arrays of up to 15 elements. This is seen in the POP module’s tables. The ability to define array fields in the Field List was added to the Note Fix Utility to handle arrays.
• Some Note Index values have been copied from the cards into the local records. So, they are not a Duplicate of another Note Index, but an intentional copy. This breaks a fundamental database design rule about not having the same data stored in multiple locations. If you want the Note Index for a record in a linked table, you should look it up from the linked table rather than keep a copy locally. To handle this, linked table functionality was added to the Field List.
• Some tables with historical data are not going to be changed and it is not worth replacing zero Note Index values for a record that will never have a note attached. So, a Table List was added to the Note Fix Utility to exclude tables from some of the fixing options.
• Some of the Note Index Fields in some of the tables were never used in the code. The Table List was updated to be able to mark a table field as not used.
• Some Note Index values using the field Note Index were copies of a Note Index in a linked table. The Field List linked table functionality could not handle this situation, so the Table List was updated with support for linked tables as well.
• Some transactions with Header and Line tables discard the Header once the transaction is posted. This means that the Note Index is duplicated for all the lines of that transaction, and they should not be counted as duplicates. The Table Grouping feature was added to the Table List of the Note Fix Utility to handle this.

Hindsight is wonderful. If I was designing this system again, I would consider storing the Next Note Index in a table in the same Database and only assigning Note Index on demand, when a note was actually created for the first time. I would always use the field as Note Index and never as any other field. I would never copy the Note Index and store them in another table. I would keep header records so data that applies to all lines only needs to be stored once. But we cannot change the design now.

The Solution

The Note Fix Utility window added to Build 30 of GP Power Tools is used to identify and fix issues with Record Note and the Note Index values used to link setup, cards and transaction records to the Record Note Master (SY03900) table. Issues can include:

• The Company’s Next Note Index stored in the Company Master (SY01500) table in the system database is lower than the maximum used Note Index in the company. Often caused by restoring Databases separately.
• Zero Value Note Indexes where records have no Note Index value assigned to them. Often caused by importing data and not assigning the Next Note Index.
• Duplicate Note Indexes where the same Note Index has been given to more than one record in the system. Often caused by the Company’s Next Note Index being incorrect due to restoring Databases separately.
• Used Duplicate Note Indexes where the same Note Index has been given to more than one record in the system AND that note has been used by one (or more) of those records. The result is cross linked notes where the note shows up for multiple records in the system.
• Note with no Note Index when there is a record in the Record Note Master (SY03900) table with a zero value for the Note Index column.
• Orphaned Notes where records in the Record Note Master (SY03900) table cannot be matched with any other records in the system. This could happen if an ISV product is using a different field to store the Note Index or if data has been removed or archives without removing the used Notes.

Note: If Orphaned Notes are showing due to an ISV product using a different field to store the Note Index values, this issue can be resolved by adding the appropriate field to the Field list.

Using the Note Fix Utility

The GP Power Tools user guide manual (now 744 pages) has information on using the Note Fix Utility but here is a quick step by step guide.

When logging into GP, you might receive a message if the Next Note Index fails a quick check against the highest used Note Index (for more info see section at the bottom of this article).

This feature can be disabled by Administrator Settings, but turning off the warning DOES NOT FIX the issue. Also there can be Note Index issues with the system even if the dialog does not open. Running the Note Fix Utility is the way to locate and fix the issues.

Selecting Note Fix Utility from the GP Power Tools Utilities menu gets a warning about backing up databases before use and ensuring other users are logged out.

Then the Note Fix Utility main window will open, and you can select which companies you wish to check for issues.

You can review or change the settings for the Field List by clicking the Edit Fields button.

If you click the Exclude Tables button, you can review or change the Tables List.

I am going to select all companies and click Process. Note that it can around 10 minutes to process a single large company. You will be presented with a warning about processing time.

The Processing dialog will be displayed while it works on identifying issues.

Once processing is complete, the company nodes can be expanded to show what issues were found.

Clicking on the individual nodes will show the Note Indexes and the tables where the issues were found. By default, fixing of the Next Note Index, Zero Valued Note Indexes, Duplicate Note Indexes and a Note record with no Note Index are all enabled.

If you wish to fix (which means delete) the Orphaned Notes you will receive a warning to check if the notes are from an ISV product that uses a different Note Index field that needs to be added to the Field List.

For the Used Duplicate Note Indexes, you can click on each Note Index and see the table records with that Note Index. Selecting a table record will display the data for that table record. You can then identify the correct record and select its checkbox.

You can also “fix” Used Duplicate Note Indexes by deleting the note and turning them into just Duplicate Note Indexes which can be fixed automatically.

Once you have selected all the items you are going to fix, click the Fix Notes button. You will be shown a dialog with the number of records and tables that will be updated.

After selecting to continue and responding to one more dialog mentioning creating backups, it will start processing the fixes.

Note: There is no undo or rollback options for the fixing. This is why you must have a backup of the system and company databases.

Once the fixing has completed the Note Fix window will be updated accordingly. Fixing for Zero Value  Note Indexes and Duplicate Note Indexes is achieved by issuing a new Note Index value to those records. For the Used Duplicate Note Indexes, the selected record keeps the original Note Index and the other records are issued new Note Indexes.

When Linked Tables are involved, if the original (such as Vendor) Note Index is updated, the new values are also updated on the linked tables that reference the same master records (such as Vendor IDs).

When Grouped Tables are involved, the new Note Index is updated to all records in the table which belong to that group, such as the GL Journal Open (GL20000) and GL Journal History (GL30000) tables.

If you wish to re-run the Processing for identifying issues at any time, click the Redisplay button. Click yes to the following warning to reset the window back to its initial state when opened.

Running this utility initially to fix any existing issues and then regularly for maintenance or after any time databases are restored will keep the system running smoothly.

Next Note Index Warning

[Edit] On log in to a company the Administrator Settings window (see section above) has a quick check for an incorrect Next Note Index value. It is on by default but can be turned off (not recommended). Even if the dialog does not show, there still could be issues with your Note Index values. It is recommended to run the Note Fix Utility on all companies regardless.

The following query is used to decide if the dialog should be displayed but is not a confirmation that the Next Note Index value is higher than all the previously assigned numbers. It will only check if there is an existing note with a higher value.

select isnull(max(NOTEINDX),0) as MaxNoteIndex
,(select NOTEINDX from DYNAMICS..SY01500 where INTERID = DB_NAME()) as NextNoteIndex
from SY03900

Fixing Test Companies

It is very common to see the Next Note Index Warning on a Test company database that is a restored duplicate of the Live company database. This is because the Next Note Index for the Live company would have increased in value over time with usage, but the Next Note Index for the Test company will have remained unchanged. The issue is that the Next Note Index is stored in the system DYNAMICS database and not in the Company Database.

After restoring a Live Company Database to a Test Company Database. You should also run SQL Server code to update the Next Note Index in the SY_Company_MSTR (SY01500) Company Master table.

declare @Live int
declare @Test int
set @Live = 1 -- Live Company ID
set @Test = 2 -- Test Company ID

select CMPANYID, INTERID, NOTEINDX, CMPNYNAM from SY01500
where CMPANYID = @Live or CMPANYID = @Test

-- Copy Next Note Index value from Live to Test Company (commented out)
/*
update SY01500 
	set NOTEINDX = (select NOTEINDX from SY01500 where CMPANYID = @Live)
where CMPANYID = @Test
*/

Thanks to Rob Farley MVP from LobsterPot Solutions, whose extensive SQL Server knowledge helped with significant performance improvements to the Note Fix Utility. If you have performance or other issues with SQL Server, I can highly recommend contacting Rob.

Hope you find this utility useful in keeping your systems running smoothly.

David

16-Sep-2023: Added information about Next Note Index warning on login to companies.
18-Sep-2023: Added Screenshot of Next Note Index warning.
15-Dec-2023: Added scripts for fixing Test company Next Note Index.
07-May-2024: Updated to note that turning off the warning dialog on login does not fix the problem.

This article was originally posted on http://www.winthropdc.com/blog.