Locate the Dynamics GP record associated with a Note's Document Attachment

By Steve Endow

I received a request tonight that I thought was interesting.  I'm guessing it's not a common request, but I found it intriguing, so I thought I would post it.

Dynamics GP 2013 R2 added a feature called Document Attachment, or Doc Attach.  This feature was an upgrade to the rather old OLE Notes feature in GP, and provides basic file attachment functionality throughout GP.

So that's great and all, but since I don't personally use Doc Attach, one detail that I never knew about was that the Attach feature in the Dynamics GP Note windows also uses Document Attachment.

In the screen shot above, I have the Customer Maintenance window open, then I clicked on the Note button, and you can see a Document Attach button available.

Okay, cool, that makes sense--might as well use Document Attach consistently everywhere.

So if you attach a document to the customer note, you're essentially attaching the document to the customer, right?

Well, not really.

If you use the Note document attach feature, you are attaching the document to the Note, not the Customer.  And then the Note is associated with the Customer record.  There is no direct link between the Note document attachment and the Customer.

Which leads to the request I received this evening.

"I see a document attachment record in the CO00105 table, but I can't tell what GP record it is associated with. Can you help me find the GP record?"

Here are two sample records from the CO00105 table, showing two document attachments.

The first record is for a document attached directly to a customer record using the Attach button on the Customer Maintenance window.  That record clearly tells us that the document is associated with customer ID AARONFIT001.  Easy peasy lemon squeezee.

But the second record is for a document attached to the Customer Note window.  But if you only look at the data in the CO00105 table, you wouldn't know that.  You can see a reference to "System\Notes", which you could eventually deduce means that the document is attached to a Note.  But what is DOCNUMBER "0000048D"?

From the second record, you can't tell which Note window in GP was used to attach the document, and you can't tell which GP record the document, or the Note, is associated with.

And this is where the fun begins.

Let's tackle the mysterious 0000048D value.  That value is not actually a Dynamics GP document number--it's a hexadecimal version of a number.  Why the developers chose to use hex is beyond me--I'm guessing they had a reason, but I'm not yet convinced it was a good enough reason.

You can convert the hex number to decimal through a site like this one:

http://www.binaryhexconverter.com/hex-to-decimal-converter

So we learn that 48D is equal to 1165.  Okay, so what does 1165 mean?

That value is the Dynamics GP Note Index value.  So if you query the SY03900 Note table, you can look up the Note to which your document is attached.

SELECT * FROM SY03900 WHERE NOTEINDX = 1165

Okay, wonderful.  So now we know that our document was attached to Note 1165.  But you'll see that the SY03900 table doesn't really tell us anything more than the CO00105 table did.  We see the note, but in typical Dynamics GP cruel fashion, we can't tell which GP record the Note is associated with, and we can't even tell which type of record it's associated with.  Nothing.  Zilch.  Nada.

So we know the Note Index value, but we have no idea which record has been assigned to that note index.  Lovely.

So here is where things get even funnerer.  Super fun.  And kludge filled.

If you are familiar with the Dynamics GP NOTEINDX field, you know what when you create a new record in GP, a NOTEINDX value is typically assigned to the record, even if you don't actually create a note for that record.

So somewhere in my Dynamics GP company database is a record that has a NOTEINDX value of 1156.  But I don't know which type of record, and therefore I don't know which table it might be stored in.

So, how in the world do we search the entire company database to find the record that has our Note Index value of 1156?

Well, I'm a big fan of the INFORMATION_SCHEMA views.  If you don't know about these views, go learn about them pronto, as they will save your bacon at some point.

Using the information schema views,you can use this query to see all of the tables that have 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

You then learn that around 189 tables in each Dynamics GP company database have a NOTEINDX field.  

I don't know about you, but I'm not about to query 189 tables.  I'm super lazy, and that right there sounds like a lot of work.

So how can we magically query the entire database, and more specifically, just those 189 tables, and find the one record that has a Note Index value of 1156?

Very good question.  I didn't have an answer either.

But I pulled out a big roll of duct tape and a ball of twine, and I cobbled together this messy little gem.  It isn't going to win any beauty contests, but it took me about 10 minutes to cobble together and was the simplest query that produced the cleanest results.  There are likely a dozen ways to do this in a much fancier way, but this inquiry was non-billable, so good enough worked just fine.

Of course you will need to replace the highlighted 1165 value with the specific Note Index you are looking for.  And if you are the ambitious type and plan to use this more than a few times, you could get super fancy and turn this into a stored procedure that accepts the Note Index value as a parameter.

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 queries a list of tables that contain the NOTEINDX field and then builds a SQL statement to query them all.  It then displays the results in a simple and tidy format.

And behold.  The query tells us that there are two tables with a NOTEINDX value of 1165.

SY03900 is in the list, of course, because it is the Note table.  But the other result, RM00101, tells us that the note is associated with a customer record.

So, given this information, I can query the RM00101 table.

SELECT NOTEINDX, * FROM RM00101 WHERE NOTEINDX = 1165

And with that, we have found the record associated with our mystery attached document.

So there you have it--tracing a document, attached to a note, back to the Dynamics GP record to which the note is associated.

I told you it was fun!

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 on Google+ and Twitter

http://www.precipioservices.com