8 Replies Latest reply on Jul 15, 2011 4:18 PM by JeffPark

    Automatically insert/delete multiple records in related table

    ptt555

      Title

      Automatically insert/delete multiple records in related table

      Post

      I'm new to filemaker pro, but have informix database design experience.   Hopefully someone can point me in the right direction here.  All help greatly appreciated!

       

      Table#1 includes fields:

      reference

      startdate

      enddate

      status

       

      Table#2 includes fields:

      reference

      date

      status

       

      CHALLENGE #1

      At time of saving entry into Table#1, I need to insert a new row into Table#2 for each date from startdate to enddate inclusive.   Status would equal "pending" or "confirmed".

       

      CHALLENGE #2

      If the record's status in Table#1 is changed at any time to "Cancelled", I need to delete the related rows in Table#2

       

      CHALLENGE #3

      If the record in Table#1 remains "pending" or "confirmed" but has the startdate or enddate modified at any time, I need rows in Table#2 added or deleted so there remains a row for(and only for) each date from 'startdate' to 'enddate'

       

      FM Pro10 Advanced on Mac


        • 1. Re: Automatically insert/delete multiple records in related table
          philmodjunk

          I'M assuming that your relationship between the two tables is defined in Manage | Database | Relationships as:

           

          Table#1::Reference = Table#2Reference

           

          And that Table#1::Reference is a primary key (Unique identifier, no duplicates)

           

          Create a script:

          Freeze Window

          If [not isempty ( Table#1::StartDate ) and Not IsEmpty ( Table#1::EndDate ) ]

            Set Variable [$Ref ; Value Table#1::Reference]

            Set Variable [$Start ; Value: Table#1::StartDate]

            Set Variable[$End; Value: Table#1::EndDate]

            Set Variable[$Status; Value: Table#1::Status]

            Go To Layout [Table#2]

            Loop

              Exit Loop IF [$Start > $End]

              New Record/Request

              Set Field [ Table#2:: Date ; $Start]

              Set Field [ Table#2::Status ; $Status]

              Set Field [ Table#2::Reference ; $Ref]

              Set Variable [ $Start ; $Start + 1

            End Loop

            Go To Layout [original layout]

          End IF

           

          To delete the rows your script can perform a find on table #2 for all records with the same reference value

          If [ Table#1::Status = "Cancelled"]

            Set Variable [$Ref ; Value Table#1::Reference]

            Go To Layout [Table#2]

            Enter Find Mode[]

            Set Field [Table#2::Reference; $Ref]

            Set Error Capture [on]

            Perform Find[]

            Delete All Records[No Dialog]

            Go To Layout [Original Layout]

          End IF

           

          For your third challenge I'd combine the two actions, Delete all the matching records, then create a new set.

           

          You can run these scripts from older versions of filemaker with buttons. With Filemaker 10 or 11, you can set up script triggers to run them.

          • 2. Re: Automatically insert/delete multiple records in related table
            ptt555

            Phil, thanks for the quick & detailed reply. 

            I'll give it a shot & let you know how it turns out.

            • 3. Re: Automatically insert/delete multiple records in related table
              ptt555

              Phil, that helped me tremendously on the syntax for filemaker.

              Mission accomplished.  Thanks for your assistance!

              • 4. Re: Automatically insert/delete multiple records in related table
                JeffPark

                @PhilModJunk,

                Hi there. I needed something similiar to this and tried it.  However, all I get is blank data insert.

                Table#1

                Start Date: 1/1/2011
                End Date: 1/15/2011

                On Table#2, I get 15 records created but they are all blanks. I'm using FM Pro 11 Advance. I've checked and checked my spelling on the script and there is nothing wrong.

                Any ideas?

                • 5. Re: Automatically insert/delete multiple records in related table
                  philmodjunk

                  Do you mean that you do not get the dates entered into the date field?

                  Please post your script. You can either print your script to PDF, then copy the text to the clipboard from the PDF and paste it here, or you can upload a screen shot of your script to a file sharing site and post a link to that site here.

                  • 6. Re: Automatically insert/delete multiple records in related table
                    JeffPark

                    Thanks Phil!  Here is copy and paste of the script.  I get blank data on all fields in Table#2

                    autocreate
                    Freeze Window
                    If [ not IsEmpty ( Table#1::Startdate ) and not IsEmpty ( Table#1::Enddate ) ]
                    Set Variable [ $Ref; Value:Table#1::Reference ]
                    Set Variable [ $Start; Value:Table#1::Startdate ]
                    Set Variable [ $End; Value:Table#1::Enddate ]
                    Set Variable [ $Status; Value:Table#1::Status ]
                    Go to Layout [ “Table#2” (Table#2) ]
                    Loop
                    Exit Loop If [ $Start>$End ]
                    New Record/Request
                    Set Field [ Table#2::Date[$Start] ]
                    Set Field [ Table#2::Status[$Status] ]
                    Set Field [ Table#2::Reference[$Ref] ]
                    Set Variable [ $Start; Value:$Start + 1 ]
                    End Loop
                    Go to Layout [ original layout ]
                    End If

                    • 7. Re: Automatically insert/delete multiple records in related table
                      philmodjunk

                      See those extra square brackets in your set field steps? That's the problem here. It's a common one for new scripters. So much so that I copy and paste the instructions from a database whenever it shows up her ein the forum:Wink

                      When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Click OK. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Automatically insert/delete multiple records in related table
                        JeffPark

                        Laughing AWESOME!!! Thanks Phil.  THIS WORKED PERFECTLY!