I need some more information regarding your business workflow.
Is this a manual process or an automatic process?
When a new month begins, perform a find for the previous month of the current year of your Balance records. Grab the closing balance from that record and store it in a variable. Then, proceed to create a new balance record for the current month and year and use that closing balance variable for your Opening Balance field.
You can use ExecuteSQL() or GetNthRecord() to get it. ExecuteSQL() would probably be easiest. You could also set an auto-enter value in the field using "value from last visited record" if you are on the record that needs to carry the closing balance over when the new record is created.
How do I get it to do this without a self join which ends up crashing the programme.
A self-join is not the asnwer here, but it should also not crash the program. At what point does FM crash? This could be an indication of the health of the file...
I guess I don't see why FileMaker can't go from one record to the next to store the ending balance in the new month's starting balance.
You could create a field that stores YYYYMM and a second field that is YYYYMM - 1 (have to adjust end of year issue) and then do a join on those first and second fields and have a summary field that calculates the ending value or something like that, but what a pain.
Mike's two suggestions work well. The GetNthRecord is fun for grabbing values from another record in a found set, but I would probably do the ExecuteSQL like Mike suggests.
Maybe something like this:
Let ( [
F1 = DateFieldOfThisMonth ;
F2 = Date ( Month ( F1 ) - 1 ; 1 ; Year ( F1 ) ) ; // Last Month's Starting Date
F3 = Date ( Month ( F1 ) ; -1 ; Year ( F1 ) ) ; // Last Month's Ending Date
F4 = "SELECT
TransactionDate >= ? and TransactionDate <= ?
FETCH FIRST 1 ROWS ONLY" ;
// This assumes you have a primary key serial number that increments and the highest serial number is the last balance value
// This could also be based on something else like highest creation timestamp.
F5 = ExecuteSQL ( F4 ; ¶ ; ¶ ; F2 ; F3 )