test company, test database, live company in test database, test company in live database…

How times have changed. Now we have a SAN and ESX Hypervisors hosting our servers, virtualisation and new hardware platforms have made managing enterprise infrastructure so much easier. For developers this had made possible the cloning of live environments, yes webservers, databases, ERP and various integrations can all be plucked from operational service and plonked down into a test environment.

This means testing and development can take place in environments that are almost identical to production live environments. Importantly I said almost identical – users can log in and test scenarios and perform UAT, performing end to end UAT scripts without interruption. Years ago they would have had to test one section at a time and everyone would pray when it was all stitched together and launched into the production environment that it would hang together. The confidence factor from testing is increased as a consequence of the improved environments and this mostly makes for safer deployments.

As developers we have almost identical copies of the production environments to work with, whilst so helpful It introduces new hazards and risks. We noticed just how much time is spent orientating every conversation, prefacing it with what version of the various systems we are talking about for fear of misunderstanding. Much akin to the header on information packets. It is risky when production and live database are open in SQL server, oh so easy to run a script against the wrong system. I am happy to say I’ve never taken GP down by running SQL against it.. yet, but when we changed to this arrangement I became very aware of how easy it is to make a mistake now. When it was a test company we were developing in, then the connection was obvious. The problem is when both databases look the same including the name and only the server is different then it becomes all to easy to make mistakes. It becomes essential to keep an eye on the connections shown in by hovering over the tabs of the SQL pane.

I also use an add in for SQL server management studio, SSMS Boostthat has recovered many scripts from loss during system crashes or simply discarding a script I wish I hadn't.  It keeps a history of everything in SQL management studio, saved or not and lets the user go back to scripts ran days ago. It also provides search for that history too- highly recommended. Anyway I am off track, this add in also has the facility to colour the connections on the task bar at the bottom. This can be another helpful aid if orientation when working between multiple connections between live and test website and GP databases.



GP Power Tools (Support Debugging Tool)

It is also dangerous for users of GP, using the GP client and developers working between the clone environment and production. Even with explicit warnings about not entering genuine orders into the test system, it only took a day for someone to do it! Luckily it was only one order and not a whole day’s worth.

The GP power tools gives us the ability to colour windows in GP according to the company database it belongs to. This provides a visual cue to the user to instantly recognise which company the user is working in. See the screen shot for the configuration of this feature. I don’t know if the newer power tools is exactly the same, but with this version, it cannot differentiate between the database server the company is running on. Hence we cannot use this program to identify the difference between servers.


Visual Studio Tools

Due to the accidental use of the clone server, it became apparent we needed something to tell the difference easily, when users are logged into GP in the clone environment. We used the same idea of colouring the user interface depending upon the company, but instead used the server name as the identifier. The solution is implementing as part of the Visual Studio Tools plugin we have written. It takes advantage of the same Dexterity calls (Color_SetSystemColor) as shown in the references that follow, pay particular attention to “Colours Depending on Company.zip” in the VBA example.

We see here how the bar is coloured, this is enough to know we are in danger.


There is still the problems of multiple test and production GP companies, servers and databases, but at least when in GP it is clear which connection is currently in use from the user interface. Thanks to Michael on the team for developing this solution.


Differentiating Companies in Microsoft Dynamics GP

Hybrid – Changing Screen Colours Depending on Company Example

Dynavistics acquire rights to Trinity (m-hance) source code

The news article linked below reveals that Dynavistics have acquired rights to develop the Myridas products that were acquired themselves by m-hance when they absorbed Trinity Computers during the m-hance acquisition run that achieved a 45% sales growth for the m-hance in 2012.

Dynavistics Acquires the Right to Develop Trinity ISV Applications for Microsoft Dynamics ® GP, for the Americas from its UK Partner, m-hance Limited

Is this a good move?

Dynavistics have distributed the products for five years, developing a strong US user base. Through this history they have gained a good understanding of the product suite and customer requirements. No doubt, from this, they have a good idea where they are going to take the product suite for its next phase.

Module list as it stood:

 myridas module screenshot

Dynavistics are planning to create a Dexterity developer team in Tampa Florida USA, to develop and support the products going forward, a sure sign of commitment. Dynavistics have had success with sales of the Myridas suite of products over the last few years, hence it makes total sense for m-hance to licence the intellectual property to Dynavistics, with a proven record. Geographically positioned in the perfect location, within the largest market for GP Dynavistics become custodians of the product for the foreseeable future. Due to their focus on GP it is certain the we will see some interesting output in the near future that will benefit both companies.

This news must be a positive for the m-hance products, ensuring they have a secure future in trusted hands thus allowing them to flourish. I expect developments to include updates to allow compatibility with the newer cloud based architectures that are going to be common in new installs of GP with the improved web client arriving in GP 2016.

Enabling Dynamics GP Modules

It can be confusing where in the GP user interface that functionality in GP is enabled from. It can seem like there is no consistent place to go in order to enable functionality. In this post I’m quickly just going to show some examples and leave it at that…

Complete modules tend to be switched on and off through the registration page, where the administrator should use the checkbox found next to the module name, to make it available. After changing status it is often bets to log out of GP and back in again afterwards.

Microsoft Dynamics GP >> Tools >> Setup >> System >> Registration


A feature such as multiples bins can be enabled within a module setup window, such as shown below.


Sometimes both the registration page and the individual setup checkboxes (or wizard) has to be used, for other modules or features, only the registration page is required, yet other times only the setup for the feature needs executing. This is why it gets so confusing!

Here are some examples;

Enabling Advanced Distribution

To enable Advanced Distribution in Dynamics GP check the box next to its name. Then configure the module.

Enabling Advanced Picking

To enable Advanced Picking in Dynamics GP check the box next to its name. Then use the Advanced Picking Setup.

Dynamics GP >> Tools >> Setup >> Sales >> Advanced Picking Setup


Enabling Service

To enable Service Call Management in Dynamics GP check the box next to its name. Then configure the module.

Enabling Extended Pricing

To enable Extended Pricing in Dynamics GP check the box next to its name. in the registration window, then go to the Enable Extended Pricing window to enable it. If the “Enable” option is greyed out then the registration check box has not been set.

Dynamics GP >> Settings >> Sales >> Extended Pricing


There is a lot more to configuring these examples modules fully. I am merely trying to demonstrate the variety of ways in which functionality can be enabled, full setup requires reading the module user guides.


Finally there are some tools for GP that may require the setup window adding to the short cuts. This way the setup window can be opened, often to click a setup button on that window!image


When working with GP for sometime, familiarity with the product helps form intuition on where to locate the various controls for modules, but for those less familiar it can be baffling!

Solution to eConnect Requester SOP Transaction trigger not creating output records

eConnect uses a Document Requester system to allow database activity in GP to be tracked, say for the creation of sales orders. It also allows the database to be queried via the eConnect API, without any logging to output tables.
Steve Endow has written up here on his blog, 8 year old bug in eConnect Requester SOP Transaction trigger an issue. Essentially the eConnect_Out table was not receiving any records when new sales orders were created, even though the  SO_Trans Insert trigger was enabled.


The issue boiled down to the auto-generated code for the trigger generated by the default requester that is “supplied with” eConnect, inappropriately making CUSTNMR a required conditional field on order record insert. Steve correctly identified that as the CUSTNMBR field is initially empty in the SQL table and is actually updated after the record is inserted. As the CUSTNMR is updated rather than inserted this is why the requester fails to insert a record in the output table. See his post for full details and a better explanation.

I’ve worked with eConnect requesters a for a long time and can offer a suggested way I would solve this issue.

eConnect provides a executable utility shown in the screenshot above, the Requester Setup Tool that allows administration of the provided output document types. I have the feeling that this utility did not exist when I started using eConnect (back many years ago), or perhaps I never noticed it, so I have always just gone right to table to change and create existing or new requesters. This puts me at an advantage for this issue as I know how requesters work.

The utility is actually merely manipulating rows in [dbo].[eConnect_Out_Setup] table. SQL triggers on this table do the clever task of creating other triggers against the relevant tables that we seek to monitor. Looking at the row for the SO_Trans record, we see the following in the setup record;


Note that CUSTNMBR has be defined by GP as a required by having CUSTNMBR in the REQUIRED1 field. This is why it is being set in the trigger as required and so why it fails to create the output record when a initial record is inserted into the database with no CUSTNMBR value.


To solve the problem we simply create a new output definition record that does not has a that "CUSTNMBR" valued in REQURIED1 the field. The design of eConnect is such that integrating applications can access the setup table directly to subscribe to what they need in GP.

To achieve this, script out the data from the original row setup row, give it a new alias name and remove the field in the REQUIRED1 value.

Insert this record into the [dbo].[eConnect_Out_Setup] table (in production the application can do this on install or start of the application). The insert causes a new trigger to be created against the sales table SOP10100, this time without that required field. Do a test, now creating a new sales order will correctly create a record in the output table.

Below is the row to insert using  Sales_Transaction_All as the requester name and SO_Trans_All as the alias (change these names to something that won't be used by any other application in real production deployment:

INSERT [dbo].[eConnect_Out_Setup] (
N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '
,N' '

The new requester row creates the associated insert trigger against SOP10100 as shown below.


The trigger contains the following SQL, note now no required field any more!

/****** Object: Trigger [dbo].[eConnect_Sales_Transaction_AllSOP10100_InsertTrigger] Script Date: 02/03/2016 15:11:01 ******/


CREATE TRIGGER [dbo].[eConnect_Sales_Transaction_AllSOP10100_InsertTrigger] ON [dbo].[SOP10100]
DECLARE @required CHAR(50)

DECLARE eConnect_Sales_Transaction_AllSOP10100_InsertTrigger CURSOR
SELECT convert(VARCHAR(50), SOPNUMBE, 121)
,convert(VARCHAR(50), SOPTYPE, 121)
FROM inserted a


OPEN eConnect_Sales_Transaction_AllSOP10100_InsertTrigger

FROM eConnect_Sales_Transaction_AllSOP10100_InsertTrigger

IF (
FROM eConnectOutTemp(NOLOCK)
WHERE DOCTYPE = 'Sales_Transaction_All'

SELECT @DRI = isnull(DEX_ROW_ID, 0)

IF (@DRI > 0)
DELETE eConnect_Out
FROM eConnect_Out b(UPDLOCK)
b.DOCTYPE = 'Sales_Transaction_All'

INSERT INTO eConnect_Out (
SELECT 'Sales_Transaction_All'

FROM eConnect_Sales_Transaction_AllSOP10100_InsertTrigger

DEALLOCATE eConnect_Sales_Transaction_AllSOP10100_InsertTrigger

The new requester type also now shows up in the setup tool as expected for future editing (might need to reconnect to refresh).


Finally here we have it, the output record in the output table,


Note that this record in the output table is created immediately that the order is created, but before it is saved. Beware acting on this until the integrating application can see the key fields of the order are complete, perhaps check the dexterity lock tables too, thus to ensure the editing is complete. Maybe this was the misguided reason that led the CUSTNMBR to be included as a required field originally?

In reality if it is merely a flag for created orders that is required, for the setup record, only SOPNUMBE and SOPTYPE are worth capturing as data fields, the rest are mostly blank anyway.

Also note that the user could click the [x] and close the order, and the SOPNUMBE will be returned to the stack and could be used again with a future order. The integrating application has to handle that too.


Although eConnect installs some example requester documents to get users going, additional requesters can be added to meet specific requirements. eConnect requesters are configurable and quite flexible, however this requires direct editing of the setup table.