6 Replies Latest reply on Aug 24, 2009 2:14 PM by Yizwitz

    Replacing data in a field and archiving the replaced data in FP10



      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. 

        • 1. Re: Replacing data in a field and archiving the replaced data in FP10

          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.

          • 2. Re: Replacing data in a field and archiving the replaced data in FP10

            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.



            • 3. Re: Replacing data in a field and archiving the replaced data in FP10

              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.

              • 4. Re: Replacing data in a field and archiving the replaced data in FP10

                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.

                • 5. Re: Replacing data in a field and archiving the replaced data in FP10

                  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.

                  • 6. Re: Replacing data in a field and archiving the replaced data in FP10
                       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.