Scribe Software Blog

Using Scribe DTS Parameters to Change Connections on the Fly

Don Panek, a Scribe MVP (Most Valuable Professional), recently completed a global deployment of Microsoft Dynamics CRM and Dynamics AX.   The integration platform used Scribe Insight. In this article Don details how they configured the integration to manage the complex requirements.  

 “This client has multiple AX servers throughout the world which means our Scribe target connection AX endpoint will need to be changed dynamically. Likewise, we may also need to change the target AX Company as well for a given endpoint…”

A challenge with a complex CRM to ERP integration is that the ERP system is often configured for multiple companies, and the CRM system does not have a way to manage that type of configuration.  Don explains how he configured Scribe to handle this challenge using a combination of CRM customizations and Scribe configuration.  Specifically he uses a nifty feature called DTS Parameters
which allows the Scribe tools to pull in variables at runtime. This feature enabled him to change the connection to the correct Destination Company in AX on the fly.

 

 

“..we will see that based on the values we have stored in CRM we can easily and reliably drive our integration to multiple AX instances and companies using this method and the powerful DTS Parameters.”

The details of this method can be found on his new blog: http://crmdirectory.com/crmblogs/using-scribe-dts-parameters-to-drive-your-dynamics-crm-to-dynamics-ax-integration/

Don Panek is a Scribe MVP, he has been involved in the implementation of Scribe integrations and migrations for Hitachi clients as well as clients with previous employers over the last 10 years. He works at Hitachi Solutions http://www.hitachi-solutions.com/ 

Scribe MVP Blog: Increase Dynamics CRM Online Scribe Data Migration Performance

Scribe MVP (Most Valuabe Professional) Jayson Goldinger, of Slalom Consulting, posted this useful blog today on Dynamics CRM Online integration with Scribe and we thought our readers would find it useful. Thanks Jayson!

"For those of you who have done data migration or integration with Dynamics CRM Online, you know that the performance may be a challenge. I recently faced that challenge during a recent project.
 
I had approximately 52,000 account records with associated contacts to import. I also needed to do a check (Seek Step) against CRM to see if the record was already in CRM. I was using Scribe Insight with my source being Excel and my target was Dynamics CRM Online using the Dynamics CRM Adapter. On my first attempt my job ran for 9 hours and only imported approx. 15,000 records. At that rate it would have taken over 30 hours to complete....."

Read Jayson's entire blog here: http://blog.slalom.com/2012/08/20/increase-dynamics-crm-online-scribe-data-migration-performance/ 

 

Multi-Target Use Cases: an alternative approach for product catalog integration between Dynamics NAV and Dynamics CRM

In this post I want to share an idea with you I came about during my experimentation with the multi-target feature of Scribe Insight 7. My company, Succentric, specializes in the support and enablement of both new and existing Scribe partners, particularly regarding Dynamics NAV and Dynamics CRM. As such we get a lot of feedback around the topic of the Adapter for NAV. When the NAV/CRM template was released back in 2009, one of the first remarks we got was this: This is a great solution, but what about customers with large item master data?

Those of you that are familiar with the NAV/CRM template will know that in order to match the different data models of Dynamics NAV and Dynamics CRM a custom table was built in NAV that would store all combinations of items, variants, and sales prices available the NAV Company, calculated by Scribe Integration processes called ScribeSalesPrices. Let's imagine a company with just 125,000 products with on average 5 variants and also on average 3 different prices per variant. That'll give you 1.875 million records in this table. It is not prevalent scenario, but it is quite obvious that if the situation is like this, we could get into trouble with this approach. At the time the template was originally built, there was scarcely an alternative. But the multi-target-feature introduced in Scribe Insight 7 allows a completely new approach, which also may fit to other scenarios where the differences in the data model require a high degree of calculation and/or expansion of the data during the integration.

The following text is not a step-by-step guide, but rather an outline to discuss the possibilities and it will require considerable NAV skills to implement. However, it may be inspiring to some of you, and it shows the direction to a solution for a whole class of complex problem.

If we have a static list structure on the CRM side, and a dynamic, engine-driven structure on the ERP side, we have to execute the ERP logic in order to build all the data as a static snapshot. But can't we avoid storing the result in an extra table just for the sake of integrating it? What we would need to do is execute the price calculation for every n-tuple from inside a DTS, and immediately write the result to the CRM price list item entity. In our example this can be achieved in the following way.

First, we have to design a new DTS that would use the "Sales Price"-Table of Dynamics NAV as a source. This table contains a record with a sales price for each combination of

• Item
• Item Variant
• Price Group or Customer Code
• Date
• Currency
• Unit
• Minimum Quantity

It is also used by the current NAV/CRM template, but the result of the integration process that is processing those records is just written back to NAV. But actually the Sales Price table provides all we need to create a sales order, which is the most convenient way to execute NAV's pricing engine. We can retrieve the price as it was calculated by Dynamics NAV immediately after the execution of our step by storing the "Unit Price" field of the Sales Line table in a target variable. As a result, we'd get a particular price that would be valid as a specific CRM price list item, and with the multi-target feature, we can within the same DTS both create a sales order and price lists and price list items on the target side. And we can get completely rid of the ScribeSalesPrice table that can become so large.

Now you may say, "That's great, but wait a sec. What about all those useless sales orders we create all around the place? We will have to delete them afterwards, which is costly in terms of NAV system resources, and it will leave huge gaps in our sales doc. No. series." That is why we would only create those sales orders temporarily that means without actually storing the Header and Line records to the database and without creating a document no. Doing this is actually a Dynamics NAV standard procedure that is used in lot of circumstances if specific prices are needed.

To do this with Scribe, we have two options. The first one is less work, but it will require a change in the NAV adapter C/AL code, in the adapter's NAV objects. We would enhance the adapter to allow us to choose if a step would create permanent or temporary records. To do this we have to perform the following actions:

1. Add a field to the Sales Header table that acts as a semaphore we can use to indicate what we want to do. It can be any data type really, but an option field will probably be the best fit.
2. Change the code in the function "InsertRecord" of Codeunit "Scribe Adapter commands":
   
   IF (<Use a function here to retrieve the value of the XML-Tags representing our semaphore from the XmlDocument-variable here> = 'yes') THEN BEGIN
   RecRef.OPEN(tmpInt,TRUE);
   xRecRef.OPEN(tmpInt,TRUE);
   END ELSE BEGIN
   RecRef.OPEN(tmpInt);
   xRecRef.OPEN(tmpInt);
   END;
   
3. Make sure that we apply a dummy value to the No. / Document No. fields of the Sales Header / Sales Line table in the data mapping of our Insert steps.

The beauty of this option is that it is less effort to implement, but the downside is that you have to modify actual adapter code. Only those that have high level of NAV coding experience should do that. You should make this decision very explicitly by weighing the gain and the risks.

The second option gives you more security and you omit the risk of screwing up your adapter, but it'll mean more effort. However, I would strongly recommend this option to most users. The idea is simply to code a custom action that calls a custom Codeunit that creates the temporary Sales Order, and returns the calculated line values as they are needed. To do this we have to perform the following actions:

1. Following the Steps in the Help file for the Adapter for Dynamics NAV (Section "Working with actions"), create a new Codeunit. Prepare a "GetInputParameters"-Function as described in the help file, and make sure that all of the above listed parameters are distributed to global variables.
2. Create your "ExecuteCustomAction"-Function and put your code for creating your Sales Header and Line there. Make sure that you are using temporary variables for Sales Header and Sales Line, and that you predefine a Sales Doc. by filling a value in the field "No." of the Sales Header (can be '0' for example). Also, you need to create your Sales Line in the following way:
   SalesLine.SetSalesHeader(VerkKopf);
   SalesLine.INIT;
   SalesLine."Line No." := SalesHeader."Line No." + 10000;
   SalesLine."Document Type" := SalesHeader."Document Type";
   SalesLine ."Document No." := SalesHeader."No.";
   <Your code to fill in Sales Line Details like Type, No., Qty. and so on>
   SalesLine.INSERT;
3. Prepare your "SetOutputParameters"-Function as described in the Adapter help, and put all relevant values from the Sales Line table record there.
4. In your DTS with the Sales Price Table as Source, add an Execute Step to call your custom action.

I hope this information about these two approaches will help you determine the best strategy for your NAV integrations that use Scribe Insight. As part of my participation the October edition of the Scribe Inside Track web meeting, I led a discussion about these approaches of using the Adapter for NAV.

Revelations on Efficiency with Scribe 7 and Multi Target

One of my clients here at Infinity Info Systems has been involved in an elaborate integration between Microsoft Dynamics GP and Microsoft Dynamics CRM that I built in Scribe 6.4 and have been tweaking ever since. The client is a medical component parts supplier with over 9000 component parts in stock, and thousands more that they source on a yearly basis.

The original GP to CRM integration I designed used an hourly batch process to migrate their data from GP (SQL direct) to CRM. This, while acceptable, was not the preferred solution due to lag-time between data entry in GP and usage in CRM, as well as a lack of true net-change management.

With the additional stability offered in Scribe 6.5.2 and the GP adapter enhancements in version 3.9, the client finally agreed to implement the adapter in their production environment. Earlier this year, I moved the client to a transactional integration using the GP publisher and MSMQ for Accounts, Contacts, Products, Units, Unit Groups and Price Lists. The effectiveness of the GP Publisher instilled confidence in the client and allowed us to move forward, tackling some of the more complex problems that we were facing.

One of the more challenging tasks I was assigned was the concept of implementing a “New Product Release” process. This process would allow the client to replace an aging database used to source new products that they do not currently carry in stock. Since this customization set was already in place in CRM, the client wanted to leverage it to store these “non-inventory” items and then convert them to GP inventory when a certain threshold was met.  This would reduce repetitive data entry in multiple systems, and allow end users to work in CRM where they felt comfortable, rather than the legacy database.

The two major challenges with this process were that the client wanted the process to be nearly instantaneous using MSMQ, and the client wanted to empower the end user to troubleshoot conversion issues themselves to reduce IT Department burden.

Unfortunately, if there was a problem on any step in the process, I was limited to one set of data from the Message published from the MSMQ to write error specific information to the end user. This frustrated me to no end, because in order to be successful with this process, the client wanted to be able to write back detailed failure information to CRM if there was a problem, such as:

• Error Reason
• (CRM) Item ID
• (CRM) Item Description
• (CRM) Item Created On
• (GP) Item Number
• (GP) Item Description
• (GP) Item Created On
• (CRM) Conversion Attempted On
• (CRM) Conversion Attempted By

This information was difficult to write back, because of the limited source update capabilities using MSMQ. In addition to the limitations, the process felt extremely clunky and prone to error due to the back and forth of the message handoffs between DTS files on success of each job. The only way for me to do this was to create multiple jobs to handle failures, creating a temporary table in Scribe Internal to gather information about the failure, get information from GP and CRM, and then push that information to CRM after the processing was complete.

With the release of Scribe 7, I attended one of the multi-target webinars, and one night while in the shower (where my brain tends to do most of its intense thinking), had one of those “Holy Cow!” moments where I realized the power of multi-target and the huge reduction in overhead it could provide. I decided to install and configure Scribe 7 in our test environment to see if I could reduce the complexity of this process by leveraging multi-target.

After redesigning the process, I was able to take 8 individual DTS files performing multiple tasks, and merge them down into one DTS that has multiple steps going against GP and CRM. The new DTS gets its source from the same message as the old process; however the resulting complexity has been reduced by a significant factor, cutting out temp tables and multiple message handoffs.

Multi-target has also provided us the ability to write back to CRM with failure info from GP (Steps 7 and 8 in Exhibit 1), should we find duplicates in GP or if the insert fails. Using temporary variables (Exhibit 2) I can capture CRM and GP data points for each record, writing back robust error information to CRM if the job encounters issues.

Additionally, upgrading to 7 from 6.5.2 for the existing processes was seamless. I took the 20+ DTS files and two publishers related to the other pieces of the GP-CRM integration and updated them all in a manner of an hour. The only issue I ran into was that I had to update my DBLOOKUP statements, because with the addition of multi-target, you now do DBLOOKUP against the connection name, not “S, T or I”.

Once I realized the power of multi-target, I haven’t looked back. I am pushing all my clients to Scribe 7.0.1 if they need any kind of new DTS development, because it makes their systems more efficient and makes my life significantly easier.

Exhibit 1

Exhibit 2

User Conference Session Content Outline: Tips, Tricks and Traps around the Scribe NAV Adapter

Greetings!

I am Markus Lischka, a consultant with succentric, specializing in Microsoft Dynamics NAV. This September I will be traveling from Germany to be a guest speaker at the Scribe User conference. Along with Morgan Pierce, a QA Engineer from Scribe, whose focus has been the NAV adapter and template, I will be presenting a session about tips, tricks and traps when using the Scribe Adapter for Microsoft Dynamics NAV.

When using application adapters, it is vital to understand the underlying business logic as well as the characteristics and limitations of the connected business solution, especially as it so easy to get connected using the Scribe adapter technology. Experience in the field has shown Microsoft Dynamics NAV to be an application where users need particular in-depth knowledge about the techniques used, and the problems that can arise using them. This session will provide insight on how to deal with or avoid the most common issues, how to decide whether a problem that emerges when using the Scribe Adapter for Dynamics NAV actually originates in Scribe or rather in Dynamics NAV, and some common customizations around the adapter and template.

Here is some additional information about the topics that will be covered:

-Brief overview about external accessibility and exception handling in Dynamics NAV. Understand on a high level what interfaces Dynamics NAV offer by its design and what functionalities they have. Understand the effect this has on any integration scenario.

-Discuss the most common application specific problems in Dynamics NAV and how to deal with them. Learn typical issues when using the NAS and C/FRONT and understand the impact of the NAV version and build.

-Learn how to evaluate a problem that occurs during the execution of a DTS in the Workbench or in an Integration process that uses Dynamics NAV as a source or a target. Understand how to make a sensible choice about where to seek for the reason if no or limited error messaging is available.

-Review common customizations using the NAV Adapter (and the NAV template) – Discussion around the template architecture (focusing on contacts and pricing) and customizations relating to Dynamics NAV sales persons and teams.

Both Morgan and I are very excited about this session and hope to see many of you at the conference this fall!

Regards,

Markus

Scribe 100% used in all our projects

Today I'd like to introduce the company I work for, Travi@ta, and explain our successful partnership with Scribe.

Travi@ta is located in Brussels (Diegem), Belgium and is focused only on Microsoft Dynamics Crm. With more than 50% of market share, we are by far the leader of the Belgian market. Founded in 2003, Travi@ta employs nearly 40 people, all of them being dedicated to and having strong experience with Microsoft Dynamics Crm.

So far we had a nice and steady growth, and in the process had the opportunity to successfully implement Crm in a lot of companies in a variety of markets. And Scribe has been involved in many of these projects. To this day, we have implemented more than 50 projects with Scribe, and we do around one new project per month.

This year, Travi@ta has been named finalist for the Microsoft Dynamics Financial Services Partner of the Year 2010 for our contribution in the insurance sector. In Financial Services, all the Belgian insurance companies are Travi@ta's customers... and they all use Scribe! There is no exception. This simple fact enlights the power of Scribe Insight and the trust that our customers have in this product. As said above, we have extensive experience with Microsoft Crm. And we have not found a tool that even comes close to the usability, simplicity and power of Scribe Insight. This is the reason why we suggest to all our customers to use Scribe.

We use Scribe mostly to setup batch interfaces between the back-end systems (master data) and MS CRM, where the data get enriched. These integration scenarios involve data synchronization, automated data feeds, etc. For such scenarios, the Total Cost of Ownership of Scribe is low given its rich inherent functionalities. The kind of entities that we sync to MS CRM are: brokers (accounts) along with their different sales reps by Insurance product, points of sales, end customers, policies, offers, territories, user hierarchy, data warehouse data (used for marketing segmentation), contact persons, software tool infos (broker tools), incentives (data such as travels, gifts, registrations), commissions, etc... Scribe Insight has proven to be the most appropriate tool to perform those data driven integrations easily and smoothly.

Thanks to our strong relationship with Scribe, we have reached the highest partner level at Scribe: Scribe Elite Partner. Ludo Frisch, Microsoft Dynamics CRM and Scribe Insight Certified Consultant at Travi@ta, has earned his Scribe MVP (Most Valuable Professional) title. There are only 33 Scribe MVP's worldwide. They represent the most experienced and skilled Scribe users from among their over 800 partners, 10.000 customers and related software vendors.

This year also, we are happy to announce that Trav@ta has been named for the 2010 President's club for Microsoft Dynamics for the third time! Our exclusive devotion to Microsoft Dynamics CRM, together with our strong business experience in CRM, and our ability to work and partner with the Best, like Scribe, allow us to continually grow and participate to the incredible success of Microsoft Dynamics Crm.

Scribe Insight Replaces Customizations in Dynamics GP

This blog post was written by guest author, Gerry Weiler of Crestwood Associates, a Scribe Certified Partner.

Scribe Insight has been implemented by a large Chicago area manufacturer and distributor of ethnic foods running a 24/7 operation. Scribe replaced several customizations enabling them to upgrade from Great Plains v 7.5 to Microsoft Dynamics GP v 10.0 and automate certain processes that previously required users to manually launch custom code.

The customizations, including some Visual Basic applets, were written to facilitate the movement of data in and out of Dynamics GP. To move data out of Dynamics GP, Scribe integrations were created to extract customer aging and invoice detail, as well as pricing and promotion information. Scribe delivers the data to a separate Microsoft SQL server running in a virtual environment. These integrations are scheduled via the Scribe Console to run during off hours.

Scribe Seamlessly Integrates Dynamics GP data with Drivers' Hand-held Devices

There are two types of data coming into Dynamics GP – new sales orders and payments.

New orders taken by delivery drivers via a hand held device are uploaded into the SQL database on the virtual server. Scribe integrations were created to monitor the virtual server for new orders and to load them into Dynamics GP.

When an order is delivered, the same driver creates an invoice on their hand held device. The invoice gets sent to a warehouse/inventory control system and ultimately into Dynamics GP. Payments made for delivered goods is recorded on the hand held device but not saved with the invoice. Therefore, a Scribe integration was created to monitor the virtual server for payments received for these invoices. The integration automatically applies the payment to the appropriate unposted invoice in Dynamics GP.

Another Scribe integration was created to upload cash receipts into Dynamics GP for payments not designated for a specific invoice.

The system has been up and running for some time with only a few minor issues reported. By removing a large amount of custom code, the client is less likely to remain on an unsupported version of Dynamics GP for as long as they have in the past, and are able to take advantage of features in the new releases sooner.

Scribe Insight Installation, Updates, and Windows User Account Control

When installing software on Windows 2008, you typically want to install it as an administrator.  I’m not just talking about the user account having local administrator rights—with Windows User Account Control on Windows Server 2008 (and also on desktop versions of Windows Vista and Windows 7), you have to right click on an .exe file and select “Run as Administrator” if you want the installation to run successfully.  This applies to almost any application, including Microsoft Dynamics CRM, Scribe, SQL Server, and others.

Of course, you can disable User Account Control; however, frequently this is not an option, especially if you aren’t the owner of the server on which the software is being installed.

With Scribe Insight installations, I have found that you always want to run the installation as an administrator; however, there are also a few more considerations.

The first time you run Scribe Workbench

The first time you run the workbench and register the application, it is also a good idea to run the application as an administrator (right click on the icon for Scribe on your desktop and select “run as administrator.”  If you don’t I have seen error messages, or have seen the registration not “take” and require the registration wizard to be re-run next time you open the application.  After the application runs for the first time and the product is registered, running as administrator is typically not required.

Installing updates

The first thing I always do after installing Scribe Insight and registering the product is to install the 6.5.2 update.  This update is the most recent update to Scribe Insight, and it has a number of very important updates and fixes.  Like with the application installation, you want to run the update install as an administrator.  Otherwise, you may see errors like this:

But how do you run the update as administrator?  Since the update is an MSI file, when you right click, there is no “Run As Administrator” option.  There are several ways to do it; this is my favorite and probably the least painful way to do it:

1. Download the 6.5.2 update.
2. Navigate to the downloaded file ScribeInsight.msi.  Hold the shift key and right click on the file and select “Copy as path.”
3. Open Notepad and paste the copied file path.  Save the file, making the file extension “.bat.”
4. Right click the .bat file and select “Run as Administrator.

The update will now install successfully.

Integrating Dynamics NAV and Web Services

Hello, I am Evgeniy Zherebnoy, from Celenia. As an employee of Celenia my responsibilities include providing pre-sales support to Scribe Software, and consulting services to Scribe Resellers who are creating and deploying integrations with Dynamics NAV and Dynamics AX.

At a time when investments in business integration for business process optimization are increasing, web services is becoming the leading technology to solve Services Oriented Architecture challenges of the Web 2.0. In this blog we discuss how the Web service technologies can be used to integrate various business processes to Dynamics NAV using Scribe Insight.

On the technical side, integration can offer some challenges both in terms of data integrity and technology – data integrity needs to be solved by the people that fully understand the context of the business process. The technology challenge can be simplified by the use of web services – it is a simple message oriented mechanism that works across most operating systems and network protocols. Most new applications today have a web services interface or their programming language allows you to build web services.

The latest releases of Scribe Web Services Adapter and Scribe Adapter for Microsoft Dynamics NAV include a number of features:

Scribe Web Services Adapter

• Connects to SOAP based Web Services via HTTP and HTTPS protocols.
• Presents Web methods as business objects to be selected as the source or target.
• Presents parameters as fields to be used in the mapping window of the Scribe Workbench.
• Supports SOAP headers when connecting to Web methods.
• Provides the ability to design custom exception conditions when the WSDL is not specific enough.
• Enables you to designate specific Web methods to be used when "logging on" or "logging off" the Web Service.
• Allows you to include binary large objects (BLOBs) in your data set.

Scribe Adapter for Microsoft Dynamics NAV

• Encapsulates the business rules of Microsoft Dynamics NAV, replicating the logic that is applied when you add or change records using the Microsoft Dynamics NAV user interface.
• Gives ability to synchronize data from any Microsoft Dynamics NAV table.
• Supports retrieving, inserting, updating, and deleting data in Microsoft Dynamics NAV.
• Adds many-to-many associations, such as Contacts to Opportunities. Provides functionality to work with related tables as a single object, which gives more flexibility in data translation.
• Ability to use custom actions for different business processes. With Adapter you are able to execute NAV code by calling specific Codeunits.
• Supports multiple companies using NAS instance for each Dynamics NAV company.

The diagram below shows high level architecture of the Scribe Insight involving both adapters:

 

Sample Integration Scenarios

Integration foreign currency exchange rates with NAV

Currency rates are changing frequently and are becoming an important factor in many companies as more and more companies trade across borders/currencies. Having the exchange rate for a specific currency in your Dynamics NAV system up to date is possible via simple implementation as an example:

• Connect through Scribe Web Services Adapter to StrikeIron Foreign Exchange Rates Web Service using WSDL link as a source: http://ws.strikeiron.com/ForeignExchangeRate3?WSDL.

• Choose a web service method on the source side that returns current exchange rate based on the defined input parameters.

• Connect through Scribe Adapter for Dynamics NAV to Dynamics NAV database and company as a target.

• Set up target steps into the Currency Exchange Rate table (or any other Dynamics NAV table) and configure data links, lookup links, and other parameters to correctly match returned parameters of the web service with corresponding fields in target table/tables.

• Once the data translation specification (DTS) document is completed and ready to use, you can run it within the Scribe Console Integration Process behind the scene with suitable options for time based integration - run a batch on a specified schedule.

Data integration and replication from a web store into Dynamics NAV

It is common way to collect base contact/customer/order information from a web store. Such web applications can be like non Windows systems hosted on Linux based operating systems. In this case direct data integration into ERP system using web service on a source side is one of the most appropriate ways.

The basic configuration steps for source and target connections within the Scribe Web Service Adapter and the Scribe Adapter for Dynamics NAV are very similar to the steps described in the first scenario.

With possibility to build and use custom ERP Actions in the Scribe Adapter for Dynamics NAV on target, you are able to perform complicated inserts/updates in Dynamics NAV by calling related business logic.

For instance, conversion routine of Contact into Customer can be done within ERP Action. In this case an execution step with such action should be used in target configuration of the corresponding DTS. It is important to point that all target operations in Dynamics NAV are executed through NAS API, so all required business logic and fields validation can be triggered in the same way as the operation done manually within CSIDE client.

Here is an example of the out-of-the-box ERP action that can be used during conversion procedure of a Sales Quote into a Sales Order:

1. Scribe Custom Action Card in NAV
2. Target "Execute" step configuration in Scribe Workbench

More detailed information about custom action can be found in Scribe – Adapter for MS Dynamics NAV help (Working with actions section) -https://openmind.scribesoftware.com/download/AdapterForDynamicsNAVHelp

Using NAV as source application and external web site/MS Share Point web site as target

Another example in this regards is the transfer of Sales Header and Sales Lines information from NAV to post Sales Order information on a web site. This comes up with possibility to connect to NAV as a source within the Scribe Adapter for NAV and execute inserts/updates through the Scribe Web Services Adapter. In this case, pulling data from NAV database is done via C/Front interface and can be implemented as real time integration process or time based as well.

Normally, the Scribe Web Services Adapter is limited to perform only "Execute" steps on a target side, however, in the scenario of using arrays of complex types (e.g. Sales Order) as a target the Scribe Web Services Adapter provides additional objects that can be used for target steps. These target steps allow you to "Insert" complex types into the array before the Web method is called. Detailed information as for this technology with step-by-step description of the scenario, where Scribe Sample database is presented as a source can be found in Scribe – Adapter for Web Services help - https://openmind.scribesoftware.com/download/AdapterForWebServicesHelp

Have you built other Dynamics NAV and Web Services solutions for your customers? If so, feel free to share the details.

Guest Post: CRM/SAP Integration using Scribe

This post is a continuation and expansion on the original blog post published on the personal blog (http://www.mohamedibrahim.net/blog/tag/scribe) of Mohamed Ibrahim Mostafa of CIBER UK.

-----

My name is Mohamed Ibrahim Mostafa and I work as a senior consultant for CIBER UK, a Microsoft Gold Partner, SAP Gold partner and Scribe Reseller based in the UK specialising in systems integration and implementations of a wide range of solutions based on Microsoft technologies among others. In one of our projects, we were required to implement an integration solution to connect a SAP system with Microsoft Dynamics CRM 4. There are many ways to achieve such integration but we finally made the decision to use Scribe for several reasons among which is the fact that Scribe Insight provides the interfaces and full control for the client to carry on supporting their own system once we complete the integration.

Scribe provides a native business application adapter for Microsoft Dynamics CRM as well as standard connectivity adapters for Open Database Connectivity (ODBC), SQL Server, OLE DB, Web Services and XML. Hence, whatever your SAP setup is, you should be able to access it smoothly from within Scribe. Scribe's CRM adapter also offers a great feature which is the possibility to access partner hosted CRM 4 implementation even in the case of a shared CRM implementation where each CRM organisation on the same server belongs to a different client of the partner host.

To connect to a hosted CRM you should use the Web Form (IFD) authentication option in the CRM adapter connection option. You can input details in the login similar to the example below:

Figure: Connecting Scribe to remote CRM hosted server

In our project, we had SAP as the data source and Dynamics CRM as the target of the integration. Configuring the source fields is very important for the mapping process between source and target fields later. You can always edit the source field names, data type and lengths by editing the QETEXT.INI file. So in case of text source files, if the field names are not meaningful and data types need to be changed (such as NUMERIC to VARCHAR), you have full control of adjusting these from the QETEXT.INI file. Just close the Scribe workbench window and open the file in notepad or any similar text editor.

To plan and perform this integration, Scribe identifies 5 major steps required for such integration in this white paper. These are: Data extraction, Translation, data update, automation and then monitoring and management of the ongoing integration (usually using Scribe Console). The figure below shows the various steps for this integration.

1. Data Extraction: This will require direct access to the source applications via a database or a proprietary application programming interface ("API",) from enterprise applications such as Dynamics GP, JD Edwards, SAP which is our case here, MAS 90/200/500, and Siebel. This will also require the ability to capture net changes either through source queries or via published messages from the source where available.

    

2. Data Mapping and Translation: It is most likely that the semantics and format of many fields from the source data will be different than those in Dynamics CRM. Important required tasks include parsing and concatenating text fields, performing date and numeric calculations, executing conditional logic, and performing lookups to resolve synonym values. There will be also the need to maintain a cross reference of primary key values in order to apply updates from one record in your source to the corresponding record in Dynamics CRM. We will discuss some of these data manipulation examples with sample formulas for your Scribe workbench project (DTS).

    

3. Data Update: This capability is the most crucial, yet complex area of the integration task. Capabilities we should look for include (i.) avoiding duplicates using fuzzy logic (like comparing elements of the company name and zip code to look for an Account match) for record lookup, you can also use CRM duplicate detection rules to prevent duplicates, (ii.) performing insert and update operations against multiple objects within Dynamics CRM when processing a single source record, and (iii.) performing all target processing against the Dynamics CRM integration API to ensure that all data imported has been validated by Dynamics CRM's application rules.

    

4. Automation: This is where utilising a customisable template model that incorporates a one-step process from source to target is very useful. Once we have designed the business process, we will need to implement an automated event detection mechanism to initiate an update to Dynamics CRM.

    

5. Monitoring and Management: Once we have developed and implemented our data aggregation solution, we need to consider the ongoing management of the solution as well. We need to be able to (i.) remotely support the solution (including start and stop processes, diagnose errors, etc.) via a web browser, (ii.) automatically alert an administrator via email when processes fail, error, or produce abnormal data conditions, and (iii.) scale across multiple processors to support high volume data scenarios.

This summarizes the steps that you will need to take and follow to be able to achieve the full integration between the two SAP and CRM systems.

The following diagram depicts Scribe's open architecture for data interchange between CRM and SAP.

This open architecture enables a broad range of SAP customers, regardless of what version of SAP they are running or how they have configured their implementation, to be able to use Scribe to integrate with Dynamics CRM system.

Sample Data Manipulation Formulas:

Data coming from SAP could be of various types and forms depending on the SAP architecture and the configuration carried out during implementation. To integrate and upload this data to CRM, you will need to carry out various data translation, mappings and manipulation to be able to correctly update CRM fields, create new records and performing lookups to capture CRM's update records unique identifiers (GUID's) based on information received from Scribe.

1. Unique Identifier Lookup sample formula:

   For Scribe to lookup the GUID of a record, a unique Identifier is needed to return this value. So for example, to get the GUID representing a CRM Account record, we can use the account number as the unique identifier:

   DBLOOKUP (S1, "T", "account", "SAP_AccountNumber", "accountid" )

    

   Where S1 is the first Source field, T= Target, SAP Account number has the unique account number for the customer from SAP and accounted is the CRM GUID for the account record.

1. Country Lookup sample formula:

   If SAP is sending the country value as a two digit code such as GB for Britain and US for USA, etc. You will need to create a new entity in CRM called countries that can act as your lookup table that corresponds to SAP's country lookup table with at least two fields in each record: country code and Country Name (country friendly name. Attribute name is usually the default "name" attribute with any new entity). The formula can look like this:

   DBLOOKUPCached(S2, "T", "SAP_country", "SAP_name", "SAP_countryid")

   This will return the lookup to the country that corresponds to the country code in the source field.

    

   You can do the same for language for example where your formula returns a pointer to the language entity record that has the language code corresponding to what SAP has passed on in the source fields.

   DBLOOKUPCached(S3, "T", "SAP_language", "SAP_languagecode", "SAP_languageid")

    

2. Picklist sample formula:

   To update a CRM picklist based on data coming from SAP, you need to use a text file which you can call it whatever you want (such as: sap_picklist.txt) but you must ensure the file is in the same folder as your scribe dts project file. So if we have a list of department codes coming from SAP, our picklist file can look like that:

   /*Contact Person Department */

   [Department]

   1=1

   2=18

   3=53

   4=4

    

   So if SAP sends a record with department = 1, this will correspond to the CRM picklist item with id = 1. If it is department 2 in SAP, then this will be CRM picklist item with id = 18, etc. This is all of course depending on your CRM picklists configuration.

    

   The formula in this case will simply look like this and Scribe will do the rest for you:

   FILELOOKUP(S4, "cp_picklist.txt", "Department" )

3. Radio button sample formula:

   Cases where SAP is sending a Boolean value (e.g. true/false, yes/no or 1/0). For example, if data coming from SAP represents the value of "send marketing material" field in CRM Contact entity, the following formula can map the value to CRM:

   IF((S5="Yes")=FALSE, 1, 0)

   i.e. if SAP sends Yes in the record, select the "yes" value for the CRM attribute.

    

   SAP might be also sending "1" or "true", so the formula can be:

   IF((S5="1")=FALSE, 1, 0) OR IF((S5="True")=FALSE, 1, 0)

    

   You can also add a small check if the value is sent blank, to set it to true by default then:

   IF(ISERROR(S5) , 0, IF((S5="Yes")=FALSE, 1, 0))

    

4. Checkbox sample formula:

   If a check box value is coming from SAP is sent as 1/0 then the formula to map to CRM should be: IF( S6="1", 1, 0 ) or in case of True/False: IF( S6="True", 1, 0 )

    

This post is a continuation and expansion on the original blog post published on the personal blog (http://www.mohamedibrahim.net/blog/tag/scribe) of Mohamed Ibrahim Mostafa of CIBER UK.

CIBER is an international IT provider supplying high quality solutions to customers in the private and public sectors all over the globe. Operating in 18 countries, with 8,000 employees and annual revenues of approximately $1 billion, CIBER serves its customers from 20 offices in Europe, 60 in the US and 4 in the Asia Pacific region. For enquiries about CIBER services please email: enquiry@ciber-uk.com or contact +44 (0) 870 000 0204.