3 Replies Latest reply on Feb 14, 2010 5:20 AM by Marx

    Insert multiple related records



      Insert multiple related records

      Your post

      I'm using Filemaker Pro 10. As part of a project I have a Property table, containing details of residential properties, a tenant table containing all the tenants who live in the properties and an agreement table which contains the tenancy information, such as monthly rent, period of rental and other things. There is also a payment table which contains all the individual payments made (rent) by the tenants. The tenancy table is related to the property table by the agreement table with an agreement ID. Similarly the payments are related to the agreement table. Everything works well.


      On the first of every month I want to insert a zero value payment in to the payment table for each active agreement. This means I need to find all the correct agreements, then insert a new record in to the payment table and insert the agreement ID which is used as a relationship to the agreement table. The only way I can see how to do this is quite slow and it strikes me that I might be missing something really obvious. The script I've written works perfectly, at least as far as the end result is concerned but there is a lot of switching between layouts. It is my intention to run this as a schedule FM Server 10 script and it would be great to know if that has any impact on how I should approach this.


      Help would be really appreciated.



      STARTUP: create monthly OB auto

      Go to Layout [ "#agreements" (agreement) ]

      Enter Browse Mode

      Show All Records

      Enter Find Mode [ Specified Find Requests:Find Records; Criteria:agreement::archiveStatus: "=" ] [ Restore ]

      Perform Find [ ]

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

      Set Variable [ $RIDX; Value:1 ]

      New Window [ Name:"payments"; Top: 0; Left: 0 ]

      Go to Layout [ "#payments" (payments) ]

      Select Window [ Name: "Property"; Current file ]

      Freeze Window


      Go to Record/Request/Page [ $RIDX ][ No dialog ]

      Set Variable [ $PAYDATA; Value:agreement::IDX ]

      Select Window [ Name: "payments"; Current file ]

      New Record/Request

      Set Field [ payments::created; Get(CurrentTimeStamp) ]

      Set Field [ payments::createdDate; Get(CurrentDate) ]

      Set Field [ payments::agreementIDX; $PAYDATA ]

      Set Field [ payments::paymentCode; 4000 ]

      Set Field [ payments::payType; "OB" ]

      Set Field [ payments::amount; 0 ]

      Set Field [ payments::payCom; "Automatically generated monthly opening balance" ]

      Select Window [ Name: "Property"; Current file ]

      If [ $RIDX ≥ $FOUND ]

      Refresh Window

      Show Custom Dialog [ Title:"Message"; Message: "Found " & $FOUND & " RIDX "& $RIDX; Buttons:"OK" ]

      Exit Script [ ]

      End If

      Set Variable [ $RIDX; Value:$RIDX + 1 ]

      End Loop






        • 1. Re: Insert multiple related records

          First of all, you will need to prepare the script you have for compliance with server side scripting.  There is a drop down in the script editor that will alow you to see those steps "greyed out" which could cause the script to fail. They do not need to be removed but conditionally executed based on whether it is an interactive run (client) or scheduled run (server) - you can set a variable to condition those steps so that you can avoid script failure.  (the example image is a startup script that has been adjusted for the various application types that might access it)



          Set Variable [$isserver ; PatternCount ( Get ( ApplcationVersion ) ; "Server" )


          Then insert If [not $isserver] statements around each of the greyed out script steps. 




          • 2. Re: Insert multiple related records
               Thanks for your reply. Perhaps mentioning the server option was a distraction. If you remove that option, how would you achieve this aim using FileMaker 10?
            • 3. Re: Insert multiple related records

              I have found that it is easier and maybe faster to add by relationship.  No layout switching.


              Add Records Table needs a unique serial number.


              Create a target relationship from the found set table (a global field) to the table occurrence of the add records table (serial number) and check "Allow creation of records in this table via this relationship checked" (for the add records table).


              In your processing script:


              Stay in the found set and loop.  You do not need to leave the layout.


              Use the GetNextSerialValue to set the global field in the found set table, e.g. Set Field [globalSerialNum ; GetNextSerialValue ( Get ( FileName ) ; "add records::serial number" ) ]


              Upon the first "set field" via this allow create relationship the record will be created.  FYI - keep in mind all validations and auto enter behaviors will be in effect.


              All the other "set field" operations will follow that you have now - they just need to use the new relationship.