Custom File and Attachment Search in Salesforce

Standard Salesforce functionality has extremely poor file search, and this can become a limiting factor when deciding whether to use Salesforce for file storage. Standard File (aka Chatter File) and Attachment search is done via the Salesforce Search bar at the top of the page, and any matching results (which seems to only return exact word matches in my example), display neither the object the file is on nor the name of the record it's on, which can make it difficult to know which file is the one you want without clicking on every result and then clicking on the parent record on the File or Attachment page.

Standard Salesforce File and Attachment Search

Consequently, to help fill the Salesforce file search gap, I came up with the idea to create a custom Visualforce page that would search both Files and Attachments at the same time (in case your organization uses only Attachments, used Attachments but now uses Files, or only uses Files) and display any results in one combined exportable list with relevant information and links on the files and their parent records. I also wanted to do it to practice a number of things I had done rarely or never, like:

• Custom Metadata Types
• Wrapper Classes (with Comparable Interface)
• Exporting CSVs from a Visualforce Page
• Dynamic SOQL Queries

As I do with a lot of my Salesforce code, I take bits and pieces freely available from all over the web, and the SortOptionList method in the Controller class I took directly from this excellent post by Jason Hartfield in 2010. Also, since the "Search Object" Custom Metadata Type is not constructed via code but rather declaratively in the Salesforce interface, I wanted to clarify that Search Object uses only two relevant fields: the "Label" for the sObject's Label and "Custom Metadata Record Name" for the sObject's API Name.

Below you can see a screenshot of the Visualforce page ("FileSearch") once implemented. The Visualforce page refers to a custom Apex Controller class ("FileSearchController") to return and clear results and which in turn refers to another Visualforce page ("FileSearchExport") to export the results. Finally, I provided an Apex test class for the Controller ("FileSearchControllerTest"). [In my instance, I also created a custom Visualforce Tab to direct users to the File Search page]

Custom Visualforce Page to Search File and Attachments

Please let me know in the Comments if you see any errors or places for improvement and feel free to use any of this code as you see fit. And now the code!

<!-- Visualforce Page= "FileSearch" -->
<apex:page controller="FileSearchController" docType="html-5.0">  
    <apex:form >
        <apex:pageBlock >
            
            <!-- Search, Clear, and Export buttons reference FileSearchController -->
            <apex:pageBlockButtons location="top">
                <apex:commandButton value="Search" action="{!searchFiles}" reRender="file-table" status="status" title="Maximum of 100 Files and 100 Attachments will be returned, sorted by created most recently."/>
                <apex:actionStatus id="status">
                    <apex:facet name="start">Processing...</apex:facet>
                </apex:actionStatus>
                <apex:commandButton value="Clear" action="{!clearFiles}"/>
                <apex:commandButton value="Export" action="{!exportCSV}"/>
            </apex:pageBlockButtons>
        
            <apex:pageBlockSection title="Search Criteria" columns="1" collapsible="false">
                
                <!-- Input fields receive keyword phrase, objects to search, and date range -->
                <apex:pageBlockSectionItem helpText="Enter keyword or keyword phrase to search for.">
                    <apex:outputLabel value="Keyword or Phrase"/>
                    <apex:inputText value="{!name}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem helpText="Press down on Ctrl (on PC) or Command (on Mac) to select multiple Objects. Add new Objects to search via Search Object Metadata Component Type.">
                    <apex:outputLabel value="Objects to Search"/>
                    <apex:selectList value="{!searchObjects}" multiselect="true" label="Objects to Search">
                        <apex:selectOptions value="{!Objects}"/>
                    </apex:selectList>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem helpText="If Start Date not entered, search will return all files and attachments created through End Date.">
                    <apex:outputLabel value="Start Date"/>
                    <apex:input type="date" value="{!startDate}"/>
                </apex:pageBlockSectionItem>
                <apex:pageBlockSectionItem />
                <apex:pageBlockSectionItem helpText="If End Date not entered, search will return all files and attachments created through today.">
                    <apex:outputLabel value="End Date"/>
                    <apex:input type="date" value="{!endDate}"/>
                </apex:pageBlockSectionItem>
                
           </apex:pageBlockSection>
                
           <apex:pageBlockSection title="Results" id="file-table" columns="1" collapsible="false" >
                
                <!-- Table displays results of search -->
               <apex:pageBlockSectionItem >
                    <apex:pageBlockTable value="{!sObjWrapList}" var="f">
                        <apex:column headerValue="Name">  
                            <apex:outputlink value="/{!f.id}">{!f.title}</apex:outputlink>  
                        </apex:column>
                        <apex:column headerValue="Description" value="{!f.description}"/>
                        <apex:column headerValue="File Extension" value="{!f.fileextension}"/>
                        <apex:column headerValue="Created Date">
                            <apex:outputText value="{0,date,MM'/'dd'/'yyyy}">
                                <apex:param value="{!f.createddate}"/>
                            </apex:outputText>
                        </apex:column>
                        <apex:column headerValue="Parent Type" value="{!f.parenttype}"/>
                        <apex:column headerValue="Parent Record">  
                            <apex:outputlink value="/{!f.parentid}">{!f.parentname}</apex:outputlink>  
                        </apex:column>
                    </apex:pageBlockTable>
                </apex:pageBlockSectionItem>
                
            </apex:pageBlockSection>
            
        </apex:pageBlock>
    </apex:form>
</apex:page>

<!-- Visualforce Page= "FileSearchExport" -->
<apex:page controller="FileSearchController" contentType="application/vnd.ms-excel#filesearchexport.csv" readOnly="true">{!header}
<apex:repeat value="{!sObjWrapList}" var="f">
{!f.id},"{!f.title}","{!f.description}","{!f.fileextension}","{!f.createddate}","{!f.parenttype}","{!f.parentid}","{!f.parentname}"
</apex:repeat>
</apex:page>

public with sharing class FileSearchController {

    // Create public variables for methods to use
    public List<Attachment> attachments {get;set;}
    public List<ContentDocumentLink> contentDocumentLinks {get;set;}
    public List<sObjectWrapper> sObjWrapList {get;set;}
    public string header {get;set;}
    public String name {get;set;}
    public List<String> searchObjects {get;set;}
    public Date startDate {get;set;}
    public Date endDate {get;set;}
    
    // Initialize variables on Visualforce Page load
    public FileSearchController() {
        attachments = new List<Attachment>();
        contentDocumentLinks = new List<ContentDocumentLink>();
        sObjWrapList = new List<sObjectWrapper>();
    }
    
    // Search Attachment and Chatter Files for keyword phrase on specified objects
    public void searchFiles(){
        
        // Clear previous results
        sObjWrapList.clear();
        
        // Construct query term for Date Range
        DateTime startDateTime = startDate == null ? null : DateTime.newInstance(startDate.year(),startDate.month(),startDate.day(),0,0,0);
        String startDateTimeGMT = startDateTime == null ? null : startDateTime.formatGMT('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
        DateTime endDateTime = endDate == null ? null : DateTime.newInstance(endDate.year(),endDate.month(),endDate.day(),23,59,59);
        String endDateTimeGMT = endDateTime == null ? null : endDateTime.formatGMT('yyyy-MM-dd\'T\'hh:mm:ss\'Z\'');
        String dateRange = '';
        if (startDate != null && endDate != null)
            dateRange = ' AND CreatedDate >= ' + startDateTimeGMT + ' AND CreatedDate <= ' + endDateTimeGMT;
        else if(startDate != null && endDate == null)
            dateRange = ' AND CreatedDate >= ' + startDateTimeGMT;
        else if (startDateTime == null && endDateTime != null)
            dateRange = ' AND CreatedDate <= ' + endDateTimeGMT;
        
        // Only perform search if keyword phrase supplied
        if(!String.isBlank(name)) {
            // Search attachments and then create wrapper class records from attachments
            string searchqueryAttachments = 'SELECT Name, Id, Description, ContentType, ParentId, Parent.Type, Parent.Name, CreatedDate FROM Attachment WHERE Parent.Type IN :searchObjects AND (Name LIKE \'%'+name+'%\' OR Description LIKE \'%'+name+'%\')' + dateRange + ' ORDER BY CreatedDate DESC LIMIT 100';
            attachments = Database.query(searchqueryAttachments);
            for(Attachment att: attachments) {
                sObjectWrapper sobjWrap = new sObjectWrapper();
                sobjWrap.id = att.Id;
                sobjWrap.title = att.Name;
                sobjWrap.description = att.Description;
                sobjWrap.fileextension = att.Name.substringAfterLast('.') != null ? att.Name.substringAfterLast('.') : att.ContentType;
                sobjWrap.parentid = att.ParentId;
                sobjWrap.parenttype = att.Parent.Type;
                sobjWrap.parentname = att.Parent.Name;
                sobjWrap.createddate = att.CreatedDate;
                sObjWrapList.add(sobjWrap);
            }
            
            // First create set of Content Document Ids since can only query Content Document Link with filter on ContentDocumentId or LinkedEntityId
            List<ContentDocument> contentDocumentList = Database.query('SELECT Id FROM ContentDocument USING SCOPE Team WHERE (Title LIKE \'%'+name+'%\' OR Description LIKE \'%'+name+'%\')' + dateRange);
            Set<Id> contentDocumentIds = new Map<Id,ContentDocument>(contentDocumentList).keySet();
            // Only search if contentDocumentIds since otherwise will return error
            if(!contentDocumentIds.isEmpty()) {
                string searchqueryContentDocumentLink = 'SELECT Id, ContentDocumentId, LinkedEntityId, ContentDocument.Title, ContentDocument.Description, ContentDocument.FileExtension, ContentDocument.CreatedDate, LinkedEntity.Type, LinkedEntity.Name FROM ContentDocumentLink WHERE ContentDocumentID IN :contentDocumentIds ORDER BY ContentDocument.CreatedDate DESC LIMIT 100';
                contentDocumentLinks = Database.query(searchqueryContentDocumentLink);
                // Create wrapper class records for Content Documents if Linked Entity type one of specified Objects
                for(ContentDocumentLink cdl: contentDocumentLinks) {
                    Set<String> setObjects = new Set<String>(searchObjects);
                    if(setObjects.contains(cdl.LinkedEntity.Type)) {
                        sObjectWrapper sobjWrap = new sObjectWrapper();
                        sobjWrap.id = cdl.ContentDocumentId;
                        sobjWrap.title = cdl.ContentDocument.Title;
                        sobjWrap.description = cdl.ContentDocument.Description;
                        sobjWrap.fileextension = cdl.ContentDocument.FileExtension;
                        sobjWrap.parentid = cdl.LinkedEntityId;
                        sobjWrap.parenttype = cdl.LinkedEntity.Type;
                        sobjWrap.parentname = cdl.LinkedEntity.Name;
                        sobjWrap.createddate = cdl.ContentDocument.CreatedDate;
                        sObjWrapList.add(sobjWrap);
                    }
                }
            }
            // Sort list since Attachment and Content Documents added in blocks and won't automatically sort by Created Date
            sObjWrapList.sort();
        }
    }
    
    // Create public class to hold both Attachment and Chatter File record    
    public class sObjectWrapper implements Comparable {
        public String id {get;set;}
        public String title {get;set;}
        public String description {get;set;}
        public String fileextension {get;set;}
        public String parentid {get;set;}
        public String parenttype {get;set;}
        public String parentname {get;set;}
        public DateTime createddate {get;set;}
        
        // Define sort logic since Attachment and Content Documents will be added in blocks and won't automatically sort by Created Date
        public Integer compareTo(Object compareTo) {
            sObjectWrapper sobjWrapSort = (sObjectWrapper) compareTo;
            if (this.createddate == sobjWrapSort.createddate) return 0;
            if (this.createddate < sobjWrapSort.createddate) return 1;
            return -1;
        }
    }
    
    // Action to clear criteria fields
    public void clearFiles(){
        name = '';
        searchObjects.clear();
        startDate = null;
        endDate = null;
        sObjWrapList.clear(); 
    }
    
    // Export search results to CSV
    public PageReference exportCSV() {
        PageReference csvPage = Page.FileSearchExport;
        header = 'Id,Title,Description,File Extension,Created Date,Parent Type,Parent Id,Parent Name';
        return csvPage;
    }
    
    // Get list of Objects for select options
    public List<SelectOption> getObjects() {
        // Add Objects to SelectOptions for display on Visualforce Page
        List<SelectOption> options = new List<SelectOption>();
       // Query Custom Metadata Type to find Search Objects
        for (Search_Object__mdt so : [SELECT MasterLabel, DeveloperName FROM Search_Object__mdt]) {
            options.add(new SelectOption(so.DeveloperName, so.MasterLabel));
        }
        return SortOptionList(options);
    }
    
    // Recursive method to sort SelectOptions by label alphabetically
    public static List<SelectOption> SortOptionList(List<SelectOption> ListToSort) {
        // Nothing to sort if null or length 1
        if(ListToSort == null || ListToSort.size() <= 1)
            return ListToSort;
        
        // Create lists to hold greater and less values based on pivot
        List<SelectOption> Less = new List<SelectOption>();
        List<SelectOption> Greater = new List<SelectOption>();
        
        // save the pivot and remove it from the list
        integer pivot = ListToSort.size() / 2;
        SelectOption pivotValue = ListToSort[pivot];
        ListToSort.remove(pivot);
        
        // Loop through pivot-less list and add to corresponding list
        for(SelectOption x : ListToSort) {
            if(x.getLabel() <= pivotValue.getLabel())
                Less.add(x);
            else if(x.getLabel() > pivotValue.getLabel()) Greater.add(x);   
        }
        
        // Call method on Less and Greater lists and add pivot value in the middle
        List<SelectOption> returnList = new List<SelectOption> ();
        returnList.addAll(SortOptionList(Less));
        returnList.add(pivotValue);
        returnList.addAll(SortOptionList(Greater));
        return returnList; 
    }
}

@isTest
public class FileSearchControllerTest {
    
    // Create Attachment and File and search for them with Start and End Date
    public static testMethod void testSearch() {
        Account testAccount = new Account(Name='TestAccount');
        insert testAccount;
        Attachment testAttachment = new Attachment(
            Name='test12345',
            Description='testDescription',
            ParentId=testAccount.Id);
        Blob bodyBlob = Blob.valueOf('Unit Test Attachment Body');
        testAttachment.body=bodyBlob;
        insert testAttachment;
        
        ContentVersion testContent = new ContentVersion(
            Title='test12345',
            Description='testDescription',
            ContentLocation = 'S',
            PathOnClient = 'test12345',
            VersionData = bodyBlob);
        insert testContent;
        
        ContentDocumentLink testLink = new ContentDocumentLink(
            ContentDocumentId = [SELECT Id, ContentDocumentId FROM ContentVersion WHERE Id =:testContent.Id].ContentDocumentId,
            LinkedEntityId = testAccount.Id,
            ShareType = 'V');
        insert testLink;
        
        Test.startTest();
        PageReference pageRef = Page.FileSearch;
        Test.setCurrentPage(pageRef);
        
        FileSearchController fsc = new FileSearchController();
        fsc.name = 'test12345';
        fsc.searchObjects = new List<String>{'Account'};
        fsc.startDate = System.today();
        fsc.endDate = System.today();
        
        fsc.searchFiles();
        Test.stopTest();
        
        System.assertEquals('test12345', fsc.sObjWrapList[0].title);
        System.assertEquals('test12345', fsc.sObjWrapList[1].title);
    }
    
    // Create attachment, search for it, and then clear results
    public static testMethod void testClear() {
        Account testAccount = new Account(Name='TestAccount');
        insert testAccount;
        Attachment testAttachment = new Attachment(
            Name='test12345',
            Description='testDescription',
            ParentId=testAccount.Id);
        Blob bodyBlob = Blob.valueOf('Unit Test Attachment Body');
        testAttachment.body=bodyBlob;
        insert testAttachment;
        
        Test.startTest();
        PageReference pageRef = Page.FileSearch;
        Test.setCurrentPage(pageRef);
        
        FileSearchController fsc = new FileSearchController();
        fsc.name = 'test12345';
        fsc.searchObjects = new List<String>{'Account'};
        fsc.startDate = System.today();
        fsc.endDate = System.today().addDays(1);
        
        fsc.searchFiles();
        fsc.clearFiles();
        Test.stopTest();
        
        System.assertEquals(0, fsc.sObjWrapList.size());
    }
    
    // Create attachment and search for it with only Start Date
    public static testMethod void testStartDate() {
        Account testAccount = new Account(Name='TestAccount');
        insert testAccount;
        Attachment testAttachment = new Attachment(
            Name='test12345',
            Description='testDescription',
            ParentId=testAccount.Id);
        Blob bodyBlob = Blob.valueOf('Unit Test Attachment Body');
        testAttachment.body=bodyBlob;
        insert testAttachment;
        
        Test.startTest();
        PageReference pageRef = Page.FileSearch;
        Test.setCurrentPage(pageRef);
        
        FileSearchController fsc = new FileSearchController();
        fsc.getObjects();
        fsc.name = 'test12345';
        fsc.searchObjects = new List<String>{'Account'};
        fsc.startDate = System.today();
        
        fsc.searchFiles();
        Test.stopTest();
        
        System.assertEquals('test12345', fsc.sObjWrapList[0].title);
    }
    
    // Create attachment and search for it with only End Date
    public static testMethod void testEndDate() {
        Account testAccount = new Account(Name='TestAccount');
        insert testAccount;
        Attachment testAttachment = new Attachment(
            Name='test12345',
            Description='testDescription',
            ParentId=testAccount.Id);
        Blob bodyBlob = Blob.valueOf('Unit Test Attachment Body');
        testAttachment.body=bodyBlob;
        insert testAttachment;
        
        Test.startTest();
        PageReference pageRef = Page.FileSearch;
        Test.setCurrentPage(pageRef);
        
        FileSearchController fsc = new FileSearchController();
        fsc.name = 'test12345';
        fsc.searchObjects = new List<String>{'Account'};
        fsc.endDate = System.today();
        
        fsc.searchFiles();
        Test.stopTest();
        
        System.assertEquals('test12345', fsc.sObjWrapList[0].title);
    }
}