Integrating Microsoft Dynamics GP Sales Order and Invoice serials / lots into Microsoft Dynamics CRM Order / Invoice lines

  • Article

Integrating serial and lot numbers between Dynamics GP and Dynamics CRM can be valuable if you need visibility into the serial numbers or lots associated to a sales document line within Microsoft Dynamics CRM. 

 

By default Serials / lots that are associated to Microsoft Dynamics GP Sales order or Invoice lines are NOT integrated into Microsoft Dynamics CRM Sales Order or Invoice Lines. This post will help you in integrating these fields between Microsoft Dynamics GP and Microsoft Dynamics CRM. 

 

Please note that the function C# code that is shown here will be incorporated in the Microsoft Dynamics GP to Microsoft Dynamics CRM function library in a future release of Connector for Microsoft Dynamics so you will not have to use the Connector SDK to create these functions after they are officially released, you will however; still need to update your maps as needed as well as preform the proper customizations to Microsoft Dynamics CRM. 

To accomplish this we will do the following high level steps documented in more detail below:

  1. Add a field to the Order Product and Invoice Product entities in Microsoft Dynamics CRM
  2. Update Microsoft Dynamics CRM Object Configurations
  3. Create Mapping Helpers (this code will be incorporated into a future version of Connector for Microsoft Dynamics)
  4. Map Microsoft Dynamics GP Serial Lot information to CRM

Add a field to Order Product and invoice Product entities in CRM

Since Serial Number and Lot numbers are mutually exclusive on Microsoft Dynamics GP order lines, we will create 1 field in Microsoft Dynamics CRM for both (separate fields could easily be used).

  1. In Microsoft Dynamics CRM, go to:
    Settings>>Customizations>>Customize the System
  2. In the Default Solution customization window, go
    to : Entities>>Order Product>>Forms
  3. Open the Main Form
  4. At the lower right hand corner click the "New
    Field    " button
    1. Set Display Name to "Serial\Lots"
    2. Set Name to "seriallots"
    3. Set Type to "Multiple Lines of
      Text"
    4. Set Max length:
      1. Max length needs to be large enough to hold a serial number for each quantity expected
        1. The maximum length for a Microsoft Dynamics GP
          Serial or lot number is 20 characters
        2. The default for 2000 characters should support
          about 100 serial numbers per sales line
    5. Click: Save and Close
  5. From the Field Explorer on the right side of the
    window Drag the Serial\Lots field onto the form next to the Price Per Unit
    field
  6. Click change properties found on the home tab of
    the ribbon
  7. On the display tap check Field is read-only
  8. On the formatting tap set number of rows = 7
  9. Click Ok
  10. Click Save and Close
  11. Repeat steps 2 - 10 for Invoice Product
  12. Click Publish All Customizations

Your Order product form should like similar to this:

Update Microsoft Dynamics CRM Object Definitions

Make sure that you have published the customization made above before continuing.

After customizing Microsoft Dynamics CRM to add the new fields to the order and invoice product entities, you will need to run the Microsoft Dynamics CRM Adapter Configuration Utility to expose the new field in the mapping UI.

1. Open the Connector client
2. Click the Adapter Settings toolbar button
3. Select the Microsoft Dynamics CRM Adapter
4. Click the Configure Microsoft Dynamics CRM link
5. Click Next
6. Enter the administrator account information
7. Click Get Organizations
8. Select the Microsoft Dynamics CRM organization modified above
9. Click next
10. Verify the Order and Invoice entities are selected in the tree view


11. Important: Make sure the "Skip complete configuration and only generate entity configuration" check box is checked
12. Click next
13. Click Configure
14. After configuration is complete click Finish
15. Close and reopen the Connector Client

Map Microsoft Dynamics GP Serial / Lot information to Microsoft Dynamics CRM

This step assumes you have already created the mapping helper shown below using the Connector SDK.

Since Microsoft Dynamics GP Serial numbers and Lot numbers are mutually exclusive on the sales line we will map them both to the same field (Serial\Lots) in Microsoft Dynamics CRM using the concatenate function.

  1. Select the Microsoft Dynamics GP Sales Order to Order map
  2. Navigate to Order>All OrderDetails>Order Product>Serial Lots
  3. Click the map Destination button
  4. Click Use function
  5. Set the Function Category to String
  6. Select the function "Concatenate"
  7. Click next
  8. In the first values field Click the Map Destination button
  9. Click Use Function
  10. Set the Function Category to MSDN Blog Mapping Helper
  11. Select the function "ConcatenateSerialLotNumber"
  12. In the serialLots field, select source field: Sales Order Lines\item\Serial Numbers
  13. Click Add
  14. In the second values field Click the Map Destination button
  15. Set the Function Category to MSDN Blog Mapping Helper
  16. Select the function ConcatenateSerialLotNumber(serialLots) : String
  17. In the serialLots field, select source field: Sales Order Lines\items\Lots
  18. Click add
  19. Click add
  20. The string in the Serial\Lots field should look as follows:

=Concatenate(ConcatenateSerialLotNumbers(Sales Order Lines\item\Serial Numbers), ConcatenateSerialLotNumbers(Sales Order Lines\item\Lots))

Create a Mapping Helper

Here is the mapping helper used in the above example. It iterates over a passed in SalesSerialLot Array concatenating the serial\lot numbers delimited by the specified string. If you need more information (such as MFD date) from the serial or lot this method could be modified to include more. For more information on creating mapping functions using the Connector for Microsoft Dynamics SDK, see page 13 in the Connector SDK documentation.

 

 using System;
 using System.Text;
 using Microsoft.Dynamics.Integration.Adapters.Gp2010.GPWebService;
 
 namespace Microsoft.Dynamics.Integration.Mapping.Helpers.BlogMappingHelper
 {
 /// <summary>
 /// The <c>CrmGp2010Helper</c> class contains <c>MappingFunctions</c> targeted for use when integrating Microsoft Dynamics GP 2010 and Microsoft Dynamics CRM.
 /// </summary>
 [MappingHelper]
 public class BlogMappingHelper : LocalizedMappingHelper
 {
 private const string BlogMappingHelperDescription = "MSDN Blog Mapping Helper";
 /// <summary>
 /// Concatenate GP serial\lot numbers into a single delimited list.
 /// </summary>
 /// <param name="serialLots">The collection of GP <c>SalesSerialLot</c> objects.</param>
 /// <returns>A string containing all the serial\lot numbers in the collection delimited by newline.</returns>
 [MappingFunction(Description = "Concatenates the serial\\lot numbers from the GP serial\\lot numbers collection into a single string, delimited by newline.")]
 [MappingFunctionCategory(Category = BlogMappingHelperDescription)]
 public static string ConcatenateSerialLotNumbers(SalesSerialLot[] serialLots)
 {
 return ConcatenateSerialLotNumbers(serialLots, Environment.NewLine);
 }
 
 /// <summary>
 /// Concatenate GP serial\lot numbers into a single delimited list.
 /// </summary>
 /// <param name="serialLots">The collection of GP <c>SalesSerialLot</c> objects.</param>
 /// <param name="delimiter">A string to delimit the serial\lot numbers with.</param>
 /// <returns>A string containing all the serial\lot numbers in the collection delimited by the specified string.</returns>
 [MappingFunction(Description = "Concatenates the serial\\lot numbers from the GP serial\\lot numbers collection into a single string, delimited by the specified string.")]
 [MappingFunctionCategory(Category = BlogMappingHelperDescription)]
 public static string ConcatenateSerialLotNumbers(SalesSerialLot[] serialLots, string delimiter)
 {
 if (delimiter == null)
 {
 delimiter = string.Empty;
 }
 var serialLotsStringBuilder = new StringBuilder();
 if (serialLots != null && serialLots.Length > 0)
 {
 for (int i = 0; i < serialLots.Length; i++)
 {
 if (serialLots[i] != null)
 {
 if (serialLots[i] is SalesSerial && !string.IsNullOrEmpty((serialLots[i] as SalesSerial).SerialNumber))
 {
 serialLotsStringBuilder.Append((serialLots[i] as SalesSerial).SerialNumber);
 serialLotsStringBuilder.Append(delimiter);
 }
 else if (serialLots[i] is SalesLot && !string.IsNullOrEmpty((serialLots[i] as SalesLot).LotNumber))
 {
 serialLotsStringBuilder.Append((serialLots[i] as SalesLot).LotNumber);
 serialLotsStringBuilder.Append(delimiter);
 }
 }
 }
 }
 return serialLotsStringBuilder.Length > 0 ? serialLotsStringBuilder.ToString(0, serialLotsStringBuilder.Length - delimiter.Length) : null;
 }
 }
 }