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 blog, we'll have a view on auto populating lookup based on the another using the web resource. I have created a real-time scenario that includes three lookup fields and they are related to one another.
Steps for auto-populating lookup
- Table Setup
- Web resource Setup
- JavaScript Code
- Add Web resource to Event Handler
- Result
Table Setup
I have four tables in Dataverse 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 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 city value
var selectedCityName=formContext.getAttribute("c99_city").getValue()[0].name;
// get state field
var targetState=formContext.getAttribute("c99_state");
var stateName=new Array();
//retreive all records from city table
Xrm.WebApi.retrieveMultipleRecords("c99_city").then(
function success(result){
for(var i=0;i<result.entities.length;i++){
var thisCity=result.entities[i];
var thisCityName=result.entities[i].c99_name;
// condition if a match for selected city and the city in iteration
if(selectedCityName!=null && selectedCityName==thisCityName){
stateName[0]=new Object();
stateName[0].id=thisCity["_c99_state_value"];
stateName[0].name=thisCity["[email protected]"];
stateName[0].entityType=thisCity["[email protected]"];
// set state value
targetState.setValue(stateName);
// trigger state field to set country field
targetState.fireOnChange();
}
}
},
function(error){
console.log(error.message)
}
);
}
The below snippet is for setCountry function.
function setCountry(executionContext){
// get form context
var formContext=executionContext.getFormContext();
// get state value
var selectedStateName=formContext.getAttribute("c99_state").getValue()[0].name;
// get country field
var targetCountry=formContext.getAttribute("c99_country");
var countryName=new Array();
//retreive all records from country table
Xrm.WebApi.retrieveMultipleRecords("c99_states").then(
function success(result){
for(var i=0;i<result.entities.length;i++){
var thisState=result.entities[i];
var thisStateName=result.entities[i].c99_name;
// condition if a match for selected state and the state in iteration
if(selectedStateName!=null && selectedStateName==thisStateName){
countryName[0]=new Object();
countryName[0].id=thisState["_c99_country_value"];
countryName[0].name=thisState["[email protected]"];
countryName[0].entityType=thisState["[email protected]"];
// set country value
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.
Save and Publish the solution. Move to the application to test how it works.
Result
Hurray... That's what we want to achieve.
Keep Learning.
Join the conversation! Your thoughts help the community grow.