Prior developers and others who should have known better at my company decided many years ago that our warehouse people simply could not do their jobs unless we unleashed the ability for them to use multiple "bin" locations for each item. Now, strictly speaking, this was not really the case, because our needs simply aren’t that complex. We manufacture reading glasses so we don’t need inventory aging and all the warehouse people really needed were different sites to separate receiving from QA from shipping. In other words, we have product staging, but each stage is physically as well as conceptually separate and each item in each stage exists really only in a single location.
As in so many real-life situations, however, there is enough cruft built up around the current process that it is simply not worth expending the political capital to change. Which means that I’m stuck trying to automate order fulfillment for multiple bins with multiple locations.
The Tools
Now, Dynamics has a number of interesting tools and my company long ago purchased all the neat bits that I need to use them all. The typical default tool that people use to customize Dynamics is a hoary old beast called "Dexterity". I’m sure that the name once fit, but in an honest world the name would have long ago been changed to "Decrepit". I’ll use Dexterity if I absolutely have to, but I’ll avoid it more assiduously than, say, rabid ninja porcupines.
Fortunately, Microsoft, feeling their oats with cool technologies like the .Net framework and web services and all, has released developer tools that includes a .Net toolset to create addins and a set of web services that can be used to perform most business tasks. I also have the Database schema if I have to resort to direct table access, but I tend to avoid that if I can (though not at the rabid ninja porcupine level).
There are some important things to know about these tools.
.Net Integration
The .Net integration can hook into Dynamics forms and events and play with field values. You can also create your own forms and hook them into a special "Extras" menu when a given Dynamics form is active (or have them pop up during an existing event handler I suppose).
They didn’t skimp on the event handles, either. Each field has before and after change and click events, buttons have before and after click events, and forms and windows have activate, open, close, and print (all with before and after handles).
Web Services
The web services are more complicated to set up and are best off installed on your database server. They use some of the older E-Connect com objects in the background which makes it a pain to set up, but they’re pretty useful once you do.
The web services expose a large number of create, update, get, and delete methods for all the stuff you’d expect—customers, invoices, sales orders, purchase orders, that kind of thing. They can be a bit temperamental when it comes to setting up the policies for the operations, but once you’ve got them working, they work well.
The Problem
When we receive orders from Nordstrom, we have two factors that complicate our work flow. The main difficulty is that printing our shipping and invoice labels is a fiddly piece of work that can result in fines from Nordstrom if we get it wrong. Some long forgotten "genius" managed to get them formatted correctly using our EDI software. This is the software that is still using FoxPro as their interface and data storage. It’s a hunk of junk, but it works. More or less.
Unfortunately, for those labels to print correctly, the EDI software has to do the Dynamics import itself (instead of using my cool new EDI processing service). If the EDI software doesn’t do the import, it doesn’t know all the right invoice ids and associated stuff to print on those labels.
Also unfortunately, the EDI program knows nothing about our multiple bin fulfillment requirements. Adding to the pain is the fact that Dynamics cannot handle automated fulfillment for multiple bins on its own—particularly when certain sites are to be allowed, others avoided, and one or two bins excluded manually (yeah, we did all that to ourselves).
Since we get orders for hundreds of items at a time, this largely manual process for Nordstrom is taking a single very kind and patient person (who may or may not ever read this) about four hours. The bulk of this time is manually assigning bins to all the items on each order in the batch.
My Solution
We’ve been working on making all of our customizations accessible within Dynamics, so it’s best if an automation solution kicks off from there. The .Net tools make this part relatively easy.
public class OrderFulfillmentAddIn : IDexterityAddIn
{
// IDexterityAddIn interface
public void Initialize()
{
Dynamics.Forms.SopBatchEntry.AddMenuHandler(AddOrderFulfillmentMenu, "Allocate Bins");
}
static FulfillmentForm form = new FulfillmentForm();
static void AddOrderFulfillmentMenu(object sender, EventArgs e)
{
if (form == null || form.Created == false)
{
form = new FulfillmentForm();
}
form.BatchId = Dynamics.Forms.SopBatchEntry.SopBatchEntry.BatchNumber.Value;
form.Show();
form.Activate();
}
}
This inserts an "Allocate Bins" menu item on the extras menu when the Batch Entry form is open. The menu item opens my own form (which isn’t any great shakes). However, because the .Net integration tools are only able to handle user-initiated events and already open form fields actual bin allocation has to happen some other way.
Yeah, I chose to use the web services.
Complicating this further, the web services don’t expose anything that will give me all the bins that contain a specific item so I’ll need to pull that information from the tables directly. Fortunately, Dynamics has a handy Resource Descriptions tool that helps here.
It can take getting used to, but it tells me that I need table IV00112.
With that information, this becomes a simple Linq query.
You Want Tests With That?
Now, this is where things get tricky because I like to test my code once or twice before deployment. Oh yeah, and sometimes I’ve even been known to create unit tests and such. Which means that I have to be able to run this in both our test company as well as the live one.
Since Dynamics keeps each company in a separate database, this means that I need to know both the Company Id to feed the web service and the database name for the Linq query. This actually held me up for quite a while. My first impulse (that actually worked out relatively well) was to put both into the app config.
Linq already has it’s connection string there so I’d just need to add a CompanyId setting.
<connectionStrings>
<add name="Integration.GPWebServices.Properties.Settings.myConnectionString"
connectionString="Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<applicationSettings>
<FulfillOrders.Properties.Settings>
<setting name="CompanyId" serializeAs="String">
<value>2</value>
</setting>
</FulfillOrders.Properties.Settings>
This worked well on the surface, but it was kind of clunky when it came to developer testing where I went into the test company within Dynamics and created batches and orders with the Visual Studio debugger attached.
That’s when the apple fell out of the sky and I realized that, duh, I have the Dynamics environment right here already. It took me a while to figure out that the database name is held in the "IntercompanyId" global property, but once I’d tracked that down, we were golden.
Orders.FulfillByBatch(BatchId
, (int)Dynamics.Globals.CompanyId.Value
, Dynamics.Globals.IntercompanyId.Value);
The unit test can still pull it from the config file, but my form can simply use the company currently open.
Doing the Work
I’ll give you my worker class just for kicks here. Note that allocating bins for hundreds of items takes long enough that we definitely need UI feedback (we love thee oh progress bar). That means that much of the class real estate is taken up with events that help keep my users from freaking out.
using System;
using System.Collections.Generic;
using System.Text;
using Integration.GPWebServices;
namespace FulfillOrders
{
public static class Orders
{
public static event EventHandler OrderProcessed;
public static event EventHandler<BeginFulfillingEventArgs> BeginFulfillment;
public static void FulfillByBatch(string OriginalBatchId, string NewBatchId, DateTime CancelDate, int CompanyId, string CompanyDb)
{
Utility.DataBase = CompanyDb;
Utility.CompanyId = CompanyId;
SalesDocumentSummary.BeginLoadingSummary += new EventHandler<BeginSummaryLoadEventArgs>(SalesDocumentSummary_BeginLoadingSummary);
SalesDocumentSummary.DocumentLoaded += new EventHandler(SalesDocumentSummary_DocumentLoaded);
List<SalesDocument> orders = SalesDocumentSummary.GetSalesDocumentsByBatch(OriginalBatchId);
if (orders.Count > 0)
{
OnBeginFulfillment(orders.Count);
foreach (SalesDocument order in orders)
{
order.BatchId = NewBatchId;
order.Date = order.UserDate1 = order.UserDate2 = order.ReqShipDate = CancelDate.Date;
order.AllocateBins();
order.CommitDocument();
OnOrderProcessed();
}
}
}
static void SalesDocumentSummary_DocumentLoaded(object sender, EventArgs e)
{
OnOrderProcessed();
}
static void SalesDocumentSummary_BeginLoadingSummary(object sender, BeginSummaryLoadEventArgs e)
{
OnBeginFulfillment(e.TotalOrders);
}
private static void OnBeginFulfillment(int total)
{
EventHandler<BeginFulfillingEventArgs> begin = BeginFulfillment;
if (begin != null)
{
begin(null, new BeginFulfillingEventArgs(total));
}
}
private static void OnOrderProcessed()
{
EventHandler processed = OrderProcessed;
if (processed != null)
{
processed(null, new EventArgs());
}
}
}
public class BeginFulfillingEventArgs : EventArgs
{
internal BeginFulfillingEventArgs(int total)
{
Total = total;
}
public int Total { get; set; }
}
}
Wrapping it Up
Integration.GPWebServices is my own library and it uses shell classes to simplify working with the GP web service objects (including the static Utility class used to store the Company Id and Database).
Enlarge to see it in full, including the nifty methods used to put the web service objects together.
Isolating the web services also means that the WCF objects are fully encapsulated and I don’t have to worry about referencing those libraries or be concerned when they cross app boundaries. It does mean that I have to be careful with the app.config files that contain my WCF endpoint connectivity, however.
Integration.GPWebServices probably deserves it’s own post. It’s certainly large enough and it is getting relatively well polished at this point. Also, it has become great personal example of the benefits of ruthless refactoring in a case where the initial design was solid to begin with.