How Playworks used Flow to replace multiple Visualforce pages and 1000s of lines of Apex code

The use case comes from a pro bono project for Playworks. Playworks is the leading national nonprofit leveraging the power of play to transform children’s social and emotional health. They’re a great organization, and I encourage you to learn more about what they do and consider supporting their work.

The Old Architecture

Playworks has a custom object called NCES Data, which contains information about 115,000 schools and 15,000 districts in the United States. The data is released by the National Center for Education Statistics each year, which the Playworks team then loads into their Salesforce org.

As Playworks establishes their presence at a new school, reps use a wizard to create an account for the school, along with a child record that stores the school’s current demographic information. The demographic information is important for various grants and funding. Districts are also represented with account records. School accounts, district accounts, and their respective demographic information records are created from data residing in the NCES Data object.

Reps interact with a Visualforce page embedded in a custom tab called Create New School, or they click an Add School button on the partner organization related list. Partner Organization is a junction object that allows Playworks to attach multiple account records to opportunities, where appropriate. A separate Visualforce page with its own controller is launched from the Add School button. When this occurs, a partner organization record is created in addition to the school or district account record. 

The rest of the logic remains the same. The search functionality is handled by Apex classes, and an Apex trigger creates the demographic information. Whether invoked by the Create New School tab or the Add New School button, each Visualforce page finishes with a redirect handled by a third Visualforce page with its own controller.

That’s a lot of custom dev work, and a lot of tech debt.

• 9 Apex classes
• 8 Apex test classes
• 3 Visualforce pages
• 1 Apex trigger

The good news is the code was written by highly competent developers, and has held up for nearly a decade. The bad news is that even a minor update requires the code to be modified.

The New Architecture

Speaking in terms of business logic, there are 5 possible outcomes when a user searches the NCES Data for a school or district. 

1. NCES record is a school or district that already has an account record.
2. NCES record is a district that does not have an account record.
3. NCES record is a school that not have an existing account record. The school has an NCES record for the district, but there is no account record for the district.
4. NCES record is a school that not have an existing account record. There is no NCES record for the district. (This can happen with some private schools.)
5. NCES record is a school that not have an existing account record. There is an account record for the district.

Each of these outcomes has slightly different requirements, depending on if it was invoked by the Add New School button or the Create New School tab.

Regardless of the eventual path for each of the 5 possible outcomes, all flows begin the same way.

First, since there are multiple account record types, the school record type is retrieved. Next, a flow screen gathers the necessary information to perform the search.

After failed attempts to recreate the Apex search functionality using a Get Records element with filter logic, a decision is made to go with code. A small portion of the existing code base is refactored into an invocable method. The method accepts the search parameters from the previous screen, and returns a list of NCES records.

public class CreateNewSchoolOrDistrictSearch {
    
    private static String maxresults = '25';
    
    @InvocableMethod(label='Search for NCES Data')
    public static List<Results> searchForNCESData(List<Requests> requests){
    
        Requests request = Requests[0];
        
        String queryString = generateNCESDataQuery(request.schoolNCESId, request.schoolName, request.schoolCity, request.schoolState, request.schoolZip);
        
        Results result = new Results();
        result.ncesRecords = Database.query(queryString);
        System.debug('NCES Records: ' + result.ncesRecords);
        List<Results> results = new List<Results>();
        results.add(result);

        return results;
    }
    
    public static String generateNCESDataQuery(String schoolNCESId, String schoolName, String schoolCity, String schoolState, String schoolZip){
        return generateNCESDataQuery(schoolNCESId, schoolName, schoolCity, schoolState, schoolZip, false, false);
    }
    
    public static String generateNCESDataQuery(String schoolNCESId, String schoolName, String schoolCity, String schoolState, String schoolZip, Boolean exactIdMatch, Boolean isDistrict){
        
        String ncesDataQuery = 'SELECT Id, Existing_Organization_in_Salesforce__c, School_Level__c, NCES_Id__c, School_Name__c, School_Mailing_Street__c, School_Mailing_City__c, School_Mailing_State__c, School_Mailing_Zip__c, School_Physical_Street__c, School_Physical_City__c, School_Physical_State__c, School_Physical_Zip__c, NCES_District_ID__c, District_Name__c, District_Mailing_Street__c, District_Mailing_City__c, District_Mailing_State__c, District_Mailing_Zip__c FROM NCES_Data__c '; 
        
        if(!isDistrict){
            ncesDataQuery += ' WHERE NCES_ID__c != NULL AND NCES_ID__c != \'0\' '; 
        }else{
            ncesDataQuery += ' WHERE NCES_ID__c = NULL AND NCES_District_ID__c != NULL AND NCES_District_ID__c != \'0\' ';             
        }
        
        if(!String.isBlank(schoolNCESId)){
            if(!isDistrict){
                if(exactIdMatch){ 
                    ncesDataQuery += ' AND (NCES_ID__c = \'' + schoolNCESId + '\') ';                
                }else{
                    ncesDataQuery += ' AND (NCES_ID__c = \'' + schoolNCESId + '\' OR NCES_ID__c LIKE \'%' + schoolNCESId + '%\') ';
                }            
            }else{
                if(exactIdMatch){
                    ncesDataQuery += ' AND (NCES_District_ID__c = \'' + schoolNCESId + '\') ';                
                }else{
                    ncesDataQuery += ' AND (NCES_District_ID__c = \'' + schoolNCESId + '\' OR NCES_District_ID__c LIKE \'%' + schoolNCESId + '%\') ';
                }
            }
        } else {
            if(!String.isBlank(schoolName)){
                ncesDataQuery += ' AND (School_Name__c LIKE \'%' + encodeForQuery(schoolName) + '%\')';                
            }
            if(!String.isBlank(schoolCity)){
                ncesDataQuery += ' AND (School_Mailing_City__c LIKE \'%' + encodeForQuery(schoolCity) + '%\')'; 
            }
            if(!String.isBlank(schoolState)){
                ncesDataQuery += ' AND (School_Mailing_State__c LIKE \'%' + encodeForQuery(schoolState) + '%\')'; 
            }
            if(!String.isBlank(schoolZip)){
                ncesDataQuery += ' AND (School_Mailing_Zip__c LIKE \'%' + encodeForQuery(schoolZip) + '%\')'; 
            }
        }
        
        ncesDataQuery += ' ORDER BY Name LIMIT ' + maxresults;
               
        return ncesDataQuery;           
    }
    
    private static String encodeForQuery (String inputVal) {
        return String.escapeSingleQuotes(inputVal.trim());
    }
    
    public class Requests{
        
        @invocableVariable(label='NCES Id')
        public String schoolNCESId;
        
        @invocableVariable(label='School Name')
        public String schoolName;
        
        @invocableVariable(label='School City')
        public String schoolCity;
        
        @invocableVariable(label = 'School State')
        public String schoolState;
            
        @invocableVariable(label='School Zip Code')
        public String schoolZip;
    }  
        
    public class Results{
        
        @invocableVariable(label = 'NCES Records')
        public List<NCES_Data__c> ncesRecords;
    }  
}

Next, the search results are displayed in a table, and a school or district is selected. Remember the results are a list of NCES data records returned by the invocable Apex. The selected NCES data record is used to create the necessary school account, district account, and corresponding demographic information record. If the school account or district account record already exists, the user is redirected.

Recall the 5 possibilities. 

1. NCES record is a school or district that already has an account record.
2. NCES record is a district that does not have an account record.
3. NCES record is a school that not have an existing account record. The school has an NCES record for the district, but there is no account record for the district.
4. NCES record is a school that not have an existing account record. There is no NCES record for the district. (This can happen with some private schools.)
5. NCES record is a school that not have an existing account record. There is an account record for the district.

Each one of these is represented by a path in the flow. For example, if a school or district already exists as an account record, the user is redirected to that record. Before that happens, if the flow is launched from the Add School button, a partner organization is created. If the flow is launched from the Create New School tab, then it simply redirects to the school or district.

Each of the 5 possibilities is accounted for, in different branches of the flow. The programmatic complexity of the Visualforce and Apex are replaced by the flow. While complicated in its own right, the solution is now declarative.

Conclusion

While we fell short of a completely declarative solution, the logic of the search functionality is unlikely to change. By refactoring the Visualforce pages and 95% of the Apex logic into flow, the Playworks team can safely make changes in the future, without having to hire a developer.