AnsweredAssumed Answered

Insert multiple related records

Question asked by Kobyashi on Feb 13, 2010
Latest reply on Feb 14, 2010 by Marx


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