Scribe Software Blog

How to call a Stored Procedure in Scribe Online IS

2013-05-10T10:29:54-04:00

We have many customers interested in using Scribe Online IS to create Orders in their accounting application. This blog will describe how to do so with SQL Server as a backend from Opportunities in their CRM application.

To start, I need to know the next appropriate Order Number value when I am creating the Order in SQL, so will I have a Stored Procedure that already exists in SQL get me the next Order Number value. Once I have that value, I will be able to create my Sales Order correctly.

Although you cannot call stored procedures directly in the Scribe Online process definition editor, you can trigger them to execute on your database. Think of triggers like Workflows in the CRM application world. They are created to automatically execute code during events on a particular table. Read more about triggers. Here are additional resources for creating a trigger in specific databases:

Using a database trigger, I can have a stored procedure called to run a SQL query for Scribe to use with these three steps:

Create a staging table with a trigger that calls your stored procedure.
Modify or duplicate your stored procedure to write the results to your staging table.
Create a Scribe Online map to trigger the stored procedure and then Lookup the results.

Prerequisites:

Scribe Online account with an IS trial or subscription.
Scribe Online on-premise Agent installed and a Connection made to your database.
Basic knowledge of triggers and stored procedures (or aptitude to learn about SQL scripts).

Step 1 – Create the staging table and Trigger

I will create a new staging table (named dbo.Trigger) with an attached database trigger (named dbo. GetNextTrigger). Here’s what my Staging table and Trigger looks like:

CREATE TABLE [dbo].[Trigger](
      [Triggerbit] [bit] NULL,
      [DateTriggered] [datetime] NULL,
      [Guid] [uniqueidentifier] NULL,
      [Result] [int] NULL
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[GetNextTrigger]
ON [dbo].[Trigger]
AFTER INSERT
AS
BEGIN
EXEC [dbo].[GetNextNum]
END
GO

You will want to change the stored procedure (highlighted) to the name of yours. You will also want change the Result field(s) to be the correct data-type for the result of your stored procedure. The stored procedure must be located on the same database as the table/trigger.

Step 2 –Modify/Duplicate your Stored Procedure

I want to find the next order number using a SQL query. Once you’ve built a query to find the next number, or duplicated an existing stored procedure that does this, you will want to add a query to update those results to your staging table. This is what mine looks like:

ALTER PROCEDURE [dbo].[GetNextNum]
AS
BEGIN
SET NOCOUNT ON;
      DECLARE @Max INT
      SET @MAX = (SELECT COUNT(*) + 1 FROM [dbo].[salesorder] WITH(NOLOCK))
      UPDATE [dbo].[Trigger]
      SET [Result] = @Max
      WHERE ([Result] IS NULL OR [Result] = '')
END

Make sure that the name of your stored procedure matches the name you are using in your trigger if you needed to create or duplicate the stored procedure.

Step 3 – Creating a map to get the value returned by your Stored Procedure

Once you’ve created your new staging table, you’ll want to login to Scribe Online and create a connection to your database, or refresh the metadata on that database.

Regardless of your use-case, you will need to trigger the stored procedure. Your triggered table can be on either the source or target connection.

The simplest configuration should insert into the dbo.Trigger table with a unique value (1^st operation). Following the trigger, lookup the result field(s) of your previous step (2^nd operation). Then you can use the results of your lookup in any subsequent step (3^rd operation).

Here is a more detailed explanation of how I configured my map:

Figure 1: Step 1 – Create Trigger row operation Target mappings.

Using the result of my NEWGUID() that I am inserting on the 1^st operation (in Fig. 1 above), I will be able to search for the result of my Stored Procedure on the 2^nd operation Lookup Criteria (on Fig. 2 below). The GUID will ensure that I always limit me Lookup operation to the record I just created.

Figure 2: Step 2 – Lookup Stored Proc Result operation Lookup Criteria.

On the 2^nd operation, Lookup Stored Proc Result, click the Field List tab and check the Result(s) field(s) so that you can use it on subsequent operations.

Figure 3: The Result is available for target mapping.

On your 3^rd (or any subsequent) operations, you can use the Result field from your Stored Procedure (Fig. 3) to map to your target (Fig. 4). For each row you process in Scribe Online, the stored procedure will run to ensure each order has the correct order number.

Figure 4: Map the Result field from the 2^nd operation’s Lookup.

Even though I have three operations above for each source row, Scribe will only log them as 1 row processed:

Notes and considerations

My Stored Procedure runs very quickly, so I am able to use the result of it on the next step. If stored procedure runs slower, the result of it may not be committed to the database in time for Scribe to find it with your Lookup operation. You may be able to run other operations or retry your lookup with later operations or in a subsequent map.
Consider indexes on your database to optimize the performance of your Stored Procedure where it makes sense.
If when running your Scribe Online IS map, you receive row errors on each create operation to your staging table, there may be an issue with your trigger, stored procedure or both. Ensure that you can create rows in the staging table manually first.

Creating a Scribe Online Text Connection to a networked drive

2013-03-28T15:38:05-04:00

Using Scribe Online IS, you can connect to any source or target text file that is on the same network domain as your Scribe Online Agent – it doesn't need to be in the default Agent directory, as the Text as Source Wizard UI may suggest! This is great if you have multiple users pulling reports or performing exports that all need to be sent through Scribe. Here's a quick tutorial for configuring your connection to a networked drive.

Prerequisites:

Scribe Online account with an IS trial or subscription
An On-Premise Agent in your Local Area Network
Have user access to a networked location where your files will reside

There are three steps to connecting to your networked text file: add the folder location to the ScribeAccess.txt, change the Scribe Online Agent to run as a domain user and configure the text connection in Scribe Online.

Adding the UNC path to the Scribe FolderAccess.txt file

After installing an On-Prem Agent, navigate to the installation directory and find the Text Connector folder, typically located at: (C:\Program Files (x86)\Scribe Software\Scribe Online Agent\Connectors\Scribe.Connector.Text).

There is a file named FolderAccess.txt you will need to add the UNC path of your networked folder to. Even though on my computer, I can access my text file by going to S:\Nate\TextLeads.txt, it's best practice to add the full path to my FolderAccess file.

I found my UNC path by right-clicking on TextLeads.txt > choosing Properties > clicking Details > and scrolling to the Computer column. Using the computer name, I navigated in a Windows Explorer window to find my folder:

\\My Server Name\Users\Nate\

Now I can add the UNC path to my FolderAccess.txt back on the Scribe Agent server, save it:

Changing the Scribe Online Agent to be run by a domain user

Since this is a networked drive, my company has only allowed access to this folder to specific domain users. I need to make sure that my local Scribe Online Agent Windows Service is running as a user with access to that folder. By default my Agent is running as Local System, which does not have access to it.

Assuming you're a user that has access to this file, you can use your own account.
If you do not want to use your account or your company has a service user account, verify that user has permissions to the folder location: right-click the folder > Security > check for the user or group that includes the user exists on that list. You may need to contact your Network admin to add rights to this folder.

To change the user, open your Services (services.msc) and scroll to the Scribe Online Agent.

Select the Scribe Online Agent > right-click and choose Properties > navigate to the Log On tab > click the This Account radio button. Now enter your username or domain\username and password, then click apply and OK. Now restart your Scribe Online Agent by right-clicking the service and choosing Restart.

Creating a connection to your Text file in Scribe Online

The next thing to do is create the connection in Scribe Online. Login > navigate to the Connections tab > click New. Create your own Text as Source or Text as Target Connection as you normally would or by using the Help Center's guides.

Make sure that if you have multiple Agents, the Agent that would have connectivity to the file is chosen. Now add the same UNC path you added to the FolderAccess file to the "Directory Path"

One thing to note about Text as Source is the ability to archive the text file after it has been processed by a map. The below configuration would move my file to another folder and rename it similar to"Contacts_Test-01-31-2013101629576Z.csv"

This has similar functionality to the Archiver.exe that Insight users may use in Integration processes.

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

2013-02-04T08:16:40-05:00

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%

2012-12-31T13:31:39-05:00

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

2012-12-18T17:33:22-05:00

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.

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

2012-12-10T08:14:10-05:00

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.

New Scribe Insight Training for Beginners

2012-12-06T13:40:26-05:00

If you are new to Scribe Insight and are looking for basic training, we have a new training course that can help you get started.

This course, titled Scribe Insight: First Steps, is appropriate for any new Insight user who has no prior experience with Scribe Insight. Whether you are a new customer, a new partner, or if you are doing a trial, this course will help you on your way. If you were looking for an extended demonstration of Insight, this course would be appropriate for that too.

During the course we show you how to build an integration from scratch that takes data from a sample application, ScribeERP, that has a SQL Server database, and integrate that data with Dynamics CRM. Then we show you how to change the integration to process only the data that has changed in ScribeERP's database by configuring the source to use the standard datetime net change pattern. Next, we show you how to create a query Integration Process in the Console to automate the running of the integration. Along the way, we explain the features used to build this mini-integration.

The course is open to all, free of charge, and is delivered live twice each month by a web meeting. See this web page, http://scribesoft.com/Scribe-Insight-Training, for more details and to sign up for the course.

Write ERP Errors Message Back to CRM

2012-12-05T08:30:42-05:00

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

2012-11-20T16:14:10-05:00

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

2012-11-08T15:11:55-05:00

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/