Managing active/inactive records in a pop-up menu
I'm having a heck of a time with this in multiple databases, and obviously missing something! Here's a simple example -
There's a table that lists all the equipment in a plant, with an active/inactive field. There's another table with a pop-up menu where the user selects an item from the equipment list, and there's a join table in-between. If an equipment record is inactive, then I do not want it to show up in the list.
Right now I create a calculation field in the equipment table that only shows equipment that is active, and then reference that field for the pop-up list, but if that equipment is switched to inactive I lose the reference, and then I add all kinds of scripting to prevent or fix.
Am I missing something? Please help! Thanks!