4 Replies Latest reply on Oct 20, 2009 2:15 PM by philmodjunk

    Help with script for duplicating records

    user14360

      Title

      Help with script for duplicating records

      Post

      Hi.  I'd love it if someone could help me with how to get a set of found records to duplicate and then change the value of one of the fields for all of those new records.  (Or if I'm on the wrong track with the structure of this, then that'd be good to know too.)

       

      I have a database that tracks student involvement and attendance in ensembles at school.

       

      The tables I have are:

      Student Records (with a primary key for student ID)

      Ensemble (list of the various ensembles that the school offers)

      Ensemble Participation Data

       

      The Ensemble Participation Data table is related to both of those tables, through the Student ID to the Students Record table and through the ensemble name to the Ensemble table.  In this Ensemble Participation Data table, it has a field for the school term, so it tracks who is in the ensemble and in which term (so a student might have multiple records in this table, if they are in the ensemble for the whole year, so term 1, 2, 3 and 4).  It also has a repeating field called "Attendance", which allows the teacher to select "Present" or "Absent" for the 15 rehearsals in that term.

       

      There is a portal on the Ensemble layout that shows which students are in that ensemble, and I have a Global field for the "term filter" on this layout so that the teacher can select just one term to view, and not see the multiple records of students in the ensemble for the whole year.  This then allows the teacher also to mark the attendance for one term through a layout based on the Ensemble Participation Data which has subsummaries when sorted by term.

       

      My problem is that I don't want the teacher to have to input their students manually for each term.  It's likely that most students will be in the ensemble for the whole year, but not all.  So once they've put the students into their ensemble for term 1, I want them to be able to click a button that automatically duplicates the records of just that ensemble, and renames the "Term" field in the new records to a new term, such as "2".  Ideally there would be a dialog that would ask them what term they would like to be inputted into the new records.  (But if I have to have 3 buttons, one for duplicating the records for terms 2, 3 and 4 then that wouldn't be the end of the world.) Then the teacher can go through and delete any students who have left for that term, and it will also mean they now can mark the role for Term 2, while leaving Term 1's role in tact.

       

      Does that make sense?  Any ideas about how I would go about doing that?  

        • 1. Re: Help with script for duplicating records
          user14360
             Sorry, I forgot to say I'm using Filemaker Pro 10 Advanced on a Mac and am quite new to Filemaker.
          • 2. Re: Help with script for duplicating records
            philmodjunk
              

            This script assumes that you've already pulled up the list of ensemble participation records you want to duplicate on an ensemble participation based layout. If this is not the case, you'll need to add some steps in the beginning to do that.

             

            Variation 1: Using a custom dialog to ask for the term:

             

            Define a global field gTerm and specify it as the input field in the custom dialog step in the following script:

             

            Show Custom Dialog ["What term?"]

            Unsort Records

            Go to Record/Request/Page [ First ]

            Set Variable [ $RecCount; Value:Get ( FoundCount ) ]

            Loop

              Set Variable [ $Rec; Value:Get (RecordNumber) ]

              Duplicate Record/Request

              Set Field [Ensemble Partcipation::Term ; gTerm]

              Set Variable [ $RecCount; Value:$RecCount - 1 ]

              Exit Loop If [ $RecCount ≤ 0 ]

              Go to Record/Request/Page [ No dialog ; $Rec + 1 ]

            End Loop

             

            Variation 2: Place a button for each term on the layout, set each to run the same script but pass the Term as a script parameter:

             

            Unsort Records

            Go to Record/Request/Page [ First ]

            Set Variable [ $RecCount; Value:Get ( FoundCount ) ]

            Loop

              Set Variable [ $Rec; Value:Get (RecordNumber) ]

              Duplicate Record/Request

              Set Field [Ensemble Partcipation::Term ; Get ( ScriptParameter) ]

              Set Variable [ $RecCount; Value:$RecCount - 1 ]

              Exit Loop If [ $RecCount ≤ 0 ]

              Go to Record/Request/Page [ No dialog ; $Rec + 1 ]

            End Loop

             

            • 3. Re: Help with script for duplicating records
              user14360
                

              Thanks for that.

               

              I ended trying something a little simpler, which was setting the global variable for the "term" to what I wanted it to be, going to the found set, going to the first record, starting a loop, duplicating that record, omitting that record, going back to the first record, omitting that record, which leaves me at the next record to duplicate and so where my loop ends.  It exits when F0undCount = 0.

               

              Seems to work at the moment.

               

              I'll try to get my head around your script but I haven't used variables much before so it may take me a while!  : ) 

               

              Thanks again. 

              • 4. Re: Help with script for duplicating records
                philmodjunk
                   I see no significant advantages over such a small record set between your script and mine. They're simply variations of the same method.