Tutorial: Schedule a Flow to Delete Old Files (ContentDocuments) – Featuring New ‘AddQuotesToFields’ Action, ExecuteSOQL, and ExtractStringsFromCollection v2

/3 Comments/in /by

Check out the video walk-through here.

With all of the new actions coming I thought I’d share a ‘bring it all together’ tutorial that outlines several actions here on USF:

I strongly recommend you reading up on Salesforce Files before tackling this, it will make the SOQL queries a lot easier to grasp.

One of the limitations (as many of you are aware) of the standard ‘Get’ elements of Flow is you that you cannot reference related fields like you can in SOQL. This usually isn’t a huge problem for standard and custom objects since you could in theory just create a formula field as a workaround. But what about objects you can’t create formula fields for like ContentDocumentLink?

Enter ExecuteSOQL. With this action you’ll be able to do the same things those fancy Apex guys and gals can do to manipulate and work with Files.

One “fun” limitation of querying ContentDocument is that you can’t use handy relationship queries for what you need – you have to get to them indirectly through ContentDocumentLink. The below example will not work:

SELECT Id
FROM ContentDocument
WHERE Id in (Select ContentDocumentID from ContentDocumentLink where LinkedEntityId in (blah blah blah)

What you’ll see developers frequently do is construct a List of ContentDocumentLinks and grab the ContentDocumentIds tied to each of them. Then they re-query ContentDocument to get their ContentDocument list which they can then update/delete/do whatever with. That’s what we’ll be doing in our second Flow!

View All Files Permission

Spring ’19 enabled the ‘View All Files’ permission which finally allows us to directly query all files in ContentDocument and ContentVersion. This is extremely handy – if you simply want to archive or delete ANY old file, you can now do that using simple SOQL queries or even a Get in Flow. You will need this enabled in Scenario 1.

Scenario 1 – Delete Old Files Posted by Community Users

Suppose you wanted to schedule a cleanup flow to delete old files posted by Community users – check out how you’d do this below!

NOTE: Always have fault paths on your actions! I skipped that for example-sake.

Step 1 – Use the ExecuteSOQL action with the below query to grab all Files that are > 2 years old and created by the Community User profile
Step 1 Continued – See above for the SOQL statement
Step 2 – For older versions of ExecuteSOQL you will need to assign the results to a number. The latest version of ExecuteSOQL posted this week does not need this step.
Step 3 – Do a decision on the number field you just assigned.
Step 4 – Delete the returned ContentDocuments from the ExecuteSOQL action

Scenario 2 – Delete Old Files Posted in Certain Accounts

A bit more advanced here – let’s say you wanted to clean up some files on a subset of records. In this case it would be a set of Accounts based on a Type field, but it could be any object. Check out the steps below on how you’d accomplish that.

NOTE: Always have fault paths on your actions! I skipped that for example-sake.

Step 1 – Set up your ExecuteSOQL action storing it in a ContentDocumentLink record collection variable. See below for the query
Step 2 – Create your SOQL statement. Fill in whatever Day value you want for grabbing old files.
Step 3 – For older versions of ExecuteSOQL you will need to assign the query results to a number to do your null check.
Step 4 – Do your null check decision on the number value we just set in the previous step.
Step 5 – Run the latest and greatest version of ‘ExtractStringsFromCollection‘ action to get a comma-separated text variable of all of the ContentDocumentIds
Step 6 – Run the new ‘AddQuotesToFields’ action to wrap each ID in quotes so that we can easily throw it into the next ExecuteSOQL action.

NOTE: If you don’t want to install this action you could still create a Loop to create your own ContentDocument collection.
Step 7 – Run the last ExecuteSOQL action using the query below to generate your ContentDocument collection you will be deleting.
Step 7 Continued – Here’s the query you will run. Notice how we throw in the output of the ‘AddQuotesToFields’ action into the query itself.
Step 8 – Delete your files!