We use cookies to improve your experience on our website.

  • Home
  • Blog
  • Sales Order/Invoice Line Items Missing? Here's the Solution! Scribe GP to CRM Integration.

Sales Order/Invoice Line Items Missing? Here's the Solution! Scribe GP to CRM Integration.

If you are currently using the out-of the-box Scribe Dynamics GP to CRM template, you might be experiencing issues with missing sales order/invoice line items in CRM.  The problem happens sporadically for no apparent rhythm or reason.  There is, however, a reason for this behavior...and there's even a solution!

Before we get to the solution, let's take a look into what's going on in Scribe and what's causing the issue.

When the Scribe Dynamics GP Adapter is first installed, it creates a table called SCRIBEPUBLISHERQUEUE in the GP company database. It also creates insert/update/delete triggers on certain source tables (e.g. SOP10100). These triggers effectively just write a row into the SCRIBEPUBLISHERQUEUE table every time an insert/update/delete is performed on the source tables. The Dynamics GP Adapter then uses the publisher queue table to create and publish messages to the message queue.

The integration processes that come in the template look like the image below. In order to update the sales order/invoice lines items, they first delete all of the lines and then recreate them. Presumably this is done to avoid having to deal with matching up specific line items between GP and CRM (which could be challenging if the data changes in GP).

scribe gp to crm process

It might not be immediately obvious why this approach causes the issue with missing line items, so let's explain. Since the trigger on the SOP10100 table creates a record for every insert/update, if a specific sales order/invoice is updated twice in a short period of time (this frequently happens with warehouse management software), two rows will be written to the SCRIBEPUBLISHERQUEUE table. These two rows will be picked up by the Dynamics GP Adapter and two messages will be pushed into the message queue. Since Scribe can run multiple message processors at a time, it is possible for these two messages to be processed concurrently. While one integration process is busy creating the line items in CRM (Step 4), the other integration process could be busy deleting the line items (Step 3)! This race condition, a classic case of the "time of check/time of use" bug in computer programming, is what can cause the missing line items!

What is the workaround?

Rather than using the default Scribe instance, create custom triggers on the GP tables to push inserts/updates/deletes into the SCRIBESHADOW table. Then, create custom Query Publishers and update the Integration Processes to work with the new message format.

This process is more work and planning than using the default Scribe integration, but doing so will prevent items disappearing from the SOP documents in GP.

Bob Guidinger

Senior Technology Consultant

Do you want to turn your system requirements into world-class user experience?  Then Bob is your guy.   His expert talent is understanding business needs and turning them into usable systems that users cannot wait to use.   Whether he is designing and building SharePoint or CRM workflows, ERP add-ins, Data Warehouses or a document approval and imaging system, there is no question – it will be the most user friendly application possible.

Bob’s is an undisputed expert in .Net, SharePoint, MS SQL Server and CRM.   His specialty is turning business requirements into functional processes by either utilizing off-the-shelf applications such as Dynamics CRM or custom developing processes in any language needed.   He is especially skilful in integrating multiple applications with whatever tools appropriate. 

Recent Projects

In a recent project, Bob developed a complex Dynamics CRM schema to meet the needs of 7 separate departments for a financial services firm.  Each department had their own unique streamlined user interface but the data that was shared between departments was available to the appropriate teams.   The departments ranged from marketing to product deployment, with contracts being created directly from within CRM as part of the sales process.  To top it off, Bob integrated business intelligence into the solution so management now has instant metrics that formerly took 12 hours per week to produce.

Another one of Bob’s projects was the integration of a complex Dynamics CRM system for a national collection agency.  CRM workflows trigger workflows and data updates in multiple systems simultaneously.    In this particular setup, Dynamics CRM is the system of record, but by enabling multiple technologies, all data in all systems is always in sync, which gives management complete confidence that their data is accurate and complete.

1000 Characters left