For Developers

June 22, 2017

Autocomplete Lookup Fields Inside Flows

John Pipkin

The Problem

Recently, I was asked by one of my co-workers if there was a way to implement a lookup field inside a visual flow. As some of you know, typically this can be handled by using a dynamic picklist on a drop-down box or radio button element. As I received more requirements, I found out that there could be hundreds of options to choose from in this lookup field. The picklist solution was turning out not to be a solution at all — but what else could be done?

The Solution

A while back I wrote an autocomplete product for Visualforce pages so I used this basic design to see if I could put an autocomplete inside visual flows. After some testing and optimizing, a workable solution was made.

First, let’s take a look how I would typically solve this issue with a picklist.

A picklist on a Visualforce page

It doesn’t look that bad with under 10 results, but imagine hundreds of results and the mess that would look like! No, thank you.

The Implementation

As the first step, I first put the visual flow inside a Visualforce page using <flow:interview> and added my Visualforce component that manipulates the flow UI.

<apex:page controller="AC_JSONCtrl" docType="html-5.0">
    <flow:interview name="Ac_Test"/>
    <c:AutocompleteForFlows></c:AutocompleteForFlows>
</apex:page>

Running a visual flow inside of a Visualforce page allows us to manipulate the DOM using jQuery.

Before we take a look at the Visualforce component, “AutocompleteForFlows”, let’s see how we configure the flow to support the autocomplete fields.

supporting autocomplete in Visualforce

The highlighted field, “Owner”, is the field that I would like to lookup active Salesforce user records. To configure the field, I simply appended “_jqlookup” to the end of the field Id (“Unique Name”). Here is the actual hack though…

variables that the autocomplete needs to query in the "Help Text"

Next, we put the variables that the autocomplete needs to query in the “Help Text” section of the field. The format is very similar to JSON, but not quite. The autocomplete needs at least 3 variables:

  1. object (Required) (name of object to query against)
  2. label (Required) (what field to search in)
  3. value (Required) (value of selected suggestion)
  4. detail (Optional) (whats field/s to display underneath label)
  5. where (Optional) (where clause for query)
  6. limit (Optional) (limit clause for query)

Each variable is separated by a semi-colon and each variable-value pair is separated by a colon. If we want to search user names where the user is active, it would look like this:

object:User;label:Name;detail:Username;value:id;where:isactive = true

Since we are storing the query parameters inside the field itself, you can have multiple autocomplete fields per screen with no issue.

Next on the list is the Visualforce Component:

<!--
** Created By: J. Pipkin (OpFocus, Inc)
** Created On: Jan 2017
** Description: Autocomplete support for Visual Flows
-->

<apex:component controller="AC_JSONCtrl" access="global">

    <apex:attribute name="addFieldMargin" type="Boolean" description="Add 10px margin-bottom for lookup field" default="false"/>

    <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
    <script src="https://code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
    <apex:stylesheet value="{!URLFOR($Resource.AutoComplete, '/css/jquery-ui-1.8.17.custom.css')}"/>
    <apex:stylesheet value="{!URLFOR($Resource.AutoComplete, '/css/basic.css')}"/>

    <style type="text/css">
        .ui-autocomplete-loading {
            background: white url('{!URLFOR($Resource.AutoComplete, '/img/AjaxLoad.gif')}') right center no-repeat;
        }
        .interviewFormQuestionHelp {
            visibility: hidden;
        }
        .fake-input {
            margin-bottom: 10px;
        }
    </style>

    <script>
		$ac = jQuery.noConflict();
		
		// loop through each input field with id ending with jqlookup and store parameters in object indexed by field id
		var queryMap = new Object();

		// visual flows add ".input" to the end of input fields by default
		$ac('[id$="jqlookup.input"]').each(function(){
			// init vars
			var $t = $ac(this);
			var $tid = $t.attr('id');
			searchTerm='';

			// object to hold query parameters for this field.
			var queryParams = new Object();
			
			// get the field's help text and parse the query parameters
			// for storing in object
			$ac(this).parent().children().each(function(){
				if($ac(this).attr('class') == 'interviewFormQuestionHelp'){
					var params = $ac(this).children().first().attr('href');
					params = params.replace('javascript:popupFlowHelp(%27InterviewHelp%27,%27','');
					params = params.replace('%27);','').trim();
					params = params.replace(/\\'/g,'\'');
					params = params.replace(/\\u003Cbr\\u003E/g,'\'');
					params = params.replace(/\&gt\;/g,'%3E');
					params = params.replace(/\&gt\;/g,'%3C');
					console.log(params);
					var lstParams = params.split(';');
					for(var x = 0; x < lstParams.length; x++){
						var kv = lstParams[x];
						var k = kv.split(':')[0];
						var v = kv.split(':')[1];
						queryParams[k] = v;
						
					}
					queryMap[$tid] = queryParams;
				}
			});

			$ac(function() {
				// query parameter object for this field
				var qp = queryMap[$tid];

				// create a dummy input field for autocomplete and hide the field
				// created by flow
				var stringinput = document.createElement('input');
				stringinput.type = 'text';
				stringinput.id = $tid+'_label';
				$t.parent().append(stringinput);

				// if field is already populated with the Id of the record, use id to find the 
				// label of the query parameters to display
				if($t.val()){
					AC_JSONCtrl.getPreviousLabel($t.val(), qp['label'],function(results,event){
						if(event.status){
							$ac(stringinput).val(results);
						}
						else{
							console.log(event.message);
						}
					});
				}
				if('{!addFieldMargin}' == 'true')
					$ac(stringinput).addClass('fake-input');
		        $t.css('display','none');
		        
		        // init jQuery autocomplete
		        $ac(stringinput).autocomplete({
		            source: function( request, response ) {
		            	// add spinner
		                $ac(stringinput).addClass('ui-autocomplete-loading');
		                
		                // call controller to perform query
		                AC_JSONCtrl.getRemotingJSON(
		                	$ac(stringinput).val(), qp['object'], 
		                	qp['label'], qp['detail'] || '', qp['where'] || '', qp['limit'] || '', qp['value'],
		                	function(results,event){
		                		if(event.status){
		                			// parse response into suggestions
		                			response(
		                				$ac.map( 
		                					results , 
		                					function( item ) {
					                            return {
					                                label: '<a>'+
					                                item.label+'<br />'+
					                                '<span style="font-size: 0.8em; font-style: italic;">'
					                                +item.detail+
					                                "</span></a>",
					                                value: item.label,
					                                id: item.value
					                            };
				                        	}
			                        	)
			                        );
		                		}
		                		else{
		                			// log exception to the console
		                			console.log(event.message);
		                		}
		                		// remove spinner
		                		$ac(stringinput).removeClass('ui-autocomplete-loading');
		                	}
		                );
		            },

		            search: function() {
		                //search only if String contains at least 2 characters
		                if (this.value.length >= 2)
		                {
		                    $ac(stringinput).autocomplete('option', 'delay', 100);
		                    return true;
		                }
		                return false;
		            },
		            focus: function() {
		                // prevent value inserted on focus
		                return false;
		            },
		            select: function(event, ui) {
		            	// FOR DEBUGGING
		                // console.log('selected: ' + ui.item.id);
		                
		                //store selected results in the original field created by flow (hidden now)
		                $t.val(ui.item.id);
		                return true;
		            }
		        }).data("ui-autocomplete")._renderItem = autoCompleteRender;
			});
		})
	
		//show results
		function autoCompleteRender(ul, item) {
		    return $ac("<li></li>").data("item.autocomplete", item).append(item.label).appendTo(ul); 
                }
    </script> 
</apex:component>

This component is the main driver of the autocomplete functionality for visual flows. It utilizes jQuery to manipulate the flow’s rendered HTML. Here exactly is what the component is doing:

  1. Finds all input elements in the current flow screen that end with “jqlookup” (like we did earlier inside the flow).
  2. We create a “fake” input field for each of the flow jqlookup fields. This “fake” field is going to be the field that the users actually type into. The field inside the flow is hidden. When a suggestion is selected from the autocomplete, the Id (“value” property) is stored in the flow field that is hidden.
  3. For each of jqlookup fields, we also find the “Help Text” it is associated with and parse out the query parameters and hide the help button.
  4. Each “Fake” input field calls jQuery’s autocomplete function and defines the data source as the controller’s remote action method.

For those of you who read my last blog about autocomplete for Visualforce pages, you may recognize this component.

For those interested, here is the component’s controller:

/* Class:       AC_JSONCtrl
 * Created On:  Mar 2016
 * Created by:  J. Pipkin (OpFocus, Inc)
 * Description: 
 */
 
global with sharing class AC_JSONCtrl 
{
	
 	public class DataWrapper{
 		@TestVisible
 		String label, detail, value;

 		public DataWrapper(String l, String d, String v){
 			label = l; //label of suggestion
 			detail = d; //detail of suggestion
 			value = v; //the value of what is stored in the input tag. Commonly the Id of the record
 			
 		}
 	}
 	/**
 	 * [getRemotingJSON 
 	 * 		returns list of DataWrapper records based on query results for autocomplete component
 	 * ]
 	 * @param  searchTerm [what to search for]
 	 * @param  objName    [name of object to query against]
 	 * @param  label      [what field to search in]
 	 * @param  urlDetail  [whats field/s to display underneath label]
 	 * @param  urlWhere   [where clause for query]
 	 * @param  urlLimit   [limit clause for query]
 	 * @param  value      [value of selected suggestion]
 	 * @return            [List of DataWrapper records of query results]
 	 */
 	@RemoteAction
 	global static Object getRemotingJSON(String searchTerm, String objName, String label, String urlDetail, String urlWhere, String urlLimit, String value){
 		List wrp = new List();

 		//get params from url
 		
 		searchTerm = String.escapeSingleQuotes(searchTerm);
 		objName = String.escapeSingleQuotes(objName);
 		List lstFields = new List();
 		label = String.escapeSingleQuotes(label);
 		lstFields.add(label);
 		if(urlWhere != null && urlWhere != ''){
 			urlWhere = String.escapeSingleQuotes(urlWhere);
	 		urlWhere = EncodingUtil.urlDecode(urlWhere,'utf-8').trim();
	 		if(urlWhere.startsWith('and')){
	 			urlWhere = urlWhere.substring(3);
	 		}
	 	}
	 	if(urlLimit == null || urlLimit == ''){
	 		urlLimit = '10';
	 	}
	 	urlLimit = String.escapeSingleQuotes(urlLimit);

 		//add comma-separated detail fieds to query and store in separate list for retrieve
 		List lstDetail = new List();
 		if(urlDetail!= null && urlDetail != ''){
 			urlDetail = String.escapeSingleQuotes(urlDetail);
 			lstDetail = urlDetail.split(',');
 			lstFields.addAll(lstDetail);
 		}
 		value = String.escapeSingleQuotes(value);
 		lstFields.add(value);

 		//start building query
 		String qry = 'Select ';
 		
 		List fieldQry = new List();
 		for(String s :lstFields){
 			fieldQry.add(s);
 		}
 		qry += String.join(fieldQry,',');
 		qry += ' from ' + objName + ' where ' + label + ' like \'%' + searchTerm + '%\'';
 		if(urlWhere != null && urlWhere != ''){
 			qry += ' and ' + urlWhere.unescapeEcmaScript();
 		}

 		qry+= ' Limit ' + urlLimit;
 		system.debug('====>> qry : ' + qry);

 		//loop through records to get data
 		for(SObject o :database.query(qry)){
 			String thisdetail ='';

 			//since multiple details fields can exists, loop through and store data in list
 			//then convert list to comma-sepatated string for display
 			if(urlDetail != null && urlDetail != ''){
	 			if(lstDetail.size() > 1){
	 				List dlist = new List();
	 				for(String d :lstDetail){
	 					// relationship field support
	 					if(d.contains('.')){
	 						dlist.add( (String) (o.getSObject(d.substringBefore('.')).get(d.substringAfter('.'))) );
	 					}
	 					else{
	 						dlist.add((String)o.get(d));
	 					}
	 				}
	 				thisdetail = String.join(dlist,', ');
	 			}
	 			else{
	 				String d = lstDetail[0];
	 				// relationship field support
	 				if(d.contains('.')){
	 					thisdetail = (String) (o.getSObject(d.substringBefore('.')).get(d.substringAfter('.')));
	 				}
	 				else{
	 					thisdetail = (String) o.get(d);
	 				}
	 			}
	 		}
	 		String retlabel, retval;
	 		// relationship field support
	 		if(label.contains('.')){
	 			retlabel = (String)o.getSObject(label.substringBefore('.')).get(label.substringAfter('.'));
	 		}
	 		else{
	 			retlabel = (String)o.get(label);
	 		}
	 		if(value.contains('.')){
	 			retval = (String)o.getSObject(value.substringBefore('.')).get(value.substringAfter('.'));
	 		}
	 		else{
	 			retval = (String) o.get(value);
	 		}
			wrp.add(new DataWrapper(retlabel, thisdetail, retval));
 			
 		}
 		return wrp;
 	}

 	/**
 	 * Find record based on Id from previous autocomplete selection
 	 * @param  jsId  [Id of the record]
 	 * @param  label [what to return to the component to represent record]
 	 * @return       [the label field value of the returned record]
 	 */
 	@RemoteAction
 	global static String getPreviousLabel(String jsId, String label){
 		// get the name of the object from the Id
 		Id objId = (Id)jsId;
 		String objName = objId.getSobjectType().getDescribe().getName();

 		//build query
 		String q = String.format(
 			'Select Id, {0} from {1} where Id = {2}',
 			new String[]{label,objName,'\''+objId+'\''}
		);
		
 		SObject[] so = database.query(q);
 		// if result is null or empty list, return null.
 		// otherwise return the result's label field.
 		if(so == null || so.isEmpty()){
 			return null;
 		}
 		return String.valueOf(so[0].get(label));
 	}

}

The controller builds the query from the parameters the Visualforce page got from the flow’s “Help Text” and return the results for the autocomplete function to use.

Good Luck!

I hope this post helps you the next time you need a lookup field on a visual flow. Leave any questions or comments you have below. In the meantime, you can always vote for this idea to allow standard lookup field support in flows.

Happy coding!

About the Author

John Pipkin

John is a Senior Developer at OpFocus. John specializes in Apex, Visualforce, integrations, jQuery, Visual Flows, and Lightning Processes, but particularly enjoys the power to customize pretty much anything on the Salesforce platform. When he is not working his magic on the Force.com platform, John enjoys creating musical magic, and plays the drums and guitar. Since he's a total rock star, John has 11+ Salesforce Certified designations. Go through this list if you want to feel immensely inadequate about your Salesforce skills... Administrator (201), Platform App Builder, Developer (401), Platform Developer I, Sales Cloud Consultant, Service Cloud Consultant, Community Cloud Consultant, Mobile Solution Architecture Designer, Integration Architecture Designer, Development Lifecycle & Deployment Designer, Identity & Access Management Designer, and System Architect certifications.

Related Posts