Auto Populate lookup based on the other lookup field in Power Apps

4 min readMar 26, 2023

Auto Populate lookup based on the other lookup field in Power Apps

Lookup fields are not like a normal field because after creation, the lookup field creates a N:1 relationship between the entity that the lookup is created for and the entity that is being looked up to. The value of the lookup is the GUID of the record in destination table we are looked up to.

In this article we’ll have a view on auto populating lookup based on the another lookup using the JavaScript web resource. I have created a form which includes three lookup fields and they are related to one another.

Steps:

Table Setup
Web resource Setup
JavaScript Code
Add Web resource to Event Handler
Result

Table Setup :

I have four tables in Dataverse which are details, city, state and country. The details table contains three lookup fields named City, State and Country. City has N:1 relationship with State, which has N:1 relationship with Country. The below diagram represents the Table setup for the context. The scenario was if the city was selected, the state and country should be auto-populated. Below diagram represents the relationship between the table we use.

Web resource Setup:

Auto-population can be achieved through business rules too, but the filtering is not possible in Business Rules. So, we can make it through Web resources. For that you have to add a new web resource by clicking + New → more → Web resource. A quick create form opens for creating a web resource. Upload your Web resource file(if you didn’t started the coding part, upload a sample JS file from device and you can modify it any time), give a name, select type as JavaScript(JS) and Save it.

JavaScript Code :

The Web resource contains two functions setState and setCountry, one for auto-populating state and another for country. Both are triggered from field on-change events. setState is triggered on-change of city and setCountry is triggered on-change of state. The below snippet is the function of setState

function setState(executionContext) {
  // get form context
  var formContext = executionContext.getFormContext();
  // get state field
  var targetState = formContext.getAttribute("c99_state");
  //declare an empty array of object to set state lookup field
  var stateName = [{}];
  // Null check on the city field
  if (formContext.getAttribute("c99_city").getValue() !== null) {
    // get the selected city id from the lookup 
    var selectedCityId = formContext.getAttribute("c99_city").getValue()[0].id;
    // retrieve records from the city table by filtering the id of the selected city
    Xrm.WebApi.retrieveMultipleRecords("c99_city", "?$filter=c99_cityid eq " + selectedCityId + "").then(
      function success(result) {
        for (var i = 0; i < result.entities.length; i++) {
          var thisCity = result.entities[i];
          //state lookup
          stateName[0].id = thisCity["_c99_state_value"];
          stateName[0].name = thisCity["_c99_state_value@OData.Community.Display.V1.FormattedValue"];
          stateName[0].entityType = thisCity["_c99_state_value@Microsoft.Dynamics.CRM.lookuplogicalname"];
          // set the value to the state field 
          targetState.setValue(stateName);
          // setCountry(executionContext);
          targetState.fireOnChange();
        }
      },
      function (error) {
        console.log(error.message);
      }
    );
  }
}

The below snippet is for setCountry function.

function setCountry(executionContext) {
  var formContext = executionContext.getFormContext();
  // get country field
  var targetCountry = formContext.getAttribute("c99_country");
  //declare an empty array of object to set country lookup field
  var countryName = [{}];
  // Null check on the state field
  if (formContext.getAttribute("c99_state").getValue() !== null) {
    var selectedStateId = formContext.getAttribute("c99_state").getValue()[0].id;
    // retrieve records from the states table by filtering the id of the selected state
    Xrm.WebApi.retrieveMultipleRecords("c99_states", "?$filter=c99_statesid eq " + selectedStateId + "").then(
      function success(result) {
        for (var i = 0; i < result.entities.length; i++) {
          var thisState = result.entities[i];
          // country lookup
          countryName[0].id = thisState["_c99_country_value"];
          countryName[0].name = thisState["_c99_country_value@OData.Community.Display.V1.FormattedValue"];
          countryName[0].entityType = thisState["_c99_country_value@Microsoft.Dynamics.CRM.lookuplogicalname"];
          // set the value to the country field
          targetCountry.setValue(countryName);
        }
      },
      function (error) {
        console.log(error.message);
      }
    );
  }
}

targetState.fireOnChange();

The above line of code is a special function in Dataverse because which triggers the function when a field is changed automatically(from another trigger). Once our coding part gets completed add the Web resource file to dataverse.

Add Web resource to Event Handler:

Move to form designer in Power Apps and add the Web resource to the form library.

Move to the Tree View and select the field and navigate to Events tab in the right menu bar. Configure the event by clicking the + Event handler. Select event type, library and function name then click Done. You have to add event handler for city and state field in form.