March, 2010
One of the problems we run into over and over again here at Blue Dot is how to handle data that is created on a device that is not connected to the server or source system. At first glance, this doesn’t seem like a big deal but let me walk you through the complexity and my most recent revelation on how to gracefully handle this situation.
We build lots of apps that interface with ERP (Enterprise Resource Planning) systems. These systems all have the concept of work orders and our software allows the ad-hoc creation of new work orders out in the field on a mobile PC with no direct connection to the ERP system. Now when you create a work order in this disconnected mode you can’t give it a real identifier because only the ERP system can generate those so you have to give it a temporary one. When a network connection does become available and the application synchronizes this new work order with the server systems and a new record is created in the ERP system; a new id is assigned to that record. The problem is that in this disconnected sync scenario the device isn’t sitting around waiting for the servers to process all this data, it simply syncs and moves on with life. Sometimes creation of a entity like a new work order can take a while because someone else actually needs to validate it or re-assign it, etc. So the device simply sends it’s data and then disconnects, allowing the user to go on with his/her job. Now let’s pretend a few things happen. 1) The mobile user remembers that they forgot some important piece of information on this work order, so they add/edit it and 2) someone in the back office changes a few things on this new work order. Now what happens when the device syncs again? Things start to get interesting. First of all you would have to have some way to connect the work order as identified by the device with the work order as identified by the ERP system (both have different ids). Secondly you have a situation where this single record has been edited in multiple places. Finally, the complexity increase as you start talking about child records on this work order and ids associated with those child records and their relationship to the parent work order.
In many past applications we have simply avoided the problem by flat out not allowing this kind of functionality. This can be done by not allowing edits on the work order until it goes ‘round the horn’ or by having the creation of new work order function more like sending an email in that once it leaves your out-box it’s gone. You can send another, but you can’t edit it again. These approaches work, but users have the expectation that the disconnected system should behave just like the connected systems and frustration often arises.
So for this most recent project that I’m leading I decided to take a fresh approach and see if I could make all this transparent to the user, offering the functionality to create new objects on the disconnected app, synchronize them, edit them, etc - operating just like you were interacting with the ERP systems real time interface. Here’s how it works:
Editable objects (like a Work Order) have unique identifiers as their primary keys. This allows us to create new objects on any number of mobile devices without running into conflicts.
These editable objects contain a field for the ERP systems primary id and this field is always expanded to something like 50 characters (enough to store a guid).
When a new object is created on the device the ERP primary id (which we don’t know yet) gets set to the same value as our unique identifier. This allows child objects to be created/associated etc.
When a sync happens the integration tier creates the new objects in the ERP system and then gets back the ERP’s identifiers for those objects, storing them in the field we have for that purpose. This all happens server side and now we are in a situation where we have a self contained mapping between our identifiers for this object and the identifier used by the ERP system.
As the device makes edits and submits more changes, we reconcile and send back to the device the ERP identifier, but our unique id stays constant. On the server end if we ever get data from a child record that has one of these guids in the ERP system id field for it’s parent object we know how to look up that parent using the mapping in our integration tier in order to submit that child add/edit to the ERP.
Eventually the mappings are pushed back down to the device during the sync process and those newly created work orders start to behave just like work orders that originated in the ERP system.
So far this seems to be working amazingly well with very little friction. I’m using the sync framework 2.0 as my synchronization layer with snap sync as the transport and integration service bus.
