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.
 
CO00102

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!

SQL server FILESTREAM for Dynamics GP file attachments in coattachitems -is it possible?

docattachwindow

In previous post (Managing document attachments  in Dynamics GP) I looked at the attachments feature in Dynamics GP 2013-R2 and above. I talked about how files attached to GP in this manner are stored in the database, specifically in the coAttachmentItems table. This table can get to be a huge size as a consequence of file attachments file sizes accumulating, then dwarfing the other GP tables by size and having knock on issues like size of and time taken for SQL back up and restores.

The table looks like this:

/****** Object:  Table [dbo].[coAttachmentItems]    Script Date: 17/12/2016 23:32:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[coAttachmentItems](
[Attachment_ID] [char](37) NOT NULL,
[BinaryBlob] [varbinary](max) NOT NULL,
[fileName] [char](255) NOT NULL,
CONSTRAINT [PK_coAttachmentItems] PRIMARY KEY CLUSTERED
(
[Attachment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


The files are stored in the BinaryBlob and filename stored for reference in the filename field. GP does this in two steps, creating the database row with empty blob (0x0), then populating the blob data.

INSERT INTO coAttachmentItems (
Attachment_ID
,fileName
,BinaryBlob
)
VALUES (
@Attachment_ID
,@fileName
,0x0
)
 
Now populate the blob
 
UPDATE coAttachmentItems
SET BinaryBlob.Write(@Bytes, @Offset, @Length)
WHERE Attachment_ID = @Attachment_ID
 
The Dex Script log shows this too with two stages or CreateAttachment then InsertAttachment methods.
22:27:30      'CreateAttachment() of form coAttachManagement', "", "0\RM\Customer Maintenance\TEST", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt", 0, 1
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 '[0]'
22:27:30 'InsertAttachment() of form coAttachManagement', "4bb1e4e4-fe24-41ad-b102-a1f51ba76b34", "C:\Users\user\Documents\itemdesc.txt", "itemdesc.txt"


In the previous post I talked about how it would be helpful if the attach feature supported SQL Server FILESTREAM. This would allow the attachment files to be stored in file storage, which is a different tier of storage, cheaper for us to manage on the file system rather than in the database. I understand there are advantages to keeping the data together in the database but this does not scale well for us.
 
Today I wondered if it would be possible to just implement FILESTREAM myself for attachments in this table. Although this would be naughty and definitely would not be supported, it was an interesting experiment to try. 
To use file stream FILESTREAM columns must be accompanied by a corresponding uniqueidentifierROWGUID column and the column must be varbinary(MAX). These kinds of tables must also be accompanied by a unique index too.

Looking at the table, the data is being stored in a varbinary(MAX) however although the “Attachment_ID” field is a GUID in its content, sadly the content is being stored in a char(37) database data type. It needs to be a uniqueIdentifierROWGUID column to allow FILESTREAM. I suspect that GPs software language, Dexterity does not support a GUID data type, hence why its been put in a char(37). Annoyingly this means I can’t change the BinaryBlob to have a FILESTREAM attribute.

coattachitems

Thus it looks like we have hit a brick wall with trying to implement FILESTREAM this way.

Thoughts for next attempts:

  • Perhaps we can intercept the InsertAttachment method using a GP Visual Studio addin in the GP client, so as to redirect to a custom table that is backed with FILESTREAM? The problem here would be pulling the data back into the form again.
  • Perhaps we could create a new table that uses FILESTREAM and remove the existing table, replacing it with a view that converts the GUID back to char(37), so that GP is updating the view.  However this is going too far in changing the native product tables, I would not be comfortable with this nature of “table hacking”, even if it technically worked I’d be afraid of it breaking the GP install in the future.

Let me know what your thoughts are with a comment against this post.

Dynamics GP Document attachments “i” and “d” check box columns

A question came up on the GP forums  today regarding the "i" and "d" column check boxes in the Dynamics GP document attachment management window and specifically what they are there for.

Luckily I got to have a good play with this window when it was first released as a new feature in GP, so I had some idea about the answer.

d product description
i product image

Above shows the meaning of the d and i in the column headings, you can hover over them to get this as a tool tip, but what are the check boxes used for that are under these headings?

Attachment

Here in the screenshot (click to enlarge), you can see that for attachments to records on the Item Maintenance window (Item Card) only, the check boxes can be checked against the attachments. Although these check boxes show under other document types, they are disabled and can only be checked on attachments to Item Maintenance. There are also some other constraints. The image check box may only be checked if the attachment row has a file attached of type .bmp and the description check box may only be checked if the row has a file attached of type .txt, (text file and bitmap image file respectively).

Also note that only one image per attachment window (document) can be selected and only one description may be selected at any one time.

What is it for?

This feature allows a default image and long full catalogue listing style description to be associated to an item. This could be useful for many potential applications. You can image in some industries, printing a photo of the image on the pick list would aid warehouse pickers identify an item accurately.

You could also image if GP were connected to an ecommerce website this mechanism would provide a basic content management system (CMS), where the item image and the item description contained in the selected attachments could be used by the website (via the website integration), to show against that item on the website pages.

I am certain you could imagine other uses too.

If you found this helpful then please do comment, it motivates me to keep blogging!

Puzzle of the negative bin quantities in Dynamics GP

Since going live with multiple binning there have been niggles with with bin quantity errors. Until today I had assumed it was our bespoke fulfilment or other bespoke processes causing the irregularities in bin quantities. Today I had the time to analyse it and find the root cause which was an interesting detective story to share.

Negative Bin Quantities

The story starts with yet again finding Bins with negative values for their inventory quantities. We can’t have a negative number of items on a shelf, let’s investigate…

SELECT [ITEMNMBR]
,[LOCNCODE]
,[BIN]
,[QTYTYPE]
,[QUANTITY]
,[ATYALLOC]
,[DEX_ROW_ID]
FROM [dbo].[IV00112]
WHERE QUANTITY < 0
 
Running this SQL against the production database reveals where bins have negative stock, it returns this:
(19 row(s) affected)
 
So it seems 19 bins/item/location/ItemType combinations exist with a negative quantity in them, not something I would expect to see.
 
Many SQL scripts running as SQL jobs are employed as a strategy to detect anomalies in GP as fast as possible. The scripts email when problems in various areas of GP occur. One of these various integrity scripts has been emailing when things go out of whack with Bins values. So why did this script not detect a negative inventory issue? Below is the guts of the script that detects where the total stock held in bins does not match the stock held in that location.
;
WITH CTE_BinStock
AS (
SELECT ITEMNMBR
,LOCNCODE
,SUM(QUANTITY) SumQty
FROM IV00112
WHERE QTYTYPE = 1
GROUP BY ITEMNMBR
,LOCNCODE
)
SELECT iv.ITEMNMBR
,iv.LOCNCODE
,QTYONHND [Location Qty]
,ISNULL(SumQty, 0) AS [Total Bins Qty]
FROM IV00102 iv
LEFT JOIN CTE_BinStock ivb ON iv.ITEMNMBR = ivb.ITEMNMBR
AND iv.LOCNCODE = ivb.LOCNCODE
WHERE ISNULL(SumQty, 0) != QTYONHND
AND iv.LOCNCODE != ''
 
Notice that it is summing up the values. The only way this could not return rows  would be if the negative stock in each location perfectly had a matching positive stock that cancelled it out, hence the net value is correct for the location.

Time to dig into data

Hmmm…
Time to dig in to the data for one item.
 
negative bin qtys shown from table query

See how in this example the 1573 is shadowed by the –1573, I know 1 is not a real existing bin number, it is not in the correct format convention we use. Looking back at the first query we ran on this post, the one showing bin qty<0, it can be seen that all the affected items are in bins that have names that also correspond to the location they are in.
 
Bin and Locations mostly the same

This correlation was the clue I needed to guess how this might be occurring, my gut instinct from experience with GP was on to something. Next I went looking for the more unusual quantities involved, such as 1573, as they are distinguished, easy to spot among many transactions. It turns out that all the items and quantities affected were involved in an inventory transfer to another site. The inventory transaction history showed they were mostly all performed by one particular user too.

Recreating the issue

I had a theory by this point as to what was causing the negative bin values, so it is time to test it out. In development/test, create a new inventory transfer in the Item Transfer Entry window.  I chose an item and tried to move some to another location, moving some from location 1 to 26.

As shown below the Bin Quantity Transfer Entry window opens prompting for which bin the stock should be drawn from. Enter the source site ID, not the Bin, in the “From Bin” field, then tab to enter a Qty. 

Entering inventory transfer, overriding bin with location

Correctly GP asked me to create a bin I was able to say ok (subsequent errors like this would not even prompt for the bin as it is now created this first time). Dynamics GP correctly points out that there is not enough stock in that bin (as it is empty, we just created it). Look at what it asks though!

Do you want to continue, or cancel?

 Continue,  are you crazy..?!.. of course we don’t want to continue there is no stock, goodness knows what might happen! -but I suspect some other users may answer the opposite. So against my natural judgement I click continue, and complete the rest of the transaction entry and post it.

bin problem3

By this point it will come as no surprise to the reader that on checking the inventory bins, -25 was found against bin “1” for the item and the stock is untouched in the other bins in the "from" location. Correctly GP had a new quantity of 25 in the destination bin at the destination location. This is the behavior we that we were trying to replicate - jackpot!

binpost6

I’ve not gotten as far as thinking hard about if this is a feature or a bug, perhaps some sites using GP have need for this functionality in some way, for us it is a problem and confusing,  some user training and monitoring needs to go into place to prevent this happening again in production.

If you too were puzzling over negative bin quantities and this helped, then please do comment it motivates me to keep blogging!