By Steve Endow

Dynamics GP 2013 R2 introduced a Document Attachment feature (Doc Attach), enhancing the older OLE Notes feature with basic file attachment capabilities. While Doc Attach is useful, many users may not be aware that the Attach feature within Dynamics GP Note windows also utilizes it.

For instance, a document can be attached to a customer note using the Document Attach button within the Customer Maintenance window’s Note section. However, this doesn’t directly link the document to the customer. The document is linked to the note, which is then associated with the customer record.

This indirect linkage poses a challenge when trying to identify the GP record associated with a document attached through a note. The CO00105 table, which stores document attachment records, doesn’t provide a direct link.

While the table shows a document linked to “System\Notes” with a DOCNUMBER of “0000048D,” it doesn’t specify the note or the associated GP record. The “0000048D” is a hexadecimal representation of the Dynamics GP Note Index value. Converting “48D” to decimal results in 1165, which corresponds to Note 1165 in the SY03900 Note table.

SELECT * FROM SY03900 WHERE NOTEINDX = 1165

Unfortunately, the SY03900 table doesn’t indicate which GP record is associated with this note index. To locate the record with NOTEINDX 1156, one can utilize the INFORMATION_SCHEMA views to find all tables containing a NOTEINDX field.

SELECT c.* FROM INFORMATION_SCHEMA.COLUMNS c

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE c.COLUMN_NAME = ‘NOTEINDX’ AND t.TABLE_TYPE = ‘BASE TABLE’

ORDER BY c.TABLE_NAME

Approximately 189 tables in each Dynamics GP company database contain a NOTEINDX field. Instead of querying each table individually, the following script generates a SQL statement to efficiently search for the record with the desired Note Index value.

DECLARE @noteindx nvarchar(20)

DECLARE @tablename nvarchar(20)

DECLARE @sqlstring nvarchar(MAX)

DECLARE @param nvarchar(100)

SET @noteindx = ‘1165’

DECLARE notecursor CURSOR FOR

SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE c.COLUMN_NAME = ‘NOTEINDX’ AND t.TABLE_TYPE = ‘BASE TABLE’

OPEN notecursor

FETCH NEXT FROM notecursor INTO   @tablename

SET @sqlstring = N’SELECT ’’’ + @tablename + ’’’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ’ + @tablename + ’ WHERE NOTEINDX = ’ + @noteindx + CHAR(13);

WHILE @@FETCH_STATUS = 0

BEGIN

       SET @sqlstring += N’ UNION SELECT ’’’ + @tablename + ’’’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ’ + @tablename + ’ WHERE NOTEINDX = ’ + @noteindx + CHAR(13);

       FETCH NEXT FROM notecursor INTO @tablename

END

CLOSE notecursor

DEALLOCATE notecursor

EXEC(@sqlstring)

This script identifies two tables containing NOTEINDX 1165: SY03900 (the Note table) and RM00101, indicating an association with a customer record. Finally, querying RM00101 reveals the specific customer record linked to the note and the attached document.

SELECT NOTEINDX, * FROM RM00101 WHERE NOTEINDX = 1165

In summary, this process demonstrates how to trace a document attached to a note back to its associated Dynamics GP record.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him onGoogle+andTwitter

http://www.precipioservices.com