Document Attach vs. Notes and how to locate whether a master record has a document attached.

Hello Community!

Have you been using one of our newer Microsoft Dynamics GP features Doc Attach and feel it would be beneficial to understand how Microsoft Dynamics GP handles this information in SQL?

If so, I have put together some information that I hope will help you better understand how you can locate records in Microsoft Dynamics GP that have documents attached to them via SQL Server Management Studio.

This could also assist with locating OLE Note records after using the OLE Note Migration Utility.

First let me define what Document Attach is and what a Note is.  I know there have been a few questions on what the difference is between these two.

What is Document Attach?
Document Attach is a feature added to Microsoft Dynamics GP in Microsoft Dynamics GP 2013 R2 update. It allows the user to attach documents such as requisitions, credit reports, and images to master records, Sales Order Processing transactions and Purchase Order Processing transactions. In addition, you can attach documents to individual line items on transactions.

What are Notes?
The Notes feature is used to track additional information about important master records and windows in the Microsoft Dynamics GP application.

What is the difference?
The main thing you will notice is that a Note is simply additional information on a master or windows record, while Document Attach is a separate document that has been attached to the master or windows record.

What tables are used with Notes and Document Attach?

The following table shows the tables used with Notes and Document Attach. These tables are located within the COMPANY database.

I have listed below some additional details about some of the tables:

coAttachmentItems- This table holds the BinaryBlob of the attached document in Microsoft Dynamics GP.

CO00101- This table should contain one entry for each unique attachment that occurs within Microsoft Dynamics GP.

CO00102- This table is used so you can have the same attachment available on several different windows due to the Allow Document Flow feature.

CO00104-This table is used to show the status of the attached document. This is generally either Attached or Deleted.

CO40100- This table holds the setting found in the following window:
Microsoft Dynamics GP | Tools | Setup | Company | Document Attachment Setup

SY03900- This table’s NOTEINDX value correlates to the NOTEINDEX value stored on the master record table.

How do I determine whether a master record has a document attached?

You will first need to determine which master table you are wanting to address. In this blog I will use the Vendor Master (PM00200).

You can run the following Select SQL query to convert the NOTEINDX found in the master record table into its HEX filename.

SELECT CONVERT(VARCHAR(MAX), CONVERT(binary(4),cast(NOTEINDX as integer)), 2) AS 'HEXFLNM', * FROM TWO..PM00200

*Replace TWO with your company’s database and PM00200 with the master table in which you want to work with.

I have below an example:

The HEXFLNM for A Travel Company is 0000003B0.

You can use the HEXFLNM value in the BusObjKey column in the CO00102, CO00103, CO00104, and CO00105 tables to locate whether a record exists or not.

As you see in the screenshot above I have a single record listed. I have included below some additional screenshots of the front end of GP showing the attached document.

When using the Notes in a Vendor ID field you can see that the syntax changes in the BusObjKey.

*This value is reflecting what is showing in the Document Attach window. These vary by where the document is attached.

 The following screenshots show another example.

The following SQL statement is another example that will show you the VendorID, BusObjKey and Attachment_ID for the specific record.

SELECT a.VENDORID,

       b.*

FROM   PM00200 a

       INNER JOIN CO00102 b

               ON CONVERT(VARCHAR(MAX), CONVERT(BINARY(4), Cast(a.NOTEINDX AS INTEGER)), 2) = Substring(b.BusObjKey, 16, 8)  

*Replace PM00200 with the master table you are wanting to look into.

Once you know the BusObjKey you are looking for you can use this as a where clause in the CO00101 table to determine exactly what the attachment is.

I have listed below a screenshot example:

I hope that this information will be useful to you going forward.

Happy attaching!

Nicole Hellerud | Support Engineer | Microsoft Dynamics GP