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).






FROM CO00105   



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.  

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)

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)

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”

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.

Blog post currently doesn't have any comments.