Autocomplete Lookup Fields Inside Flows
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.
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.
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…
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:
- object (Required) (name of object to query against)
- label (Required) (what field to search in)
- value (Required) (value of selected suggestion)
- detail (Optional) (whats field/s to display underneath label)
- where (Optional) (where clause for query)
- 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(/>/g,'%3E');
params = params.replace(/>/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:
- Finds all input elements in the current flow screen that end with “jqlookup” (like we did earlier inside the flow).
- 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.
- 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.
- 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!
