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 |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |