best implementation of conditional value lists
Mac OS 10.6
I'm creating a solution for my father-in-law, who runs a military contracting business. He deals with all branches of the military, and his contacts have different rank naming conventions. I have created fields called "name_rank" and "branch". "Branch" is validated through a value list containing the values "Army, Air Force, Navy, Marines, Coast Guard." I have created value lists containing the rank name conventions for each branch: "Air Force Ranks" etc. I need the value list drop down menu for the "name_rank" field to change depending on the branch selected, because Army ranks are not the same as Air Force ranks, etc.
I thought of creating a script to go into Manage Database to actually change the value list, but that seemed like a less-elegant solution, and I wouldn't know exactly how to do that.
I also thought of just creating a new table for each of the rank lists and creating a traditional conditional value list based on those records (where each record would be a rank, and each record would have a branch_id). That also seems less elegant to me than I envision this could be.
Does anyone have any more elegant solutions, or should I just accomplish the goal in the way I described above?