Find orphan note records in a Dynamics GP company database

About the Dynamics GP Notes table

Notes are held within the SY03900 company table in Dynamics GP. Every note in that notes table has a NOTEINDX, which is used by other tables in GP to reference that note. This makes the notes system extensible, when new modules/addins are created for GP,  those modules can simply piggy back off the existing notes table for their notes too by inserting notes into the table and referring to them by index. However this does have the disadvantage that looking at a random note in the notes table, you have no idea as to what records may be referencing that note. 

Hence, if you were to delete records from tables that reference note records, it orphans any note record for that deleted record. The note record can still exist, but the record that refers to it has been removed. An example of where this could happen is, if someone were to delete a sales order line using SQL DELETE, without also deleting the note record in SY03900 that is referenced by that sales order line record. 

You see, the note table does not have a "source" field to identify the "owner" table, so there is nothing in the note record to indicate from what table the note record originated. Thus you must check all the NOTEINDX fields in all the tables over the entire company database in order to find its owner.

Lucky for us, using dynamic SQL we can do this. We can query SQL server for the table definitions of all the tables in the database containing the field named NOTEINDX. We can then copy all the values that exist from each of those tables found into one big list of NOTEINDX values. Finally we can compare the notes table with that extracted big list of all valid values and find where we have a value in the notes table that does not exist in any of the tables in the GP company database. These will (most likely) be orphan records. Note you must exclude the SY03900 table itself when preparing this consolidated list!

Dynamic SQL to find all references to note records

The following script does this. It looks for any table that have a field named NOTEINDX and inserts all the note index numbers from those tables and fields into a temp table, from where you may join it back to the notes table to find the notes that no longer have reference in the database (orphaned). 

If you are then going to use the results from this to delete notes, beware as if you have a third party product that uses notes but does not name its reference to the note as NOTEINDX, so I'm saying use this with care, especially if you start removing notes based on it, check what they are first and gain confidence they are genuine orphans.

 

IF EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE NAME = '##NotesConsolidated'
)
DROP TABLE dbo.##NotesConsolidated

SET NOCOUNT ON

CREATE TABLE ##NotesConsolidated (
NOTEINDX numeric(19,5)
,TableName VARCHAR(1000)
)
GO
DECLARE @SqlStatement VARCHAR(500)
DECLARE @DatabaseName AS VARCHAR(5)
SET @DatabaseName = cast(db_name() AS VARCHAR(5))

DECLARE This_cursor CURSOR
FOR
SELECT 'INSERT INTO ##NotesConsolidated (NOTEINDX, TableName) select NOTEINDX, ''[' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'' from [' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.NAME = 'NOTEINDX' AND NOT (t.name='SY03900' AND SCHEMA_NAME(schema_id)='dbo');
OPEN This_cursor

FETCH NEXT
FROM This_cursor
INTO @SqlStatement

WHILE (@@fetch_status <> - 1)
BEGIN
EXEC (@SqlStatement)

FETCH NEXT
FROM This_cursor
INTO @SqlStatement
END
DEALLOCATE This_cursor

SELECT * FROM SY03900
where NOTEINDX not in (
SELECT NOTEINDX FROM ##NotesConsolidated )

 

Results 

Examples of the four hundred and seven (yours will differ) tables containing references to notes in the notes table:

Word Template reports format messed up when rendered to PDF Dynamics GP 2015

Dynamics GP2015 Word PDF Alignment wrong

After upgrade to GP2015 it was found the modified reports using Word Templates screwed up when sent to email as PDF.

Email embedded PDF reports from Dynamics GP messes up the formatting, resulting junk formatting.

After a week of investigation and following false leads around the email interaction interfering with formatting, no solution was found.

Then after trying and failing to compensate for the column widths being upset and failing, the solution ended up to be an installation of the version 14.00.095, applying the GP January Hotfix.

Showing GP login box with Version 14.00.0952 shown

Information for the hotfix can be found under this post on the GP blogs… Microsoft Dynamics GP January Hotfix....RELEASED

Dynamics Community logo

There is an example included on this post in the community forums: Alignment issues in PDF version of Word Templates

GPUG Magazine Q2–2016 is available!

If you didn’t already know, there is a professional magazine for Dynamics GP. Published by the Dynamics GP User Group (GPUG). The magazine has hints & tips, conference and events listings, product and services advertisements that are relevant to the GP market. Also showcased are some of the benefits of joining GPUG. Go have a read!

 GPUG Q2 2016 Magazine Cover

Q2 magazine has an especially interesting article about the release of GP2016 on page 24 by an author who’s name you may well be already familiar with! It is an article commissioned and written before we started getting details of the release but also provides the links to the Microsoft team blog where up to date information is now available for GP2016.

This magazine there is an interesting article from Chad Sogge – group program manager at Microsoft. Should the opportunity arise, Chad is one of the most interesting people you can catch for a chat on developer topics, it is great that he spared some time to talk to the user group magazine!

Dynamics GP window forms flickering-redrawing during Network Group Policy update

Rolling out Windows 10 to our estate has caused an issue with Dynamics GP interacting with Group Policy updates. The users are experiencing the form windows in Dynamics GP vigorously flickering/updating for about five seconds, at regular, but random intervals through the day.

Group policy is a set of network rules/configuration that can be pushed to network machines or/and users.

It turns out to be related to when group policy gets automatically pushed to the machines and users that happens throughout the day.

It can be reproduced by force an update by invoking the update manually from the machine from the command prompt:

GPupdate /target:user 
GPupdate /target:computer

Group policy update running


I’m not an expert in group policy, but from what I understand, the Windows 10 machines are using group policy extensions that were not previously used for the XP or Win7 machines. For now it looks like the users are going to have to live with this.