How do I reference a value in a Parent table ...

[Former Community Member]

How do I reference a value in a Parent table for a Select performed in the Child table?

I have looked at about a dozen posts and not seeing the answer I need.

This seems straight forward and I think probably fairly common but I am not getting

it yet.

I have a Parent Table - CustomerOrders, a child table - OrderedItems, and a CustomPrice table to select Price info from.

When a user enters Customer ID and the Product Code, I want to retrieve the Price.

In the CHILD.Price field, the following test SELECT() expression works where the Customer ID and Product Code are hard coded into the expression:

=Any(SELECT(CustomPrice[Custom Price], AND([Customer ID] = ‘vST0SIan’, [Product Code] = ‘100107’, [Active?] = “Y”)))

I’ve tried many variations and can’t seem to get it right.

How do I change the syntax so that the hard coded values reference the user input such that Customer ID is from the - CustomerOrders and the Product Code is from the - OrderedItems, equivalent to the below expression??

=Any(SELECT(CustomPrice[Custom Price], AND([Customer ID] = .[Customer ID], [Product Code] = .[Product Code], [Active?] = “Y”)))

Thanks!

[Aleksi_Alkio]

How about… Any(SELECT(CustomPrice[Custom Price], AND([Customer ID] = [_THISROW].[Customer ID],[Product Code] = [_THISROW].[Product Code], [Active?] = “Y”)))

[Former Community Member]

@Aleksi_Alkio I had tried that previously.

I guess I should have mentioned that.

When I try the formula as you have suggested I get this error :

"Error in expression ‘[Order Item ID].[Customer ID]’ : Unable to find column ‘Customer ID’ "

It seems it is trying to read the Customer ID value from the OrderedItems (CHILD) table.

I will also mention that I used a LOOKUP() expression beforehand and it was able to retrieve the Customer ID from the PARENT table even while operating at the CHILD level.