Friday, September 10, 2010

What are all those GPSFOINTEGRATIONID columns in some tables

I thought I would close the week with a very little known fact to new comers (and some not so new) to the Microsoft Dynamics GP world.

First some history

Around mid 1998 (or so), Siebel Systems formed an alliance with the then Great Plains Software Inc. to deliver a suite of front- and back-office applications. Great Plains' back-office applications included an integrated suite of accounting, financial, and supply chain modules. Under the agreement the new suite would add Siebel's front-office applications covering sales, marketing, and e-business functionality. The combination would allow users to complete sales transactions over the Web, for instance. Great Plains delivered the first component of its new package in November 1999 as the Sales and Marketing Series of Great Plains Siebel Front Office, with customer service and call-center applications to follow in 2000. The suite was aimed at small and mid-sized businesses.

The new Great Plains Siebel Front Office product would initially offer a module to automate sales, marketing, service and electronic business processes. With another module for customer service following in January of 2000, this was the first step toward overall front office/back office solutions.

Fast-forward to April of 2001, Microsoft completed its acquisition of Great Plains and soon began working on other plans to phase out GPSFO, pushing overall its .NET technology and its vision for a .NET based CRM solution. As a result, Microsoft CRM 1.0 was released in January of 2003... the rest is, well, history!

So, how is history related to the title of this post?

So now that you know that your beloved Microsoft Dynamics GP used to play some serious game with Siebel, it's just about right that they had to have some way of talking back to each other, this is, integration.

As an integration mechanism between GPSFO and Great Plains Dynamics, the Dynamics dictionary (DYNAMICS.DIC) went through a few changes required to accomodate integration points between the two systems. Today, there are still vestiges of these changes in the SOP10100 (Sales Transaction Work), SOP10200 (Sales Transaction Amounts Work), RM00101 (RM Customer Master), RM00102 (RM Customer Address Master) tables. These tables all share a GPSFOINTEGRATIONID, INTEGRATIONID, and INTEGRATIONSOURCE columns, used to track and exchange information between GPSFO and Dynamics. Even the Field Service module had piece of the action with the SVC00203 (Service Call Line Detail) and the SVC_FO_ID column.

Now, can I reuse those columns to store my own data?

As the say goes, not because you can means you should. While these columns are no longer used to store data in these tables -- according to the Microsoft Dynamics GP SDK they are marked as "reserved" -- you should not begin to store your own data in them. Microsoft may choose in the future to re-purpose or simply drop these from the database schema and your data will be gone. Hence, it's not recommended to use these fields for any purpose. Alternatively, you can create your own custom tables, whether in Dexterity or natively in Microsoft SQL Server to store any related sales information.

As a final request, please add your comments on your experiences (or nightmares, depending who you ask) with GPSFO. Let's see how many of you veterans are still out there and tackled the "new brave world" (or the 'old' as it has been a good number of years since).

Until next post!

Mariano Gomez, MVP
Maximum Global Business, LLC


DynamicsGP said...


Perry Smith said...

Thanks Mariano, the nightmares of GPSFO subsided last year.....

It was a painful time in GP history. There was so much promise that was not fulfilled until CRM 4.0.

Mariano Gomez said...

Interesting comments guys... I went to GPSFO training in Fargo back in 1998 as part of the original group of partners who would preview the product before its official launch. I have to admit that the amount of knowledge needed to configure the product was beyond the reach of any one individual. I sure recommended against it in my company.

We never translated it and never cared to implement it.