Scenario Overview
In this scenario we have a requirement to move attachments from a Dynamics 365 system into SharePoint. This needs to be done for multiple entities: Account, Contact and Opportunities; and we want to move all attachments - old and new.
- We will create a formula that executes over the attachments entity - Note
- We will run this formula over the existing Notes via a Process Genie formula to move all the Notes to SharePoint in their own unique folders
- We will then set up a Schedule that will execute nightly to move any newly created notes to SharePoint
This is beneficial as it reduces storage requirements in your Dynamic 365 system.
North52 Decision Suite
The North52 Decision Suite solution works like this:
- We create a Decision Table that is executed by Workflow or whenever a Schedule is triggered
- The formula will create a SharePoint folder to store the Entity's Notes
- It will then move the Notes to the SharePoint folder
- A Schedule executes nightly to move all newly created Notes to SharePoint
Set up Formula, xCache, Process Genie and Schedule
Formula
The following set of steps assumes you are using the out of the box Dynamics 365 setup. But its easy to enhance if you have extra fields/requirements.
- Create a new formula, setting the following values in the Formula Guide:
- Source Entity set to Note
- Set Formula Type to Process Genie
- Select the Decision Table editor
- Change the Name of the formula to Send Attachments to SharePoint
- Set Mode to Server Side
- Set Display Format to String
- Click Save
- Right-click on the Decision Table and select Insert > Insert Decision Table. Repeat so that we have two new Decision Table sheets
- Rename the sheets by double clicking the sheet name tab and entering DocumentLocation and TransferAttachments
- Ensure the sheet are in the above order
- This will ensure that Document Location runs first. Decision Table sheets run from left to right.
-
DocumentLocation Sheet
- In sheet DocumentLocation, delete both columns by right-clicking and selecting Delete > Delete Column twice
- Right-click and select Insert > Insert Inline Calculation eight times
- We will use Inline Calculation instead of Global Actions as the columns will need to reference each other
- In cell A2 enter GetEntityType
- In cell B2 enter GetSiteId
- In cell C2 enter GetSiteUrl
- In cell D2 enter CreateOrRetrieveTopLevelSiteID
- In cell E2 enter FileName
- In cell F2 enter CreateOrRetrieveRelativeUrl
- In cell G2 enter FolderLocation
- For each corresponding cell in row 4, copy and paste the formulas below using the Advanced formula editor. To open up the advanced formula editor, click on the Plus sign (in the top right corner of the sheet) before copying and pasting.
GetEntityType
FindValue('annotation','annotationid',[annotation.annotationid],'objecttypecode','?','true')
GetSiteId
FindValue('sharepointsite', 'isdefault' , '1', 'sharepointsiteid', '?', true)
GetSiteUrl
FindValue('sharepointsite', 'isdefault' , '1', 'absoluteurl', '?', true)
CreateOrRetrieveTopLevelSiteID
SharePointCreateDocumentLocation(
'TopLevelSite',
'sharepointsite|' + GetVar('GetSiteId'),
GetVar('GetEntityType')+'|' + [annotation.objectid],
GetVar('GetEntityType'),
'sharepointdocumentlocationid' )
FileName
ReplaceMultiple([email.subject] + '_' + Replace(Upper(ToString([email.activityid])) , '-', ''), xCacheGetGlobal('CRMSharePoint_ReservedCharacters'), '-')CreateOrRetrieveRelativeUrl
SetVar('CreateOrRetrieveRelativeUrl',
SharePointCreateDocumentLocation('Documents - ' +
Left(FindValueQuickName(GetVar('GetEntityType'),[annotation.objectid] ), 50),
'sharepointdocumentlocation|' +
GetVar('CreateOrRetrieveTopLevelSiteID'),
GetVar('GetEntityType')+'|' + [annotation.objectid],
FindValueQuickName(GetVar('GetEntityType'),[annotation.objectid] ),
'relativeurl') )
FolderLocation
GetVar('GetSiteUrl') + GetVar('GetEntityType')+'/' + GetVar('CreateOrRetrieveRelativeUrl')
TransferAttachments Sheet
- In sheet TransferAttachment, delete the condition column by right-clicking in cell A2 and selecting Delete > Delete Column
- Right-click cell A2 again and select Insert > Insert Action twice so there are 3 Action columns
- In A2 enter CreateFolder
- In B2 enter CreateFile
- In C2 enter DeleteRecord
- For each corresponding cell in row 4, copy and paste the formulas below using the Advanced formula editor
CreateFolder
SharePointCreateFolder( GetVar('GetSiteUrl'), xCacheGetGlobal('CRMSharePoint_UserName'), xCacheGetGlobal('CRMSharePoint_Password'), true, GetVar('FolderLocation'), true )
CreateFile
SharePointCreateFile(
GetVar('GetSiteUrl'),
xCacheGetGlobal('CRMSharePoint_UserName'),
xCacheGetGlobal('CRMSharePoint_Password'),
true,
true,
GetVar('FolderLocation'), [annotation.filename], [annotation.documentbody] )
DeleteRecord
DeleteRecord('annotation',[annotation.annotationid])
- Click Save
xCache
The formula above uses North52 xCache functionality. Below we detail how to set up the two xCache records required:
- Go to Settings > N52 xCache
- Click the NEW button
- For Category enter CRMSharePoint
- For Base Key enter CRMSharePoint_UserName
- For OrgName Key enter your instance's Unique Name. You can find this by going to Settings > Customizations > Developer Resources
- Scroll down to Value Information and Under Value (Secured) enter the Username that needs to connect to SharePoint
- Click Save
- Click the NEW button again to create another record
- For Category enter CRMSharePoint
- For Base Key enter CRMSharePoint_Password
- For OrgName Key enter your instance's Unique Name. You can find this by going to Settings > Customizations > Developer Resources.
- Scroll down to Value Information and under Value (Secured) enter the password corresponding to the Username entered above
- Change Datatype to String(Password)
- Click Save
- Click on +NEW again
- Under Category , paste "CRMSharePoint"
- Under Base Key , paste "CRMSharePoint_ReservedCharacters"
- Under OrgName Key , paste in your Orgs Unique Name . You can find this by going to Settings > Customizations > Developer Resources.
- Scroll down to Value Information and under Value (Secured) , paste in ~, ,",#,%,&,*,:,<,>,?,|,\,/,{,},',’
- Click Save
Process Genie
Next, create a workflow and Process Genie to run this formula on the Notes we wish to move to SharePoint:
- Go to Settings > Processes
- Click the NEW button
- Give it the name Send Note attachments to SharePoint
- Set Category to Workflow
- Set Entity to Note
- Click OK
- Uncheck Record is Created
- Check As an on-demand process
- Click Add Step > North52 > N52 Process Genie
- Click Set Properties
- Enter the formula short code from the formula created above
- Click Save and Close
- Click Save again and then Activate
You can now manually run this workflow on the Notes records you wish to move to SharePoint.
Schedule
Next create the Scheduler record that will run the Workflow above every night. Note the Schedule is not needed if you wish to simply run the formula manually.
- Go to Settings > N52 Schedule
- Click the NEW button
- Give it the name Send Note attachments to SharePoint
- Change the Frequency to Daily
- Change the Schedule Type to Workflow
- Set Workflow to Send Note attachments to SharePoint
- Set the Start DateTime to the time you want the Schedule to start
- For testing purposes, you may wish to pick a time that is soon and then change this later
- Copy and paste the Fetch Xml query below into Fetch Xml Query
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="annotation"> <attribute name="subject" /> <attribute name="notetext" /> <attribute name="filename" /> <attribute name="annotationid" /> <order attribute="subject" descending="false" /> <filter type="and"> <condition attribute="createdon" operator="today" /> </filter> </entity> </fetch>
Click Save - Click N52 Commands > Start Schedule
