Use SmartConnect to map Microsoft Dynamics GP attachments to CRM notes

Maureen Franz / May 08, 2017
Use SmartConnect to map Microsoft Dynamics GP attachments to CRM notes

A customer recently asked us to transfer files attached to SOP records from GP to CRM sales orders.  For this customer, these files are typically PDFs or Word documents.  The task can be completed very easily using SmartConnect.  To set up the map, you need to create your data source, destination and mappings.

First, create your data source (Figure 1).  In our case, the data source type is “Bulk Data Load”.  Data source is “Microsoft Dynamics GP Query”.  You will need to select the connection type/data source for your organization. 

Click Edit SQL to add your GP query.  The basic query for this map will be similar to the following.  It selects the document number, unique id for the attachment, the filename and the document (BinaryBlob).  In our case we only want attachments for orders (SOPType = 2) that have not been deleted (Delete1 <> 1).
 

SELECT  

CO00105.DOCNUMBR, 

CO00105.ATTACHMENT_ID, 

CO00105.FILENAME, 

coAttachmentItems.BinaryBlob   

FROM CO00105   

INNER JOIN SOP10100   

ON CO00105.DOCNUMBR=SOP10100.SOPNUMBE    

INNER JOIN coAttachmentItems   

ON CO00105.ATTACHMENT _ID= coAttachmentItems.ATTACHMENT _ID    

WHERE SOPTYPE = 2 AND CO00105.Delete1 <> 1


Click Preview to check that your query pulls the data you want. 

Next, set the Key Fields: Document number and Attachment_ID.


Figure 1: Data Source setup


To set up the destination (Figure 2), select Destination Type = “Microsoft Dynamics CRM”.  Select the server and port for your organization.  Click the ellipsis to open the list of entities available.  Check the box next to Note (technical name = annotation) to select the Entity to Process.  


m2.png
Figure 2: Destination setup


Next we will map the GP values to the CRM note.  Double click “Note (annotation)” to open the SmartConnect mapping screen. 

Click “Additional Columns” to create the two additional columns we’ll need for the mapping.  The first one is an Entity Lookup to link our note to the associated sales order.  (Figure 3)


SmartConnect-Microsoft-Dynamics-GP-SC3
Figure 3: Entity Lookup column


The second additional column is a calculated field required to convert the BinaryBlob column to a Base 64 String using the function Convert.ToBase64String(_BinaryBlob). (Figure 4)


SmartConnect-Microsoft-Dynamics-GP-SC4
Figure 4: BinaryBlob conversion column


Make the following mappings (Figure 5):

  • FILENAME to File Name

  • LK_ORDER to Regarding

  • ATTACHMENT_ID to Description

  • DOCNUMBR to Title

  • CALC_DOCUMENT_BODY to Document

  • For the destination column “Is Document”, select Local Constant as column type.  Then enter the value “True”
     

m5.png
Figure 5: Final mappings
Maureen Franz
About the Author


Maureen Franz
Access Programmer

Maureen has been a developer at HBS for 6 years.  During her time here, she has worked with several different technologies and has recently expanded into integrations using SmartConnect with multiple data sources/destinations, including Great Plains, CRM, SAGE, SQL Server, etc.

Comments
Blog post currently doesn't have any comments.