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.