Stock transfer between Microsoft Dynamics GP companies

Mergers and acquisitions present new challenges in all parts of the business and in my most recent challenge it has presented the opportunity to solve the problem of allowing one GP company with inventory to resell a recently acquired GP company's stock. Both companies now members of the same holding group. As it was suspected that there would be a rapid high volume uptake of the new products, the system had to run itself.

For sound strategic, legal and financial reasons the GP companies have to be maintained as separate entities, they may be hosted on different servers in different physical locations.

How it was done

The main requirements are;

  • Item enquiry and SOP entry staff should have sight of the stock held in the other company.
  • The selling price in the reseller company is totally independent of the originating company selling prices.
  • The cost price in the reseller company should be the FIFO cost price from the first company.
  • Stock must be reserved (allocated) as soon as possible to the reseller company to guarantee stock promises to customers.

Resulting from a couple of meetings with the stakeholders and a white boarding session, we finally picked the the best fit solution from the various proposed ideas and thus crafting of the system began.

The core of the solution is to utilise stock adjustment out of the source company and a matching stock adjustments into the reseller company. Ensuring the FIFO cost from the out adjustment of the source company is used as the cost to adjust into the selling company with. This stock requisition system had to be automatic to deal with the potential volumes of transactions each day. Easy was to get eConnect to create the inventory transactions, but more challenging was the issue of how to get those inventory transactions posted automatically.

Automatic posting of Dynamic GP transactions

Automatic posting of transactions is an integration issue often faced. There is no API exposed to allow developers to post transactions in GP. This lack of a posting API is an unfortunate gap in the developer tools experience.

Recently discovered was the Post Master product by Envisage Software. this software runs as a .NET plug-in to Dynamics GP, allowing automated posting of batches. It can be controlled through a control table, providing the long sought after hook into the posting process. Post Master will be covered in another blog post, it is a well written robust solution for automatic posting of transaction batches in Microsoft Dynamics GP.

Controlling the requisition

A set of transaction tables have been created for controlling the process of requisitioning stock. These tables allow stock to be requested and track progress as the process creates the out adjustment, in adjustment and allocates up the requisitioned stock to the SOP order. It is obvious the the stock requisition system must be very robust, coping with servers restarting, connection problems and other unforeseen events. It has to be able to roll forward or backwards any transactions that get interrupted half way through otherwise stock will “leak” causing no end of horrors! The control tables give full insight into where the stock has come from and which SOP order the stock ended up allocated to.

The control tables are monitored and written to by a windows service that runs on a server. The server process constantly looks at the control tables for new stock requests. The service uses eConnect to create the stock transactions in each company and also controls posting of the stock in the two companies via the Post Master application. Finally the windows service allocates up the stock to the SOP document requesting stock, once it has been successfully requisitioned from the source company.

When the user saves the SOP document in the reseller company, the lines on the order are scanned to see if any are held by the other company. If found and stock is available in the other company, a stock requisition request is made to the windows service by adding rows to the stock requisition control tables.

An independently developed stock allocation routine is ran using custom processes every hour to allocate SOP documents. This allocation process also checks over to the source company for available stock and requests stock from the requisition system if available. This deals with requisitioning items that have a back ordered quantity as they are received by the source company.

Duplication of items

The items to be resold have to exist in the reseller company, integration manager helped duplicate the items over to the selling company. Going forward  critical fields like item description, bin locations are synced in SQL to the source company items so that any changes are correctly propagated over. Price list are created for the reseller company items independently so that they can be priced appropriately for that companies market. 


Pick lists are printed with the locations and bins that were synchronised from the other company. As the items have been requisitioned into this reseller, they are fully fledged products in that company by this point. Thus fulfilment and despatch is no different for these items to any other in the company’s inventory.

Deallocated stock

Deallocated stock due to cancelled lines or changed quantities show up as available stock in the reseller. The reseller should never hold free stock. There should never be available stock in the reseller as any stock is always associated with an order and will be allocated, thus free stock needs to go back to the source company. Pushing stock back to the source company it cam from, is simply a reverse process to that to used to requisition the stock in the first place. This free stock sweep is done regularly to ensure stock goes back to the source company as soon as possible in order to allow it to be sold there if required.

Sock level visibility

Visibility of the stock is provided to the users through extra fields added to the SOP and Inventory enquiry windows. These windows display the stock available from the other company.

Returned items from customers

Returned items are dealt with manually at the moment. The return has to be processed through both companies to keep everything right and proper.

The first couple of months how it has gone…

The solution has been working well for a couple of months now, fully hands off, stock is merrily moving between companies with no admin or user intervention. Very satisfying solution indeed!

I’ve cut out some of the detail of this implementation as it is very specific to how GP is ran in our environment, but feel free to leave me a comment if you would like to know more about any aspects of this solution.

Windows 7 Start menu search not working

Searching from the start menu resulted in no results

Searching from the start menu suddenly stopped working on one of my machines recently. Only when it stopped working did I realise how much I had started to rely upon it to find programs, configuration wizards and such like.

Trawling the forums unearthed many suggestions. Often the suggestions were to solve similar symptoms but did not resolve the issue I was experiencing. Starting to search would show ghosts of the category headings but not results.

You can see this in the screen shot below.



The solution that worked for me is the last one I give here but the journey can help find all the touch points for search.

Check Customize Start Menu options

If it is merely the programs are not showing up then use the customise start menu, right click start menu>properties>customise. Ensure the search program files menu option is selected as shown;


From the control panel it is possible to manage the search index

Use the windows search trouble shooter

There are many trouble shooters found here;

Control Panel\All Control Panel Items\Troubleshooting\All Categories

Amongst them is a search and indexing trouble shooter. Give it a try, it reported no problems for me even though obviously something was up.



Check search is enabled as a feature

From Control Panel\Programs\Programs and Features go to windows features. This allows search as a feature to be turned on or off. Try turning off, reboot then turn on, reboot.



Rebuild the index

By going to the search options you can click the advanced button and then rebuild the index.


Finally fix the registry

In my case it was the following registry fix that worked.

Navigate to the following registry key;


Find the value GroupBy and if it is System.Null,  change it to value System.StartMenu.Group.

Killing explorer.exe process then re-launching it or rebooting will now allow the start search to work!

Sagepay Access protocol for .NET (VB.NET) using LINQ

Sagepay are a card payment provider. There is a new Sagepay API to manage the actions that are normally accessed through the account management web portal “MySagePay”. At time of writing it is in restricted Beta. As it is not open I feel unable to disclose everything about how it works, but if you are using the beta service with .NET then parts of my wrapper may be useful to you.

To get in on the Beta, go into the Sagepay website Access forum and PM Joe in support who will send you the API documents. If you want my .NET wrapper for Access then I don’t mind sharing but would need to confirm with you that you have been given the Beta documentation first.

LINQ to XML seemed like the best way forward so here goes;

Prepare the XML

I’ve left out the command name, you can replace it if you have the API documentation as you will know what it is. For each call in the API you start with the same XML fragment then append the relevant parameters to it.

Public Shared Function lockUserXMLRequest( _
 ByVal VendorID As String, ByVal UserName As String, ByVal Password As String, _
                      ByVal lockusername As String) As XElement
    If lockusername.Length > 20 Then Throw New ArgumentException("username must be 20 characters maximum", _
         "lockusername", Nothing)
    Dim SagePayRequestXML As XElement = New Credentials("[commandname]", VendorID, UserName, Password)
    SagePayRequestXML.Element("user").AddAfterSelf(<username><%= lockusername %></username>)
    Return HashRequestxml(SagePayRequestXML)
End Function
The credentials are passed in for each request as an xElement;
Public Class Credentials
    Inherits XElement
    Public Sub New(ByVal Command As String, ByVal VendorID As String, _
                   ByVal UserName As String, ByVal Password As String)
        If UserName.Length > 20 Then
            Throw New ArgumentException("UserName maximum of 20 character", "UserName", Nothing)
        End If
        If VendorID.Length > 16 Then
            Throw New ArgumentException("VendorID maximum of 16 character", "VendorID", Nothing)
        End If
        If Password.Length > 32 Then
            Throw New ArgumentException("Password maximum of 32 character", "Password", Nothing)
        End If
        Me.Add(<command><%= Command %></command>)
        Me.Add(<vendor><%= VendorID %></vendor>)
        Me.Add(<user><%= UserName %></user>)
        Me.Add(<password><%= Password %></password>)
    End Sub
End Class

Hash the request

Once you have the XML it is hashed with your vendor password, the password must be removed before the XML is submitted to Sagepay;

Private Shared Function HashRequestxml(ByVal RequestElement As XElement) As XElement
    ''Is there a better way to convert xElement() to string?
    Dim sb As New StringBuilder
    For Each currentElement In RequestElement.Elements
    ' Get the "inner xml" of the element
    Dim oReader = RequestElement.CreateReader()
    Dim asstring = oReader.ReadInnerXml()
    'Hash the request and password element together and use the result
    ' as the signature
    Dim signature As XElement = _
            <signature><%= getMd5Hash(asstring) %></signature>
    'Now remove the password, not required now hash generated
    Return RequestElement
End Function

Note: A post in the Sagepay forum indicates that the final Access may be significantly different to the current one in use. See: Our improved Reporting & Admin API is coming...

LINQ SQL innerxml equivalent

A note to myself, if you have an xElement and want to get just the inner xml then this is the way to do it:

' Get the "inner xml" of the element
Dim oReader = RequestElement.CreateReader()
Dim asstring = oReader.ReadInnerXml()