Views:

Scenario Overview

In this scenario we have a requirement to move attachments from sent emails to Sharepoint. We wish to do this via Schedule so we can run this formula at off peak hours.

This can be beneficial to clear up storage from your CRM system.  

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • We create a formula that triggers whenever an Email Message is updated.
  • 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.  
  • We create a N52 Schedule that will trigger this formula by updating the Email Message. 

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

N52 Formula Manager Steps

The following set of steps assumes you are just using the out of the box Dynamics CRM 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 'Email Message'
  • 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

Schedule 

Now we create the Scheduler that will trigger the formula.

  • Go to Settings > N52 Schedule 
  • Click on + NEW
  • Give it the name "Moving Email Attachments to Sharepoint"
  • Change the Frequency to 'Daily'
  • Change the Schedule Type to 'Entity Update'. 
  • Set the Start DateTime to your 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 below into the Fetch Xml Query
  • Click Save
  • Click N52 Commands >Start Schedule. 

Fetch Xml Query 

<fetch version="1.0" output-format="xml-platform" mapping="logical" no-lock="true" distinct="true" >
   <entity name="email">
      <attribute name="activityid" />
      <order attribute="createdon" descending="false" />
      <link-entity name="activitymimeattachment" from="objectid" to="activityid" alias="ab">
         <filter type="and">
            <condition attribute="filesize" operator="gt" value="0" />
         </filter>
      </link-entity>
      <filter type="and">
         <condition attribute="statuscode" operator="in">
            <value>2</value>
            <value>3</value>
            <value>4</value>
         </condition>
      </filter>
   </entity>
</fetch>

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
  • Wait until the time you picked for the Start DateTime has passed.
  • 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.