Filter expression for ref list in data entry form
I am trying to filter the records displayed within a ref dropdown relative to an earlier entry in the same form. In summary my form is to log and assign actions required and the related categories to be selected in the following sequence:-
- Select a property (building) 2. Select a trade (e.g. carpenter, decorator, cleaner, etc) 3. Enter brief description of works required (simple long text field) 4. Assign the work to a company relative to the trade 5. Enter various statements about completion target date etc. all of which work fine.
Items 1-3 work fine but I am falling down when it comes to item 4. Basically I only want the dropdown for item 4 to show me the companies that have been assigned the corresponding trade selected under item 2 but have been unable to achieve this. My current tables and column names relevant to this are:-
(a) TRADES - contains 2 columns [TradesID] & [Trade] (b) CONTACTSTRADE - contains columns with [ContactTradesID], [TradeID],[Trade] & [TradeName] © ACTIONS (to which I am trying to add records) - contains columns [ActionsID], [PropertyID], [Property Name], [TradesID], [Trade], [ActionDescription], [ContactTradesID], [TradeName]
So, essentially I want the dropdown for item 4 to only show me the companies that can carryout the works for the trade selected as item 2. Would think something like [TradesID]=[ContactTradesID]. Problem is I cannot find a way of achieving this. Can anyone help to explain how to filter the list displayed and where to do this e.g. initial value filter, data slice or what?