Smart Connect for Dynamics GP Webinar

I attended a webinar today hosted by Touchstone’s Dominic Houlbrooke-Bowers, about Smart Connect for Dynamics GP

Summary

There are two ways to summarise this product from this demo (i’ve not yet read more);

Replacement for integration manager using eConnect to enforce the busines rules, providing a richer and much faster experience than integration manager.
OR
A power user graphical user interface for eConnect

It has some very powerful extra features too like  push from internet placed excel or info path forms to web services and into GP.

Product description

More...



UPS World Ship XML Import from Dynamics GP

Problem

The Sales Order Fulfilment (SOP) software I wrote for Microsoft Dynamics GP, deals with multiple Dynamics GP companies. You can choose the company and fulfil any available pick lists for sales orders or invoices from that company. The software implements a least cost parcel routing engine. If it decides to route to UPS or TNT carriers we wish the software to ensure that that carrier invoices the correct originating company, avoiding the necessity for intercompany transactions in the accounting system to apportion costs.

The parcel details are sent to the UPS World Ship software as XML files, generated from an XSLT against the order tables SOP10100 and SOP10200 and several custom tables for pick lists and despatch records.

History

The XML Import has been something we have used for many years, since World Ship 5.0, I think we are on 11 now. However when we enquired about having multiple shipper billing accounts it was not possible without a separate instance of world ship for each carrier account. It was something that was mooted as a feature of the next version.

Last Week

Time has moved on and UPS upgraded us recently to the current version of World Ship, so I revisited the problem.

I investigated the Openshipments.xdr schema file for the new version of World Ship to see what had changed. I found a new element definition as shown below for the shipper UpsAccountNumber, i.e. the place the cost for the shipment should be invoiced to.

<ElementType name = "Shipper" content = "eltOnly" order = "seq" model = "closed">
<element type = "UpsAccountNumber" minOccurs = "0" maxOccurs = "1"/>
</ElementType>

I then changed our XSLT to add a node in our outputted XML, and a parameter to pass into the XSLT so that I may pass the UPS account number to the XSLT.

<Shipper>
<UpsAccountNumber><xsl:value-of select="normalize-space($AccountNo)"/></UpsAccountNumber>
</Shipper>  

After ensuring the accounts were set up and activated in the Shipper Setup of World Ship, we tried it out, thankfully it just worked, the consignment was generated against the company for who the order had been fulfilled.

TNT

We also use the auto import utility with TNT Express Shipper. In this case it is a fixed length text file import, but I applied a similar upgrade for TNT with our technical representative a couple of weeks ago. This results in the account number getting passed and mapped in the TNT software so that again the correct company gets the invoice.

TNT Errors

Another advantage of the new TNT software is that it passes back the consignment number and any errors that have occurred during the import, so it does a full handshake – like UPS have done for a long time. This makes my life easier as I don’t have a job running anymore to pluck the consignment numbers from the TNT SQL database in order to populate the Dynamics GP tracking number tables in Dynamics. As it happens I’ve started using our own consignment numbers now so GP is the source of the consignment number, but having access to errors allows us to correct address records in Dynamics GP that consistently throw exceptions in Express Manager. These exceptions are usually things like town in wrong field or a post code update making a post code invalid that was entered in the past in GP. Mismatches can occur between the versions of post code PAF file on GP and on TNT.

Conclusion

My fulfilment software now passes the account numbers for the carriers through depending on the owner company of the order in GP. This account number is used by TNT or UPS to assign that consignment to the correct company for invoicing, that in turn makes the accounts department happy as the billing gets easier.

Next…

It was also mentioned by TNT that they can now provide electronic invoices, UPS have done for years but this means it is now worth importing the actual cost of carriage back into GP so we can get a better analysis of margin for individual orders.

WaitOne while I introduce multithreading

For some time I've been meaning to do some multi threading in the Dynamics SOP Fulfilment software I wrote. I have always thought that I could make things run a little more slickly. At fulfilment the following processes occur;

  • Credit/debit card debited if one is attached to the order
  • Order lines are fulfilled by quantities on the pick list being fulfilled
  • receipt printed (optional depending on carriage option- customer collect etc)
  • despatch note printed (optional)
  • invoice printed (optional)
  • thermal parcel label(s) printed one for each parcel entered
  • consignment details are exported via XML to UPS / Business Post / TNT depending on carriage routing algorithm
  • UI is constantly updated with progress bar and messages about progress

Spinning the fulfilment thread off on to its own thread using a background worker control was straight forward and is something I did when VS2005 was released. However some of the above processes take quite a few seconds to run, the fulfilment computers are quite recent hardware and thus should be able to perform better.
Thanks to good object orientated design, it turned out to be fairly trivial to get each of these processes to run on their own thread and synchronise them using wait handles. I had to use wait handles as some tasks depend on the successful completion of others. More...

Concurrent user log for Dynamics GP

A few months ago we kept hitting our seventy five concurrent user limit on our GP server. Rather than rushing out to buy some more licences it was time to investigate how it was getting used.

In order to do this a SQL job was set up sample the current concurrent users on the system throughout the working day, logging the results into a SQL server table for later analysis.

I was not too interested at this point in the detail of who and where people were logged in, merely how many, thus a few mins later we have created a table in the BI database, UserConcurentCount that has two columns, UserCount and TimeDate.

The sql job created had one step running every 10 mins that did the following:

/* Insert into the monitot table how many users visiting */
Insert into BI.dbo.UserConcurentCount
 (usercount,[datetime]) 
(select Count(*) as UserCount, getdate() as SampleTimestamp from dynamics..activity)

This results in a table that can be used to build a histogram using a bit of TSQL and excel of the number of users in the system through the day. The problem seems to be with the changing patterns of work in a small number of the part time staff in some departments causing a pinch point when change over half way though the day occurrs and both sets of staff are in the system. The problem now seems to be resolved and we hover under the user limit now.

Take care to put a clean up script on a weekly job clean old entries out of this log table, no need to build up excessive records unless it is useful to you.