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

The below snippet is for setCountry function. 

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.

Save and Publish the solution. Move to the application to test how it works.

Result:

In the above clip you could see the working of auto-population of lookup based on the other.

Keep Learning..

Tamilarasu Arunachalam

2 Comments

  1. Heres my code that is built on yours and i cannot get it to work :(

    function setCC(executionContext) {
    // get form context
    var formContext = executionContext.getFormContext();
    // get Client Company CC field
    var targetCC = formContext.getAttribute("w3g_clientcompany");
    //declare an empty array of object to set Client Company CC lookup field
    var CCName = [{}];
    // Null check on the Client Contact field
    var clientContactAttr = formContext.getAttribute("w3g_ClientContact");
    if (clientContactAttr && clientContactAttr.getValue() !== null) {
    // get the selected Client Contact id from the lookup
    var selectedContactId = formContext.getAttribute("w3g_clientcontact").getValue()[0].id;
    // retrieve records from the Client Contact table by filtering the id of the selected Contact
    Xrm.WebApi.retrieveMultipleRecords("contact", "?$filter=contactid eq " + selectedContactId + "").then(
    function success(result) {
    for (var i = 0; i < result.entities.length; i++) {
    var thisCompany = result.entities[i];
    //Client Company lookup
    CCName[0].id = thisCompany["_w3g_clientcompany_value"];
    CCName[0].name = thisCompany["_w3g_clientcompany_value@OData.Community.Display.V1.FormattedValue"];
    CCName[0].entityType = thisCompany["_w3g_clientcompany_value@Microsoft.Dynamics.CRM.lookuplogicalname"];
    // set the value to the state field
    targetCC.setValue(CCName);
    // setCountry(executionContext);
    targetCC.fireOnChange();
    }
    },
    function (error) {
    console.log(error.message);
    }
    );
    }
    }


    ReplyDelete
    Replies
    1. Have you inspected this code to find out where is the error?

      Delete
Previous Post Next Post