View using related table, lookup column value not showing

(0) Share

Report

Posted on by Rosie

295

Hello,

I have a Customer table, this is used as a lookup column in a Orders table.

Orders is related to the Order Details table, I have a view in Order Details and I want to add in the lookup column Customer from the related table Order. The values in the Customer column do not show in the view. If I select another column (that is not a lookup column) from the related table Orders then the values show in the view.

Is it not possible to show values from a lookup column in a related table?

Thanks

Rosie

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Mira Ghaly 11,413 Moderator on at

Like (1)

Report

Hi @Rosie

To be able to show columns from a related table what is only support is one level and the customer is a lookup on the order but not on the order details.

This means you need to add the customer to the order details table to be able to see the customer related fields on the order details column.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @Rosie ,

@Mira_Ghaly recommended the exact approach i have used many times. Not exactly what you think you need to do from a traditional "SQL" perspective but works well in Dataverse. The key thing to remember is that if the Order changes the Customer, this will not force the Order Detail to change the customer. This could cause consistency issues in your data if you allow the customer to be changed on your order.

Was this reply helpful? Yes No
Rosie 295 on at

Like (0)

Report

Thanks for your help.
I don't seem to understand how to make this simple thing work.
The Order is created (1 record), this is when the Customer is selected.
Then the order details are created (can be multiple order details per order), this also need to show who the customer is in a view.
I have already the Order number as a lookup column which works fine.
When I add another lookup column I get the order number again as the lookup only allows you to select the table, not the column in the table.

Was this reply helpful? Yes No
Verified answer

Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@Rosie

You need to add another lookup on the order details to the customer and it should be read-only to avoid data inconsistency as mentioned by @dpoggemann

And you need using a power automate flow or a synchronous workflow to set the customer on the order details from the parent order ,on create and on change of the customer field on the order.

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report

Hi @Rosie ,

When you create the Order Detail record from the order, setup the Mappings to copy the Customer from the order table down to the Order Details table. (https://www.linkedin.com/pulse/relationships-types-behaviors-dataverse-kumar-manish-csm-pmp-/)

Once you do this, you now have the Customer lookup on your Order Detail table so you will be able to select the other fields like you did from the Order table on the Order Details...

Was this reply helpful? Yes No
Rosie 295 on at

Like (0)

Report

@Mira_Ghaly @dpoggemann Thanks to both of you for your help.
I have learnt a lot today from you that will be very helpful.

Was this reply helpful? Yes No