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
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”)))