Value list conundrum
Not sure why I can't seem to wrap my head around this. I have three tables: Contacts, Dispatch and RunDetails.
The dispatch table calls upon the contact table for obvious reasons while the RunDetails contains driver information, status and other things. The reason why I had to create a separate RunDetails table was because sometimes Dispatch jobs need to share the same status, driver etc etc. In order to link the multiple records together you simply copy paste the RunDetailsID into the dispatch match field and you now have shared information. When the status is changed in one record they obviously all get changed.
What I'm looking to do is create a value list that only shows ContactIDs for dispatch records that have a status set to complete. Remember that the status is stored in the third table which is RunDetails. Seems like this should be easy but I can't wrap my head around this, I've tried several serried relationships and can't ever nail it.