Views:

Scenario Overview

In this scenario we have a requirement to move attachments from sent emails to Sharepoint. This can be beneficial to clear up storage from your Dynamics 365 system.  

We will do this using multiple Decision Table sheets. 

North52 Decision Suite Solution

The North52 Decision Suite solution works like this,

  • We create a formula that triggers on the save event of an Email.
  • The formula checks the status of the email.
  • If the email is at status Sent, the formula will move the attachments of the email to its Sharepoint.  

Note this Formula has 3 parts /formula sheets - Status Validation , DocumentLocation and TransferAttachments

 

North52 Decision Suite Steps

The following set of steps assumes you are just using the out of the box Dynamics 365 setup. But its easy to enhance if you have extra fields \ requirements.

  • Create a new Decision Table of type 'Save - Perform Action'
  • Set the Mode is set to 'Server' Side
  • Set the Event field to 'Create & Update'
  • Set the Source Entity to 'Email'
  • Set the Source Property to 'Status Reason'
  • Expand Deployment Settings and set Pipeline Stage to 'Post-Operation (Asynchronous )'
  • Right click on the Decision table and select Insert > Insert Decision Table
  • Right click  on the Decision table and select Insert > Insert Decision Table again so that we have 3 Decision Table sheets
  • Rename the 3 Decision Tables Sheets to 'StatusValidation', 'DocumentLocation' and 'TransferAttachments'.
    • You can rename them by double clicking the sheet name at the bottom. 
  • Ensure the sheet are in the order in the above screenshots.
    • This will ensure that StatusValidation runs first. Decision Table sheets run from left to right.
  • Below we have the set up of each sheet. 

Status Validation Sheet : 

  • Click on A2
  • Open up the Source tab and expand Source. 
  • Click on Status Reason
  • Click on A4
  • Expand Status Reason and click on 'Sent'
  • Click on cell B2
  • Right click and select Set Action Column Options > Set Serverside Action
  • Click on B5 
  • Go to the Function tab and search for "ExitAllDecisionTables"
  • Select the function ExitAllDecisionTables()
    • This setup will ensure that this Decision Table only runs for Emails with Status equal to 'Sent'. All other emails will exit the decision table here. 

 

DocumentLocation Sheet :     

  • Right click on cell A2 and select Delete > Delete column , do this twice.
  • Right click on cell A2 and select Insert > Insert Inline Calculation . Do this 6 times. 
    • We will use Inline Calculation instead of Actions as the columns will need to reference each other. 
  • In Cell A2 paste 'GetSiteId'
  • In Cell B2 paste 'GetSiteUrl'
  • In Cell C2 paste 'CreateOrRetrieveTopLevelSiteID'
  • In Cell D2 paste 'FileName'
  • In Cell E2 paste 'CreateOrRetrieveRelativeUrl'
  • In Cell F2 paste 'FolderLocation'
  • For row 4, Copy and paste their corresponding formula into the Advanced formula editor . The formula can be found below in this article.
    • To open up the Advanced formula editor b, click on the Plus sign (in the top right corner of the sheet) before copying and pasting. 

 


TransferAttachments Sheet : 

  • Right click on cell A2 and select Delete > Delete column
  • Right click on cell A2 and select Insert > Insert Action , do this three times. 
  • Right click on cell A2 and select Insert > Insert ForEachRecord
  • Delete Set Name and type "FindAttachment"
  • Right click and select Operations > Toggle Advanced Mode.
  • Click on A3 and then go to the Functions tab. 
  • Search for "FindRecords"
  • Shift click the FindRecords() function to open up its wizard
  • For Friendly Name , leave it blank. 
  • For Entity Name, select 'Attachment'
  • For Where Attribute, select 'Regarding'
  • For Equals Value, go to the source tab and expand Source
  • Search for and select 'Email Message'
  • For Select Attribute , leave it at *
  • Leave all other Parameters at their default
  • Click Generate. 
  • Right click and select Operations > Toggle Advanced Mode to turn off Advanced mode. 
  • In B2 ,paste 'CreateFolder'
  • In C2 , paste 'CreateFile'
  • In  D2 , paste 'DeleteRecord'
  • For row 4, Copy and paste their corresponding formula into the Advanced formula editor . The formula can be found below in this article.
    • To open up the Advanced formula editor b, click on the Plus sign (in the top right corner of the sheet) before copying and pasting. 

 

 

Formula 

Below we have listed the formula parts of each decision table. They can be copied and pasted into your Decision table. 

 

StatusValidation Sheet

Status Reason /  [email.statuscode]

 

{Sent}

 

ServerSide

 

ExitAllDecisionTables()

 

 

DocumentLocation Sheet


GetSiteId

 

FindValue('sharepointsite', 'isdefault' , '1', 'sharepointsiteid', '?', true)

 

GetSiteUrl

 

FindValue('sharepointsite', 'isdefault' , '1', 'absoluteurl', '?', true)


 

 

CreateOrRetrieveTopLevelSiteID

 

SharePointCreateDocumentLocation( 'TopLevelSite', 'sharepointsite|' + GetVar('GetSiteId') , 'email|' + [email.activityid], 'email', 'sharepointdocumentlocationid')

 


FileName

 

 ReplaceMultiple([email.subject]  + '_' + Replace(Upper(ToString([email.activityid])) , '-', ''), xCacheGetGlobal('CRMSharePoint_ReservedCharacters'), '-')

 

 

CreateOrRetrieveRelativeUrl

 

ReplaceMultiple(
  SharePointCreateDocumentLocation('Documents - ' +     
    Left(ReplaceMultiple([email.subject], xCacheGetGlobal('CRMSharePoint_ReservedCharacters'), '-'), 50),
    'sharepointdocumentlocation|' +  GetVar('CreateOrRetrieveTopLevelSiteID') ,
    'email|' + [email.activityid],
    GetVar('FileName'), 
    'relativeurl')  , 
  xCacheGetGlobal('CRMSharePoint_ReservedCharacters'), '-') 

 

 

FolderLocation

 

GetVar('GetSiteUrl') + '/email/' + GetVar('CreateOrRetrieveRelativeUrl')

 

TransferAttachments Sheet

 

FindAttachments

 

FindRecords('activitymimeattachment','activityid',[email.activityid],'*','50','true')

 

 

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'),
  ReplaceMultiple(CurrentRecord('filename'),
    xCacheGetGlobal('CRMSharePoint_ReservedCharacters'),
    '-'),
  CurrentRecord('body') )

 

 

DeleteRecord

 

DeleteRecord('activitymimeattachment', CurrentRecord('activitymimeattachmentid'))

 

 

xCache

 

The formula above uses N52's xCache functionalty. Below we detail how to set up the two xCaches you will need. 

  • Go to Settings > N52 xCache
  • Click on + NEW
  • Under Category , paste "CRMSharePoint"
  • Under Base Key , paste "CRMSharePoint_UserName"
  • 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 your Admins username
  • Click Save
  • Click on +NEW again
  • Under Category , paste "CRMSharePoint"
  • Under Base Key , paste "CRMSharePoint_Password"
  • 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 your Admins Password
  • Also 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

 

Testing 

We now will test this by creating an email, attaching files to it and sending. We will then check to make sure the files that were attached have been moved to its Sharepoint. 

  • Go to Sales > Activities 
  • Click on the Email icon in the top left. 
  • Fill out the email details and Save
  • Now attach files. 

  • Send the email. 
  • Go back to Sales > Activities  and open up the email view. 
  • Refresh until you see the email has been successfully sent
  • Open up the email and note that the attached files are now gone. 
  • Click on the arrow next to  Sales >Activities > Your emails name > at the top bar
  • Select Documents

  • You should see that the attached files have been moved to Sharepoint. 

 

Wizard - FindValue()

Please see below the wizard you can use to create the FindValue() function used in this formula. 

 

Did you know?

North52 Decision Suite helps streamline complex business processes

North52 Decision Suite is a cloud-based or on-premise decision management/decision engine platform for the Dynamics 365 and Power Platform that uses data, analytics and business rules to automate decisions.

Companies use North52 to streamline their processes, improve the quality of the decisions inside their Dynamics 365 systems, and shorten the time it takes to adjust to business and competitive changes.

Learn more about the North52 Decision Suite

Keywords: xCache, Decision Table, WebFusion, ExitAllDecisionTables, ToString, FindValue, FindRecords, ForEachRecord, CurrentRecord, DeleteRecord, Left, Replace, Upper, DecisionTable, GetVar, SharePointCreateDocumentLocation, SharePointCreateFile, SharePointCreateFolder, xCacheGetGlobal, ReplaceMultiple, Formula Manager