Dependent dropdown on ref

[Chacal33]

Hey all,

I cannot get my head around this problem of mine.

I have 4 tables:

T_Supplier--- SupplierID / Supplier
T_Product --- ProductID / Product / SupplierID
T_Order --- OrderID / OrderNum / OrderDate / SupplierID
T_OrderDetail --- OrderDetailID / OrderID / ProductID (based on SupplierID) / Qty

As you would have guessed, I am sure, the problem lies with ProductID, which should be based on the chosen supplier for the order.
I cannot make it work...

I have tried a couple of ways:

1) ProductID is a REF and DataValidity: SELECT(PRODUCT[ProductID], [ProductID].[SupplierID] = [OrderID].[SupplierID])
But the Product dropdown does not show in the form

2) ProductID is a REF and DataValidity: FILTER("PRODUCT", [SupplierID] = [OrderID].[SupplierID])
But the Product dropdown does not show in the form

3 and 4: same formula but with ProductID as ENUM and SupplierID in T_OrderDetail

Thanks for your ideas!

Matt

[Steve · Accepted solution]

As the Valid If expression for the ProductID column of the T_OrderDetail table:

[OrderID].[SupplierID].[Related T_Products]

[SkrOYC]

You need to add [_THISROW] when taking values from the current row you are adding