Replacing data in a field and archiving the replaced data in FP10
I am currently working on a solution to track the progress of administrative hearing cases. The rules that guide the hearings allow parties to request a substitution of the judge, so long as the substitution is made within a certain timeframe. Susbstitutions are made from a fixed pool of judges. In my solution, I have one table for case information and another for the pool of judges. The case table will, for obvious reasons, have a field that will list the name of the current judge appointed to the case and a relationship between to the two tables has been created on this basis.
When considering how to track the substitutions, I know what I want the solution to do, but I'm just not certain how to get it done. It would appear to be a two-step process:
1) The new judge's name would replace the name of the original judge appointed to the case, then
2) The name of the original judge would be moved into a field I'll call "former judge" so that I can find out who has previously been appointed to the case.
This process could occur several times (I've seen it happen up to 5 or 6 times in a given case). So a given case would have one current judge and several "former" judges associated with it. In addition, I'd need to track the dates each of the "former" judges were replaced in the case.
I'm pretty certain that I will probably need to set up a join table between the Cases and Judges as it would appear to be a many-to-many relationship. I also strongly suspect I need a script to get this "field swapping" process done, but I'm new enough to FileMaker Pro 10 that I'm having a tough time getting my mind around the steps involved. I've set up a few test files thus far, but they have been real botch ups so far.
Any insights you could provide would be greatly appreciated.