Executive Summary

This blog post is for Power Apps developers as well as end-users that are looking for a wildcard-like solution for Lookup controls in Microsoft Power App and Dynamics. It begins by describing the problem that a client had with finding records where only part of the name was known. Next, it covers the workarounds that were offered to the client as well as the client’s response. Next, the solution is explained in detail, including the necessary code and instructions for setting up within the Power Apps solution. Finally, a video of the final product is provided demonstrating how the implementation behaves.

The Problem

In 2019, I worked with a client that wanted to migrate a CRM system that had been developed in FileMaker (an Apple Subsidiary). The client was an Apple / Mac user through and through; however, he did like Microsoft Office products. He had researched Power Apps and wanted to migrate his system to it and integrate it with Microsoft Outlook. After spending a few months migrating the system to Power Apps the client was pleased but was a little frustrated with some of the user interface elements. He did not like the way that the Lookup controls functioned and claimed that it was not finding all the records.

After meeting with the client, I figured out the problem. In the example below, we are using fictitious company names. Let us pretend that we are adding an Account for a Contact and are having trouble remembering the exact Account name. We remember that the word “Generic” is in the name, but cannot remember the rest.

If you start by typing “gen” into the Lookup control, notice that the lookup only returns items that begin with “gen”.

This scenario is what the client was upset about, but I had some workarounds for him.

The Workarounds

I explained to the client that if he entered a wildcard before typing the search term that it would return the results that he was expecting. I also informed him that he could configure and use Relevance Search to locate the name and then enter a full name into the Lookup control. The client found these workarounds to be frustrating and wished that it behaved like his FileMaker solution. He did not want to have to explain wildcards or relevance search to office staff and wanted the lookup to behave how he viewed to be correct.
If you start typing “*gen” into the Lookup control, it will return Accounts that contain “gen”, including “Advanced Generic Corporation”, the company we are looking for.

I put together a prototype using a Single Line of Text field with an Auto Complete control like below. When you enter “gen” into the Auto Complete control, it behaves like the client wanted, returning matches that started with or contained the term. The client also likes the fact that it highlighted the text that matched too. My initial prototype had the Lookup and the Autocomplete next to each other. The user could use the OOTB Lookup or use the Auto Complete control. Once a user was selected a record in the Auto Complete control, the Lookup would be resolved based on the record selected. Unfortunately, the client found this to be “clunky” and insisted that it work like user interfaces do on Apple products.

The Solution

I started working on a solution making these 2 controls work together to achieve the client’s expectations. The implementation would follow these rules:

1. On load:
   1. If the Lookup was a value, show it and hide the Auto Complete field (note: the lookup is what is important). Otherwise, show the Auto Complete and hide the Lookup.
   2. Populate a list of all entity values to be used by the Auto Complete.
2. When the Auto Complete changes, try to resolve it against a pre-populated list of Entity values. In this example, I use the Account name field, but it could be any entity.
   1. If there is a single match, set the Lookup field to that value, show the Lookup and hide the Auto Complete.
   2. If there is more than one match, alert the user and force them to use Lookup.
   3. If no match is found, do nothing.
3. When the Lookup changes, if an item has been selected, hide the Auto Complete field. If not, then hide the Lookup and show the Auto Complete (i.e. if the user clears the lookup).

The JavaScript

There are a variety of ways the scenario could be achieved. I prefer to establish a single event on the form and wire up events within my JavaScript. I also broke this code up for cleanliness and reusability. For this blog post, I have all the JavaScript in a single file; however, some methods could be moved to a shared.js file and reused across multiple forms. Here is a breakdown of the code:

• configureForm – a method called when the form is loaded. It sets up the on-change events for the fields and initializes the fields.
• onAccountAutoCompleteChange – event handler for the on-change event of the Auto Complete textbox. It simply calls onAutoCompleteChanged with the necessary parameters.
• onAccountLookupChange –calls the onLookupChanged with the necessary parameters and is used to initialize the fields.

These methods could be moved to a shared file if desired:

• populateAccountSet –populates the accountSet array. It calls populateSet with the necessary parameters.
• populateSet – populates an array with the desired entity field values. These values are used to resolve against when the user selects a value in the Auto Complete control.
• onAutoCompleteChanged – performs the logic established above for the Auto Complete field.
• onLookupChanged – performs the logic established above for the Lookup field.

var accountSet = new Array();
var formContext = null;
function configureForm(executionContext) {
    formContext = executionContext.getFormContext(); 
    populateAccountSet();
    formContext.data.entity.attributes.getByName('new_accounttext').addOnChange(onAccountAutoCompleteChange);
    formContext.data.entity.attributes.getByName('new_account').addOnChange(onAccountLookupChange);
    onAccountLookupChange();
}

function onAccountAutoCompleteChange() {
    onAutoCompleteChanged("new_accounttext", "new_account", "account", accountSet);
}

function populateAccountSet() {
    populateSet("account", "?$select=accountid,name", accountSet, "accountid", "name");
}

function populateSet(entity, query, set, idField, nameField) {
    if (set !== null && set.length > 0) return;
    Xrm.WebApi.retrieveMultipleRecords(entity, query).then(
        function success(result) {
            if (result.entities.length > 0) {
                for (var i = 0; i < result.entities.length; i++) {
                    set.push(
                        new Array(
                            result.entities[i][idField],
                            result.entities[i][nameField]
                        )
                    );
                }
            }
        },
        function (error) {
            Xrm.Utility.alertDialog(error.message, null);
        });
}

function onAutoCompleteChanged(autoCompleteField, lookupField, entityType, dataSet) {
    var value = formContext.data.entity.attributes.getByName(autoCompleteField).getValue();
    if (value !== null) {
        var valueLowerCase = value.toLowerCase();
        var matches = [];
        for (var i = 0; i < dataSet.length; i++) { if (dataSet[i][1] !== null && dataSet[i][1] !== undefined) { if (valueLowerCase === dataSet[i][1].toLowerCase()) { matches.push(dataSet[i]); } } } if (matches.length > 0) {
            if (matches.length > 1) {
                var friendlyEntityType = entityType.replace("new_", "");
                friendlyEntityType = friendlyEntityType.replace("_", " ");
                var alertStrings = {
                    text: "More than one record exists; please use default " + friendlyEntityType + " control."
                };
                Xrm.Utility.alertDialog(alertStrings);
                formContext.data.entity.attributes.getByName(autoCompleteField).setValue(null);
                formContext.data.entity.attributes.getByName(lookupField).setValue(null);
                formContext.getControl(autoCompleteField).setVisible(false);
                formContext.getControl(lookupField).setVisible(true);
            } else {
                var lookupVal = new Array();
                lookupVal[0] = new Object();
                lookupVal[0].id = matches[0][0];
                lookupVal[0].name = matches[0][1];
                lookupVal[0].entityType = entityType;
                formContext.data.entity.attributes.getByName(lookupField).setValue(lookupVal);
                onLookupChanged(autoCompleteField, lookupField);
            }
        }
    } else {
        formContext.getControl(lookupField).setVisible(true);
    }
}

function onLookupChanged(autoCompleteField, lookupField) {
    var lookupVal = formContext.data.entity.attributes.getByName(lookupField).getValue();
    if (lookupVal !== null && lookupVal !== undefined) {
        formContext.getControl(autoCompleteField).setVisible(false);
        formContext.getControl(lookupField).setVisible(true);
    } else {
        formContext.getControl(autoCompleteField).setVisible(true);
        formContext.getControl(lookupField).setVisible(false);
        formContext.data.entity.attributes.getByName(autoCompleteField).setValue(null);
    }
}

function onAccountLookupChange() {
    onLookupChanged("new_accounttext", "new_account");
}
 

Updating the PowerApps Solution

For this script to be used, you must first create a Web Resource for it. In this example, I created one called accountwildcard.

Next, you need to add an event handler function to the OnLoad event; be sure to check the box Pass execution context as the first parameter. Add the library to the form and tell it to call the configureForm method for the OnLoad event.

Your form properties should look like this then you are done.

You will also want to make sure the labels for both fields are the same to make the transition look clean.

The Final Product

As you can see below, the transition is smooth and seamless. When you type, the Auto Complete control serves up wild-carded results. After hitting tab or moving outside of the control, the Lookup is populated with the match and the Auto Complete is hidden. If you clear the Lookup, it hides itself and shows the Auto Complete again.

This is a specialized solution and I would recommend steering your client towards using a wild card in Lookup control; however, if you have a client that is insistent on this functionality, I hope this saves some time. The client I did this for is pleased with the results and has had no issues with it since.