Scribe Software Blog

Scribe presenting at eXtremeCRM and mAX in Rome Feb 3-6, 2013

If you're attending either of these terrific Microsoft Dynamics CRM and AX - focused events, we want to see you! At eXtremeCRM Rome – let us show you how the leader in Dynamics CRM integration can help you and your customers accelerate your business and make better decisions, faster, with Dynamics CRM in 2013.

Stop by our booth #11 to meet Henk Adriaans, the NEW Director, EMEA for Scribe. Lynn Harrington has returned to the US to focus on our growing ISV area of our business, and Henk is now leading the Scribe EMEA Team. Prior to joining Scribe in 2013, Henk served for close to 25 years in leadership positions in channel and direct sales & marketing, with companies like Oracle, QAD and most recently Exact Software.

Join Peter Chase, Executive VP and Founder of Scribe with the latest on Scribe Online. See demonstrations of solutions for ExactTarget, Silverpop, GoToWebinar, Znode, and more. Learn how your organization can create and sell market-leading integration solutions powered by Scribe Online. He is presenting Tuesday at 11:45am in Bernini 3, "Scribe Online: Revolutionizing Integration Solutions for Microsoft Dynamics CRM". He also spoke during the Executive Summit, "Breaking Through the Barriers" how to scale your business and best practices for growth and re-invention of your business.

Other exciting news: we have 5 of your peers who are Scribe MVPs attending eXtremeCRM EMEA, and will be spending time in our booth. If you have Scribe product questions, want to talk through a CRM data integration issue you may be stuck on, or just want to see how YOU can become a Scribe MVP, be sure to stop by and visit us at booth #11.

Are you also attending mAX in Rome? If so, please join Linda Johnson, EMEA Regional Sales Manager and Anders Jeppesen, Senior Integration Specialist, AlfaPeople on Wednesday 2/5 at 11am for "Scribe or the Microsoft Connector: 5 reasons to Consider Scribe for integration with Dynamics AX."

Scribe Workbench Improves Insert Operations over 40%

Scribe Insight’s newest release has added a Bulk Operation mode to improve the performance at which we send data into Dynamics CRM and Salesforce.Com. An overview of the upgrade is here. For data stores that support bulk operations (such as Dynamics CRM and Salesforce), you can now configure DTS files to specify bulk processing with basic insert, update, and delete. Now you can now set bulk operations for individual steps within a DTS file. Prior to Insight Version 7.6, the Bulk Insert feature was set for the entire DTS file, rather than for each step.

In December when this was released, I was so excited to see our focus was on speed.  So over the holidays I upgraded some of my virtual machine images to see what kind of performance improvements one can make by upgrading Scribe along with several other sub systems.  I just want to take it out and let her “stretch out her legs”, lets see how fast we could go.  So below is the first step of the performance improvements.

• First I set up a SQL database with 50,000 contacts and built a simple DTS to insert these contacts into Microsoft Dynamics CRM 2011 as Leads. By the way if you are looking for some test data I found a great resource for generic contact lists.  http://www.briandunning.com/sample-data/ these are random names mixed with random addresses with properly formatted e-mail and web addresses.  He has built many file sizes from 500 to 5 million contact names, perfect for our purposes.  I created a table in SQL and copied the CSV file into the SQL table.
• Next I built a DTS to insert these into Dynamics CRM as Leads.   No formatting of data, no lookups, just moving the data in from the table as fast as possible. I felt that this would help me see the raw changes. Using our previous version I was able to insert 50,000 records in 26.93 minutes, that is 31 rows per second with this DTS, on this image. 

Before we go any further I need to mention that integration performance is determined by many factors.  The use of Lookups and external variables, target application speed, use of database indexes, query syntax, Step Control pre operation conditions, step order of operations, available RAM on the Server, and wind speed all impact the performance of a DTS.   Well maybe not wind speed, but suffice to say there are a lot of factors impacting the performance inserting thousands and millions of records.  So these results are specific to my environment.   So in this instance the only variable was the Scribe upgrade, your “actual mileage will vary”.        

• After upgrading to Scribe Insight 7.6 I opened up the DTS, I got the following indication that the DTS is upgraded.  I click OK and move to the next step of configuration. 

 

 

• Opening up upgraded DTS, I went to the Configure Steps area, selected the insert step and set insert to Bulk Mode, 1000 rows. You can max this out at 1000 rows if you think your data will process error free. If it encounters an error it will revert to non-bulk mode and will step through each row individually until it gets to the end of the batch size.  So as this was purely inserting with no parent or child objects it is good to set it to the maximum value. 

 

• I ran the DTS from the Workbench.  Happily the rows click past.  16 minutes later all 50,000 records have been inserted into CRM.

 

 

When we look at the performance I see that we are slightly over 40 % faster than the previous version at 52 rows per second.

My performance results are as follow:

DTS Version	Source	Rows	Total Time – Insert
7.5.2	SQL 2008 R2	50,000	26.935
7.6	SQL 2008 R2	50,000	16.007
Improvements			41%

Improving the core functional speed is a great way to start 2013.  When I look at the way the DTS needs to work to gain these performance improvements,  some rethinking of the integration patterns and methods may be needed. When bulk operations are called for it will be best used when your steps are of a simple design.  These would be when building migrations where all the data is inserted. When doing SQL based integrations having separate DTS's for Inserting and Update operations may be called for.  Also where extensive parent child operations are being used, like a sales orders with header and details in 1 DTS, this bulk setting will do little to improve performance.  Further, if the source is a single XML file where only one record is being integrated, then bulk will not improve the performance in any noticeable way. 

As these are only preliminary results running on a virtual machine set up on my laptop, not an ideal environment, I am excited to see the impact of some of the other areas where I can improve my integration performance.  I will be tweaking RAM settings, upgrading to SQL 2012, changing Queue Priority settings, and performing several other tweaks to the system, all of which I will share on the Blog.  Next, I am going to upgrade the underlying database to SQL 2012 to see how that impacts performance.  

Insight 7.6 Released – Featuring Performance Improvements

Scribe Insight 7.6 is now available for download. Insight 7.6 features a number of updates to the core to better support bulk operations with Adapter for Salesforce and to add bulk operations support for the Adapter for Dynamics CRM. The Insight 7.6 release also includes fixes for customer reported issues and other defects in the core product and the adapters for Dynamics CRM, Salesforce and Web Services.

Adapter for Salesforce

• With the Adapter for Salesforce, Insight now supports bulk Insert, Upsert, Update and Delete operations. Previously the adapter supported only bulk Insert operations.
• Using bulk operations can dramatically reduce the number of hits against the Salesforce API.
• For Salesforce target connections, you can now select the Upsert operation from the Configure Target dialog box. Upsert is available for both basic and bulk operation along with any of the available target configuration settings.

Adapter for Dynamics CRM

• For the Adapter for Dynamics CRM, Insight now supports bulk Insert, Update and Delete operations.
• The message priority feature, introduced with Insight v7.5.2, is now available for the Publisher for Dynamics CRM.
• Message Priority for Dynamics CRM 2011 Publishers. Insight Version 7.5.2 introduced the message priority feature for managing messages. With this release, the message priority feature is now available when configuring the Dynamics CRM 2011 Publisher.

Insight Core

• Extended support for bulk operations. For data stores that support bulk operations (such as Dynamics CRM and Salesforce), you can now configure DTS files to specify bulk processing with basic insert, update, and delete.

  You can now set bulk operations for individual steps within a DTS file. Prior to Insight Version 7.6, the Bulk Insert feature was set for the entire DTS file, rather than for each step. When you open a DTS file that uses the Bulk Insert feature, the Bulk Insert mode settings will be configured on each step of the DTS file. While this feature adds flexibility, it also requires careful DTS design. Be sure to read Using Bulk Operations in the Scribe Insight Online Help before configuring bulk operations.

• New Insight Help Library. The Scribe Insight Help Library is undergoing an upgrade. For the new library, we are using a new help authoring tool, which gives us support for multiple browsers, and makes it easier to maintain. Online Help for the following applications is available from the new Insight Help Library:
  • Scribe Insight v7.6.0
  • Scribe Adapter for Dynamics CRM v5.3
  • Scribe Adapter for Salesforce v2.7
  • Scribe Adapter for Web Services v1.5
  • Scribe Jumpstart: Dynamics AX to Dynamics CRM

Online Help for the remaining applications is available from the original Insight Help Library. Over the next few months, we will migrate the help for these products to the new help library.

Release Notes

If you would like to see more information about Insight 7.6 and related adapters you can download the release notes, which include the requirements, information about the upgrade process, and notes about specific defects that were fixed.

• Insight release notes
• Adapter for Dynamics CRM release notes
• Adapter for Salesforce release notes
• Adapter for Web Services release notes

Using Scribe Insight to integrate UPS tracking information into Dynamics CRM 2011

Over the course of the year and particularly during the holiday season, many companies are hastily fulfilling their customer orders. These orders include packages that are shipped by carriers from all over the world to their final destinations. A critical component within the overall supply chain process is that companies need to have available up-to-the-minute delivery tracking information of their shipped product so as to effectively communicate accurate information to their customers. In this blog post, it is my attempt to share with you how I was able to leverage Scribe Insight technology so as to integrate UPS tracking information directly into Microsoft Dynamics CRM 2011. I used the publically available UPS SOAP based web service to get to the tracking information. Theoretically, you could build a similar integration with an alternative shipping company provided that they offered a SOAP based web service endpoint.

Setup Requirements:

1.  Learn About Integrating UPS Developer Kit APIs http://www.ups.com/content/us/en/resources/techsupport/integrating_apis.html

This link will provide a starting point to better understanding the UPS technology API's that are available for integration.

2.  UPS Developer Kit https://www.ups.com/upsdeveloperkit?loc=en_US

This link provides a "How to Get Started" section from which you will need to register for a UPS account. Below the "Developer API" section on the web page, you will find a link to the "Tracking" API, the one that I had used in my testing and being discussed in this post. You will also notice, there are many other API's that are obtainable should your interest stand elsewhere. Please note that you will not be able to download the API until after you have created a UPS account.

3.  A valid UPS tracking number that you can use in your testing. I just happen to have one because of recent holiday shopping.

4.  Scribe Insight (most current release, as of this post - 7.5.2), Web Services Adapter, and Scribe Adapter for Dynamics CRM 2011.

5.  Let's not forget relevant Scribe Insight and web service experience as well!

Testing Steps Detail:

1.  With your UPS account created and logged in, download the "Tracking" API. Via the "Support" tab and under the "Tools and Resources" section, click on "UPS Developer Kit". Remember this page because you will need to reference it later in step #3 below.

2.  On the presented page, you should see a section labeled "How to Get Started". Click on step #5 and follow the instructions to request your access key. This will be needed later on.

3.  Navigate back to the "UPS Developer Kit" page that you were on in step #1 above. In the "Developer APIs" section, click on the "Tracking" link. On the presented page, you will find a description of this API and how to download it.

4.  Click on "Tracking.zip" link to download the API to your PC. It will be approximately 9.8KB in size.

5.  Opening the "Tracking.zip" file, you will see 3 folders (Common and General, Developers Guide, and TrackingPACKAGE). Certainly take time to familiarize yourself with the zip file's entire content however the folder and specific content of interest regarding this post may be located within \TrackingPACKAGE\TRACKINGWebServices\SCHEMAS-WSDLs. I would recommend that you extract all the files (5 of them) to a local folder from which you will reference when building out your Scribe Insight integration.

6.  Unfortunately all is not easy and you will need to perform a few edits to the extracted Track.wsdl and TrackWebServiceSchema.xsd files. Reason being is that the WSDL makes reference to the schema files (TrackWebServiceSchema, common, Error1.1, and UPSSecurity) however the referenced pathname does not reflect where you just saved your files in step #5 above.

 

7.  Beginning with the Track.wsdl file, here are the edits (highlighted) that need to be made:

 

 

8.  Next you will need to edit the TrackWebServiceSchema. See below:

 

 

9.  With the above edits in place and the files saved, you are now ready to launch the Insight Workbench application and create a web service connection to the Track.wsdl file. Note the 3 sequential steps highlighted below that you will need to perform to use Track web service endpoint. In step 1, make sure that you path out to your Track.wsdl location on your PC.

 

 

10.  With the "Validate" operation successful, on the "Configuration" tab, set the SOAP Header values with your provided UPS credentials.

 

 

11.  Shown below is where you can set a static value with your genuine UPS tracking number.

 

12.  At this point you are now ready to build out your integration design, using Scribe Insight. Appreciating that integration designs can come in many flavors, I have shared my process flow diagram of the automated steps that I had put together to integrate data from UPS into Dynamics CRM. I have also provided some additional detail of my Insight design.

13.  Associated to a Scribe Console queue-based integration process, my Workbench DTS step logic is shown below. This logic is initialized at step #3 shown in above process flow diagram.

 

 

14.  The web service call (Step 1 of Workbench logic) executes a request to UPS tracking WSDL based on the XML message automatically published from the user's initial sales-order update within Dynamics CRM. The XML contains the sales-order update information such as tracking number, account information, etc. The web service request immediately provides a return response with various tracking number related information which is then captured as target variables to be used later on within design step 3.

Step 2 of the Workbench logic performs a seek into Dynamics CRM so as to identify the proper account that has the matching UPS tracking number. You will note that I am using multi-target functionality within my Workbench design.

Step 3 then performs an update to the identified Dynamics CRM account to include tracking information such as UPS Activity Date, UPS Activity Status Description, UPS Reference Number Value, etc. Although there are many more values provided in the returned web service response, these are the values that I used to update my custom fields within the Dynamics CRM sales-order entity.

See snapshot below containing the Scribe Insight integration results within Dynamics CRM.

 

I hope that this information is useful to you and demonstrates the power that Scribe Insight can bring to your unique integration use case scenarios.

Write ERP Errors Message Back to CRM

Let me share a story about Bob, a frustrated order processor in need of a tightly integrated system... Bob is processing an order in Dynamics CRM. He pushes the “Submit Order Button” confidently knowing that the order is whizzing around servers, surfing thru the clouds, and eventually landing in the appropriate ERP system. While all of this integration goodness happens his customer waits patiently on the phone. They both await for the all-important Confirmation Number. This number shows up in CRM once the order is accepted in the ERP system, it is the indicator of Order Success.  Pressing the F5 key to refreshe the screen he waits. No Confirmation Number appears. 

“The computers must be a little slow today,” he says with nervousness in his voice. “How is the weather in your city? We just had several inches of rain.” He adds, “...Well, you don’t need to wait any longer. When this Confirmation Number comes back I will give you a ring back. Have a great day, and thanks again for your order.”

Needless to say, Bob is a bit frustrated that he is unable to get a confirmation that the order had made its way into their ERP system. He quickly dials the phone and calls his IT help desk. “Where is my order number? What is going on back there? I had a customer on the phone and…”

The fact is that a CRM to ERP integration can fail for myriad reasons. The customer may be on Credit Hold, or the product that is being ordered is not in stock, or is discontinued. This is a major challenge of “loose integrations”, that is, where the business logic is not replicated in both systems. So if a failure happens in one system the other system needs to be notified of the failure.

What can be done to address the issue specifically with end users? 

In Insight version 7 we introduced a new feature called “Multi Target”. This feature allows one to build a .dts that writes to multiple objects, including the source object. In this article I will show you how to capture the error message and send it back to CRM so the user can see the status of the order, almost immediately. 

Step 1 – Create a Field for the Error Message

First in Dynamics CRM you will want to create, or reuse a field on the Order to send the message into. In my example here, I am creating a field and placing it on the top of the form. However you could use many mechanisms for this. You could create an activity and send an e-mail to the Order Owner, or add a Note to the Order, or create and entry in the Activity feed if that was being used. In this example I am just going to put the error into the Description field. 

 

 

 

 

 

 

 

 

 

 

 

Step 2 Add a Connection to Source CRM system

In this example I am going to use a sample .dts, from one of our templates, whose source is XML and whose target is an ERP system. This XML comes to the .dts from the CRM Publisher; so we need a connection to the Dynamics CRM adapter that we can use as another target.  

 

 

 

 

 

 

 

 

Step 3 – Add Step to Update CRM Order as the Last Step

From the configure steps window, add an update step Sales Orders. Don’t forget to define the lookup link on the SalesOrderId.

Step 4 – Push all the Failed Steps to the Order Update Step You Just Added

All the steps need to have their Fail option set to goto the Update CRM Order steps.

 

 

 

 

Step 5 – Edit Step 5 to Copy the Error Message Into the Field You Designated in Step 1.

You can prefix the message with some text or just put the data straight in. The formula that copies the error message is GETROWERROR().This function will return any error information that occurred while processing a row.

  

 

 

 

Step 6 – Run the .dts and ERP Error Shows On the CRM Order Screen

When the .dts encounters an error, two things occur. First the DTS will record an error in the event log allowing for any notification settings in the console to notify the appropriate individuals of the error.  Second, and most importantly, the ERP error will show up on the screen in CRM.

Dynamics CRM Pick List Mapping Options

A common challenge in data integration is translating pick lists between a source and target system. The two lists of values may have similar context, but have different structures in their respective systems. The trick is to map the list of values from one system to the other so that the meaning of the data is preserved and presented appropriately to users of each system involved in an integration.

Pick lists, or option sets as they are called in Dynamics CRM, allow the users to specify everything from an Account Type to Credit Terms. The way the option sets work in CRM is that the user sees the label on their screen, but Dynamics CRM stores a corresponding value as integer behind the scenes. So when pulling data from CRM you get Account Type = 345 and not what is displayed on the screen. This number has to be translated back into the text that the user selected. This is the issue that the article details.

In his post, Mohamed Ibrahim Mostafa details how to use two methods to do pick list mappings with Dynamics CRM including using an Xref.ini file using nested IF functions. You could also explore using the CASE and DBLOOKUP functions to do pick list mappings.

Click here to read the details of his approach. http://www.mohamedibrahim.net/blog/2012/11/11/scribe-insight-cross-reference-drop-down-and-pick-list-mapping-approaches-option-sets-in-dynamics-crm/

Mohamed Ibrahim Mostafa is the Group Manager - CRM Solutions Architect at Avanade UK and a Scribe MVP. Mohamed has led and implemented projects in the Health sector, Local government, Media, Finance and online E-Commerce solutions.

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.

Integrating Dynamics CRM and Microsoft SharePoint

As a Sales Engineer at Scribe, I get many questions about Scribe capability in specific integration scenarios. One such question is how we can integrate Dynamics CRM and Microsoft SharePoint. In this post, I provide an overview of how I managed to integrate these two systems, some of the tools I had to use, and some of the pitfalls I ran into. I used the Scribe Adapter for Web Services and the Scribe Adapter for Dynamics CRM 2011 when designing this integration. I hope this helps shed some light on how this integration can work between these two systems.

Adapter for Web Services

This adapter is a generic connection to SOAP based web services provided by Scribe. It allows Insight to utilize many SOAP based web services that are publicly available, and also allows you to create your own in order to access legacy systems with poor or no database structure. You can utilize this to either pull or push data to the back end systems, as it exposes all the methods and parameters for the particular WSDL you are using. This adapter also exposes some additional configuration that can be used to streamline your integration design, like providing default values to parameters that can be overridden, logon and logoff methods, as well as error capturing using Regular Expressions.

SharePoint Web Services

Microsoft SharePoint has a very well documented web service back end to assist you in integrating with it. It is not just one web service, but instead a collection of roughly 21 separate WSDL endpoints that allow you to manage and manipulate data. When starting with these SharePoint web services I highly recommend utilizing MSDN for help on how to work with the web services as it is very well documented, gives you code samples that you can utilize to figure out what type of data needs to be passed in, and teaches you what each method does. SharePoint also has a unique way of working with separate sites and its web services. Instead of giving you just those 21 web service endpoints, if you have 3 separate sites built within SharePoint, you get 63 separate end points, 21 for each site. This makes it very easy to distinguish what the particular web service connection is being used for, removing all ambiguity from the integration design. In my case, I did two separate integrations, one for Help Desk tickets and one for Marking Campaigns, which had two separate sites in SharePoint. When I created my connections to the web services, I could see right inside the URL what site I was connecting to.

Integration Overview

The integration that I will overview here is a Marketing Campaign integration, bringing Campaign data down to SharePoint lists. This includes not only the core data about the Campaign, but any related marketing material as attachments. The use case behind this scenario is that a company has users who need access to marketing material such as PowerPoint templates, email drafts, and white papers, but who do not have access to Dynamics CRM. This integration allows those users to utilize the corporate SharePoint server to gain access to materials that were previously only available inside Dynamics CRM.

When approaching this integration, several decisions had to be made up front about how this should run. I decided to make this a real time one way integration using Queue based integrations so that the data could be presented as soon as changes are made, and it would make use of multi-threading. In order to do this, I had to work outside of the box a bit. Because I need to bring all attachment data to SharePoint, I had to have a direct connection to my CRM system. This means that I could not just use the XML message as the source of my DTS file. So, to overcome this I used a Prompted Variable to capture the GUID of the campaign I was working with, and fed that into my source query using XPATH. That allows me to extract that CampaignID value from the XML that triggered the integration, and feed it into the source query at run time, giving me not only the campaign I care about, but also the attachments that come with it.

So, this setup will allow me to use a real time, multi-threaded integration, but I do not have to use XML as the source of the DTS file in order to make this happen.

Now, within the setup of the job itself there are some complex formulas that must be used in order to work with the SharePoint web services. There is the concept of an xmlAny attribute, which is how data must be sent to SharePoint. This attribute allows me to send in a piece of XML with field information and values to match what fields exist in SharePoint. It must be done this way because SharePoint is so easily customizable there is no way for the web service to constantly be re-configuring itself to display all the fields you are adding/changing within SharePoint. By using Scribe formulas to create XML header and node information, I am able to pass in whatever fields are available at that time, and if I need to make a change to add/remove a field, I simply alter the XML I am passing in. As you can see in the screen shot, I was able to create calculated variables which contained XML node information and data, and concatenate them together with XML header information to present a large block of XML with a minimal amount of data in my formula.

Another aspect in dealing with the SharePoint web service is working with return values. Much as is with passing data into SharePoint as XML, the values you receive from SharePoint are also formatted as XML. So, instead of creating formulas to stitch together XML, we must now find a way to parse the XML and locate the values we are looking for. I did this using text functions like FIND() and MID(), in order to locate the nodes I cared about, and get just that value from the XML. I could then use this in subsequent steps.

There are many more design details to this integration that can be found in our latest video within the Inside Track, if you would like to view this new video but are not yet a subscriber of the Inside Track, you can register for a free trial here using the registration code "InsideTrackTrial". For those of you who have already subscribed, there should be a new book on your shelf titled Integrating Dynamics CRM and SharePoint.