Retrieve Dataverse lookup column and non lookup column values

[KMP_Uniserve]

Hi guys,

Here's my scenario.

We have a form with a lookup field which connects to a Dataverse table that has 3 columns, I need to figure out a way to obtain values from all 3 columns (not just the lookup column). The objective is to allow the user to select a shipment reference from the lookup (which is populated onto the lookup box labelled Target Job), and for 2 other values on the same row to be obtained and auto populated, onto two read only fields; labelled Consignee and Consignor (these can be text boxes or dropdowns). The below image shows the 3 columns and the values which we require from each row.

The first column is not a problem as this is the lookup (primary column), in our use case this column gives us a shipment reference number.

The challenge is with the next step, we need to also grab the consignee and consignor values which are in the 2nd and 3rd column respectively. As far as I am aware, the lookup will only point to the primary column, and there is no way to obtain values from any other column.

I have tried multiple ways with Jquery, but I have not found a way to achieve this, I am only able to duplicate the lookup column shipment reference number onto the consignee and consignor text boxes (as shown on the 2nd image below), there does not seem to be a way to obtain the consignee and consignor values associated to the lookup column shipment reference, and auto populate this to the consignee and consignor text boxes.

Is there a way I can use Jquery to achieve this? If this is not possible with Jquery, how would I accomplish this using FetchXml and liquid code?

I am relatively new to Power Pages, Jquery, FetchXml and Liquid code. I did attempt to use FetchXml combined with Liquid code, but I couldn't figure out the correct syntax for what I am trying to achieve.

The Jquery syntax which I am currently using (only duplicates shipment reference from lookup column) is listed below (part of the Dymanics 365 environment name has been replaced with 'xxxx':

$(document).ready(function(){

$('#xxxx_target').on('change', function() {

$('#xxxx_consigneeauto').val($('#xxxx_target_name').val());

$('#xxxx_consignor').val($('#xxxx_target_name').val());

})

});

Target is the lookup search element name, consigneeauto is the text field intended to be used to autopopulate the consignee value, consignor is the text field intended to be used to autopopulate the consignor value.

Any advice would be greatly appreciated, apologies for making this post long; I wanted to make the scenario clear.

Thank you.

[Atmaram90]

You can join both the tables in fetchxml and select columns from both the tables. Reference: https://learn.microsoft.com/en-us/power-apps/developer/data-platform/fetchxml/join-tables?tabs=fetch... If you think to use power page webapi you could use $expand (https://learn.microsoft.com/en-us/power-pages/configure/read-operations#retrieve-related-table-recor... ) If you try to show both the columns in list that can be done by adding fields(consignee , consignor) in the dataverse view which is out of the box feature. Reference: https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Dataverse-View-how-to-add-column-for-related...

[Fubar · Accepted solution]

If the user only needs to see the values (after they have selected the Lookup value) you can use a Quick View Form (defined on the Lookup Table, and added to your main dataverse form).

If the values need to be saved, if using the quick view then save them using a realtime Workflow on change of the Lookup. Alternatively in JavaScript/JQuery on change of the Lookup field call the Power Pages Web API to query the the Lookup table and then populate the values with the returned results (for this option there will be a short delay as the ajax call will take a second or two).

https://learn.microsoft.com/en-us/power-apps/maker/model-driven-apps/create-edit-quick-view-forms

if using the power pages webapi there are additional settings that are also required etc https://learn.microsoft.com/en-us/power-pages/configure/web-api-overview

[KMP_Uniserve]

Thank you both for your advice! 😉

I decided to go with the Jquery Web API approach and this has resolved the issue.