3 Replies Latest reply on Mar 8, 2010 7:04 PM by neu

    Split 1 record into 3 records?

    neu

      Title

      Split 1 record into 3 records?

      Post

      I am running Filemaker 10 on Windows 7. I have a contract database (each contract is a record). Each contract has 3 payment dates, and I need to construct a separate layout which gives a schedule of payments due drawn from all the contracts in the database. Is there a way to create automatically, by script or by relationship, a set of 3 different date/amount records in another table or layout, from the 3 date fields which exist in the contract?  Then, once that is created, it is easy to sort in the other table/layout and print out a report of payments due.  Important to note that payments from several different clients could be due on the same day, thus necessitating a separate record for each in the new layout.

       

      Many thanks for any help you can offer --

        • 1. Re: Split 1 record into 3 records?
          Steve Wright

          Since its a fairly small number of fields it can be done quite easily with a script

           

          Here is a quick and dirty script which should get the job done, although I may not have used exact synta

           

          Create your new relationship to the payments, from the invoice:primarykey  to Payments:foreign Key

          Create a script which will go through  your found set and perform something like : 

           

          Assuming your invoices have a primary key field, which is obviously unique between records (if not you should have)

           

           

          Freeze Window
          Go to Record Request First
          Loop
          set variable $primarykey = Invoice::primarykey
            if [not isempty(Date1) ]
              set variable $date = Date1
               Go to Layout Payments
               New Record
                set field payments_table::ForeignKey   = $primaryKey
                set field payments_table::Date = $date
                Go to layout (original layout)
            [end if]
          
            if [not isempty(Date2) ]
             set variable $date = Date2
               Go to Layout Payments
               New Record
                set field payments_table::ForeignKey  = $primaryKey
                set field payments_table::Date = $date
                Go to layout (original layout)
            [end if]
          
            if [not isempty(Date3) ]
             set variable $date = Date3
               Go to Layout Payments
               New Record
                set field payments_table::ForeignKey  = $primaryKey
                set field payments_table::Date = $date
                Go to layout (original layout)
            [end if]
          
          Go to record request page [Next ; exit after last ]
          End Loop

           

           

           

          • 2. Re: Split 1 record into 3 records?
            neu

            Many thanks for speedy reply -- will give this a try today and report back !

            • 3. Re: Split 1 record into 3 records?
              neu

              The solution posed by SWS was effective -- I ended up using a variation on it because of the particular situation. The learning thing for me on this was that I could use such a script as proposed to establish the relationship, and then use that relationship to create the records in the other table. I am now going to test whether I can establish the relationship by using the script to copy the ID number (primary) to the newly-created record in the other table (foreign) -- which I can because I just did it using that script -- and then use that relationship to create a live link to the related records in the other table. This will be particularly useful when someone goes in to the first table and changes a date or amount of payment due.

               

              Again, many thanks for the inspiration --