So that’s so relieving to know that we do not have to wait to get a compatible Microsoft Dynamics Connector for Microsoft Dynamics GP 2013.
The current version V2 RU2 supports Microsoft Dynamics GP 2013 already. Check out this post on official Connector blog: Connector for Microsoft Dynamics V2 RU2 supports Microsoft Dynamics GP 2013.
VAIDY
Recently, I had to integrate around 1000+ Sales Quotations which comprised of 10000+ Sales Item Lines. This had to be done in a seemingly impossible timeframe, as we were totally running out of schedule.
I devised a strategy to do an All-SQL work as follows:
1. First Export required SOP tables from source database and Import it as SOP TEMP tables to destination database. The Source & Destination databases denote GP Company Databases, of course.
2. Generate the SOP Number by taking the Number Sequence from destination GP Company SOP Quotation Setup.
3. Update all SOP TEMP tables with relevant new SOP Number.
4. Identity only those fields which are required and then do a direct INSERT INTO. This insertion, in my case, happened for SOP10100 (Header), SOP10200 (Detail) & SOP10106 (User-Defined).
5. Once the above is done, run a Reconcilation on all these inserted documents, to make sure that these documents got accepted by destination GP Company as valid SOP Quotations.
All above tasks took me 5 hours of time (including 2.5 Hours of Data Updates & Reconciliation).
Excellent, and I was so happy that I could meet the deadline. I intimated the users to verify and send me the feedback. Users reported back with positive feedback and I was relieved.
ISSUE FACED: Quotations were not getting converted to Orders. Transaction Edit List shown nothing. All looked perfect.
TROUBLESHOOTING RESULTS: 30mins of troubleshooting yielded me one very silly reason behind this issue. The field in SOP10100, USDOCID1, was an Empty String for all Quotations integrated thru’ SQL. This field is the one which tells GP to which Order ID it is suppose to convert this quotation to.
SOLUTION: You all may know by now what the solution would be. Another SQL UPDATE which took the “Transfer To Order ID” value from SOP Quotation Setup and updated SOP10100 accordingly, did the trick.
WARNINGS:
1. Though I had mentioned that I followed ALL-SQL approach, it’s not supported by Microsoft.
2. I have not shared the SQL Scripts here, as it completely depends on the particular scenario and the way SOP Quotations are used by clients.
3. This post is intended to explain two things:
3.1. There are several successful & time saving approaches that we can follow, even if it’s unconventional.
3.2. With each unconventional approach, there lies a risk which cannot be foreseen. Unless, you are dead sure about your GP Technical & Database Skills, you are NOT suppose to tread any such approach.
4. I just wanted to highlight how one single field (which in our perception may look simple) may cause a critical consequence.
VAIDY
It may be coincidental, but I am of-late reading lots of CRM to GP Integration articles.
This one is another gem from Mariano: Integrating Microsoft Dynamics CRM with Microsoft Dynamics GP in 72 hours…and 5 “Easy” Steps.
May sound like simple things, but very critical things.
MUST READ.
VAIDY
I am working on integrating CRM with GP for very specific requirements and you would have read my findings and explanations in the past two articles.
That’s when I came to read this article: Successful Model for CRM Implementation. This is pointed out in The eOne Dynamics GP and CRM Blog at the right time, I would admit.
Can’t agree for more.
A MUST READ for all Consultants, Customers & Implementation Partners.
VAIDY
There is a change in what I explained in previous article.
CRM GP Adapter does have a Mapping Facility where in you can select Sales Document Type & Sales Document Type ID, in the Microsoft Dynamics CRM Adapter Mapping Settings.
If there is no settings done here, it works exactly like what I explained earlier. If it finds this setting done, it creates the Sales Order accordingly.
[Thanks to Consultants, Prince & Prashantha, who are working on this Implementation and sharing this knowledge with me].
But still, the ability to create Sales Orders from CRM to GP by dynamically selecting Order Type ID & respective Document Number is still a miss.
More to follow as and when I gain more knowledge.
VAIDY
This is the first time I am working on CRM GP Adapter, and I am closely monitoring it being implemented and not working on it directly.
In our business scenario, we maintain different SOP Order Document IDs for different Departments and there by different running SOP Order Document Numbers. For instance, Sales Department Orders will have a Document ID as SALES and SOP Number Sequence will be something like SALES-0000001. Services Department Orders will have a Document ID as SERVS and SOP Number Sequence will be something like SERVS-0000001 and so on.
Incidentally, when I was working on CRM Order Integration to GP Sales Order, I was curious to know how the Sales Order in GP is created by using which Document ID and which running sequence number is used to generate my SOP Number in GP. The below is the answer to this query:
The highlighted SOP Default Order ID is what CRM takes to create all Sales Orders to get pushed from CRM to GP.
This we (I and another Consultant) confirmed by deleting that Default Order ID from SOP Setup and running the CRM to GP Integration for an Order. The below is the eConnect Error Log:
—
Microsoft.GreatPlains.eConnect Version=10.0.0.0
.Net SqlClient Data Provider
Procedure or function ‘taSopHdrIvcInsert’ expects parameter ‘@I_vDOCID’, which was not supplied.
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ExecStoredProcedures(String xml)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.eConnect_EntryPoint(String ConnectionString, ConnectionStringType ConnectionType, String sXML, SchemaValidationType ValidationType, String eConnectSchema)
—
CRM Adapter tries to get the Default SOP Order ID from SOP Setup and when it did not find any one being setup, it passed NULL STRING as DOCID to respective Web Services method and in turn creating a NULL STRING node in respective eConnect method.
But isn’t this absurd? When GP allow us to create as many Order IDs as we want and have as much different SOP Document Number patterns as that of Order IDs, why can’t we have the same in CRM?
In my opinion, when we have an interface between any two applications, we should have atleast the critical fields (or factors) mapped to each other. Otherwise, the purpose of integrating two products is not satisfied completely.
I do understand that some Customers maintain one single Order ID across it’s Sales Orders. But then what about the Customers who religiously maintain different Order IDs for different scenarios?
Will be working more on this in coming days and will update you all with my findings.
VAIDY
Dynamics GP - Learn & Discuss 