Answered

Auto populate a column from another lookup column

(0) Share

Report

Posted on by ritetrakeng

Brief overview: We have been using a Task Manager database solution for many years I built using an Access Front End and split Backend. Last year I migrated the back end to use Dataverse whilst still using the Access front end. Everything works fine. Now I am in the process of designing a Model Driven App to replace the Access front end. This is where I have come stuck in a few areas. Despite weeks and hours of research I have not been able to get anywhere so I am coming here to see if anyone can assist me.
Scenario: I have an Equipment Hire Table that holds the equipment description and the rate per day e.g. 3 tonne dumper truck at £100 per day.
In my Hire quotation form/table the equipment column is a lookup to the Hire table. So, I click the looking glass and select the required equipment. Here is the issue I cannot resolve. How can I automatically populate the Hire Rate in the quotation form/table with the one from the Hire table? I hope that makes sense.
If anyone can point me in the right direction I would be so grateful.
Kind regards.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

MichaelFP 1,847 Super User 2025 Season 2 on at

Like (2)

Report
You can do it in that way:

On hire quotation form add a on change event of the Field Hire. With javascript you can get the value using formContext.getValue("attributename")[0].Id.

With this information you can use Xrm.WebApi.RetriveMultipeRecords to get Equipament Hire Tables based on the Guid of the equipement that you got with the getValue() method above.

will be something like this:

function onEquipamentChange(executionContext) { var formContext=executionContext.getFormContext(); var equipamentId=formContext.getAttribute("attributename).getValue()[0].Id; Xrm.WebApi.retrieveMultipleRecords("logical name of hire equipamente table", "?$filter=_equipmanetlookup_value eq '" + equipamentId + "'&$select=fieldTaxRate").then((result)=> { if(result.entities.length > 0) { var taxRateValue=result.entities[0].fieldTaxRate; formContext.getAttribute("HireQuotationTaxRateField").setValue(taxRateValue); } }) }

If my answer helped you, please give me a thumbs up (👍). If solve your question please mark as solution ✔️. This is help the community.

Was this reply helpful? Yes No
ritetrakeng 14 on at

Like (0)

Report

Thank you very much for the reply and proposed solution. I am not an expert in Javascript so will need to read up how I can put this into my form as its asking me to "Add a Library" when I click on the Events tab.
I will let you know how I get on.

Was this reply helpful? Yes No
ritetrakeng 14 on at

Like (0)

Report

I have managed to upload and place an "on change" event using the javascript provided. I get a script error so its more than likely I have entered incorrect information into the code.
I will just extract the relevant table information below to see if I have understood the script and placed the correct values where they should be.

Table - Hire Equipment (Logical Name - pre_hireequipment)
Table Guid - pre_hireequipment_ID
Equipment Name - pre_name (Description of the Hire equipment e.g. 3 tomme dumper truck)
Hire Rate - pre-rate (Hire rate of equipment per day)

Table - Hire Quote
Table GUID - pre_hirequite_ID
Equipment - pre_equipment (Lookup column to the equipment name in the Hire table)
Qty - pre_qty (How many days hire required)
Unit Cost - pre_unitcost (Cost per day - to be populated by script from Hire table)

function onEquipamentChange(executionContext) {
var formContext=executionContext.getFormContext();
var equipamentId=formContext.getAttribute("pre_hireequipment_ID").getValue()[0].Id;

Xrm.WebApi.retrieveMultipleRecords("pre_hireequipment", "?$filter=pre_name eq '" + equipamentId + "'&$select=pre_rate").then((result)=> {
if(result.entities.length > 0) {
var taxRateValue=result.entities[0].fieldTaxRate;
formContext.getAttribute("pre_unitcost").setValue(taxRateValue);
}
})
}

Please advise where I have gone wrong - many thanks.

Was this reply helpful? Yes No
Verified answer

ritetrakeng 14 on at

Like (0)

Report
After much research/understanding I have managed to resolve the minor issues and its now working.
function onEquipmentChange(executionContext) { var formContext=executionContext.getFormContext(); //get the form Context var equipmentId=formContext.getAttribute("pre_equipment").getValue()[0].id; //get GUID of the equipment selected Xrm.WebApi.retrieveMultipleRecords("pre_hireequipment", "?$select=pre_rate&$filter=pre_hireequipment_ID eq '" + equipmentId + "'").then((result)=>{ if(result.entities.length > 0) { var HireRateValue=result.entities[0].pre_rate; formContext.getAttribute("pre_unitcost").setValue(HireRateValue); } }) }
Thank you for your assistance.

Was this reply helpful? Yes No