Lookup fields are not like a normal field because after creation, the lookup field creates a N:1 relationship 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..
Heres my code that is built on yours and i cannot get it to work :(
ReplyDeletefunction 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);
}
);
}
}
Have you inspected this code to find out where is the error?
Delete