Try Microsoft Edge
A fast and secure browser that's designed for Windows 10
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.
Document Attachment Master
Document Attachment Reference
Document Attachment Properties
Document Attachment Status
Document Attachment Email
Document Attach Setup
Record Notes Master
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.
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.
Nicole Hellerud | Support Engineer | Microsoft Dynamics GP
I have a question. How the Attachment_ID value is calculated? We have a customer that has thousands (more than 80K) OLE Attachments and the OLE Migration Tools is taking to much time to run. It only imports 14 notes every 7 minutes before it times out. We are thinking on create our own tool to get this job done but we need to figure the Attachment ID first. Any help you can provide would be much appreciated.
Jesus Cruz - Evertec Group, LLC.