1

The need is to compile a list of fileIDs of 2 types of Salesforce files that contain a specific word within them.

  • Document Files in ContentVersion
  • Email Attachment files in Attachment

When using global search to search in FILES, an elegant list of matching files are returned (but without the file ID )

The goal is exclude these files/delete them prior to migrating to a new org.

It seems that the only option is to do 2 passes: Use Data Loader to search ContentVersion object, download the file using curl and then check if the word is present

Rinse and Repeat for Attachment object.

Is there a more elegant solution to somehow get to the file ID of these files so we can exclude them easily?

1 Answer 1

1

You can get a list of values using SOSL, such as:

FIND {word} IN ALL FIELDS RETURNING ContentVersion(Id, ContentDocumentId), Attachment

This would return a list of up to 2,000 rows of content version and/or attachment and the relevant ContentDocumentId so you can delete/exclude the records. Of course, you may want to review the data before deleting it, but this query should help narrow down your search (pun intended).

I'm not sure if the Data Loader can actually do SOSL, but there are plenty of tools out there that can.

11
  • 1
    @EashwerB.Iyer you can do progressive searches by adding a WHERE clause to each RETURNING to filter by, for example, Id or CreatedDate so you can find all the values. This is easy in Apex.
    – sfdcfox
    Mar 13, 2023 at 22:16
  • 1
    @EashwerB.Iyer Oh, I meant you could do it programmatically in Apex, so long as it's less than 10k files. For workbench, there should be a download CSV option.
    – sfdcfox
    Mar 14, 2023 at 15:08
  • 1
    @EashwerB.Iyer SOSL has a 2k limit across all returned types. You could balance it with RETURNING ContentDocument(WHERE ... LIMIT 1000), Attachment(WHERE ... LIMIT 1000). This would give you the proper estimates in any tool that supports SOSL, but I'd say it'll still be easiest in Apex.
    – sfdcfox
    Mar 14, 2023 at 18:05
  • 1
    @EashwerB.Iyer use something like ORDER BY ID ASC LIMIT 1000 for the first, then WHERE ID > (the last id from previous batch) ORDER BY ID ASC LIMIT 1000. But it would be a lot faster in Apex anyways.
    – sfdcfox
    Mar 14, 2023 at 18:27
  • 1
    @EashwerB.Iyer a single document can be attached to many records. You'll have to query the Content document link table with the content document id values to get that info. Parent id on content document actually refers to a Folder/Library, whatever they're calling it these days. Check out the docs on Content Version. It has all the related data you need.
    – sfdcfox
    Mar 14, 2023 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .