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!
How about… Any(SELECT(CustomPrice[Custom Price], AND([Customer ID] = [_THISROW].[Customer ID],[Product Code] = [_THISROW].[Product Code], [Active?] = “Y”)))
@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.
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |