I suggest a portal and using a join table to link judges to Cases.
Your relations would look like this:
Cases::CaseNo = JudgeAssignments::CaseNo
JudgeAssignments::JudgeID = Judges::JudgeID.
To assign a judge to a case, you'd create a record in JudgeAssignments with matching case and JudgeID numbers. (This can easily and automatically be done with a portal.)
In the Cases to JudgeAssignments relationship, enable the "Allow creation of records via this..." option for JudgeAssignment records. Define a third field in JudgeAssignments, AssignID and define it as an auto-entered serial number. (You can add other fields as needed to document details on each Judge assignment.)
Put a portal on a Cases based layout that specifies a Descending sort order based on AssignID. (You can include fields such as a name field from the judges table in this portal as needed.)
The most recently assigned judge will appear in the top row with previously assigned judges listed in order in the rows below.
Thanks. That definitely worked in order to track the substitutions on each case. I'd still like to find some way of also tracking how often a judge has been substituted within a given period of time and I think this arrangement will only be able to tell me how many times a judge has been appointed. I'm looking into setting up a multiple predicate relationship between the original join table and a second table occurrence of the same join table. I think that way I can do a lookup from the prior record in order to pull the judge's name and place it a "replaced judge" field (or some such name).
I really appreciate the prompt reply.
If you add a date field to your Join table, you should be able to analyze the frequency of Judge substitutions. You could also add a field that flags a Join table record as either an initial assignment or a substitution. Another field could record the reason given for the substitution, if that should be relevant.
With data in such fields, you could pull up groups of records that represent:
Total substitutions for a specific court case.
Total subsitutions for a specific Judge
Both of the above options but over a given date interval.
Once you've pulled up your group of Join Table records, the relationships you already have in place can get you case and judge information via the Case and Judge Id numbers.
Thanks, Phil. That makes sense to me. I think I was overcomplicating things in my head.
Okay, one last wrinkle and I'll stop bugging you about this. I have a "case summary" layout that gives me a stripped down, snapshot view of each case. I want the case summary to include a field that tells me who the current judge is, and that will automatically update whenever a new substitution record is entered in the join table. I've tried a straight lookup, a calculation using a lookup and even a short script using a "relookup" step, but I always end up with either nothing in the field or the name of the initial judge that appointed to the case.
Got a quick solution to this issue?
Thanks again for your prompt and easy-to-understand responses.
You're not bugging me :smileywink:
This one's pretting simple to fix. Just place the field that's currently returning the original judge directly on your layout. Now we'll modify your relationship:
I think I suggested defining a serial number field in JudgeAssignment table that functions as a join table between cases and judges. If you haven't defined such a field create one. (You can add a serial number to this field for existing records by using the Replace Field Contents tool.)
Open File | Manage | Database | Relationships and double-click the line linking your case records to the case-judge join table. You'll see an option at the bottom for sorting your Join table records. Specify a sort order for sorting your records in descending order by the auto-entered serial number field. Now the most recently created Judge assignment data will appear on your case summary layout. Since you have portals that use this same table, the order of their records may now have been reversed. If this is not desirable, open the portal settings on each such portal and you can specify a different sort order for the portal.
Note: When you don't specify a sort order in a relationship and place a field from the related table on a layout without using a portal, the oldest matching record is displayed. Using a specified sort order for the relationship, as I've just described, enables you to control which of several related records will actually appear.
You're the man, Phil. Thanks a lot. I gotta say the support here on this forum is some of the best I've ever seen.