Document attach feature Dynamics GP database and BusObjKey formats
I’ve been working on an archive project recently where I’ve been archiving records from GP into another archive database. This meant looking at how attachments are handled in Dynamics GP, in order to take any attachments related to records being moved into the archive.
The Table CO00102 is the table that relates the attachment to the record it is attached to and contains the BusObjKey column, that is implemented as a resource path back to the database record that the attachment is related to. Using a resource path makes it flexible, allowing the widest variety of GP object to be compatible with the attachment system.
The resource path is implemented in a inconstant manner, varying due to the different nature of the records involved and also through misuse by developers too (I think).
An example of the format pattern is:
0\PM\Purchase Order History\290941
Where the elements are as follows:
| 0 | Dynamics GP Product Dictionary ID (see hyperlink) |
| PM | Table Series ID |
| Purchase Order History | Table “Display Name” (see Microsoft Dynamics GP>>Resource Descriptions>> Tables) |
| 290941 | Record ID Could be customer id or purchase order number or other identifier for record |
However for attachments to notes windows, the Record ID is the NOTEINDX (ID of the record) but encoded into a hexadecimal version! Some developer was obviously thinking we didn’t have enough hoops to jump through when they imagined that one up and fancied burning some more CPU on the SQL server!
Update 2017-01-12: If you've used the OLE notes migration tool, you will notice that the OLE notes look like they used this way of encoding the record ID too, this might be where this scheme came from.
The article Document Attach vs. Notes and how to locate whether a master record has a document attached explains how to get from the note index against an attachment record to the hex version of that note index for participating in the BusObjKey column.
CONVERT(VARCHAR(MAX), CONVERT(binary(4),cast(NOTEINDX as integer)), 2)
In the screen shot above, the note ending in number 000001CF2 is actually note index ID 7410.
Now examining the final format format in the list, this is for the Advanced Credit Control Module (module 1577). For this module they decided that they would shorted the format of the path. Note how the format is one “folder” shorter in depth than the other paths, it misses out the “Table Series”, then jumps right to the table display name. Doing this screws up some SQL scripts that you might encounter out there on the internet that are used for reporting on attachments. They assume the same number of path separators when deconstructing the path but this has one short–oh dear! So what I’m saying is that you must program/script defensively when working with these BusObjKey fields as you are at the mercy of developers, also expect to use a lot of case statements or to create a SQL scalar function to convert your BusObjKey values!
I would be really interested to expand this article if you have any other formats that you have encountered, please drop me a comment against this post!