View using related table, lookup column value not showing

[Rosie]

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

[Mira_Ghaly]

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.

[dpoggemann]

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. 

[Rosie]

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.

[Mira_Ghaly · Accepted solution]

@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.

[dpoggemann · Accepted solution]

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...

[Rosie]

@Mira_Ghaly @dpoggemann Thanks to both of you for your help.

I have learnt a lot today from you that will be very helpful.