I've got a conundrum.

I'm working on a filemaker database which is used to manage customers purchases and accounts - I'm having trouble putting together a script to run a report on overdue accounts. It should be simple, but I think I'm getting hampered by the structure which I don't think could be easily changed at this stage.

But I'm open to either a scripting solution, or possibly a structural solution if it isn't too tricky to implement.

it's for a gymnastic club so there's a table structure based around the following:

Enrollment > Student > Family < Accounts < Payments

The enrollment table is essentially a purchase associated with a specific student. Some families may only have one student, while others may have 4 or 5.

Each account may have a few different enrollments attached to it (or just one) depending if the student is enrolled in multiple classes, or if there is more than one student in a family enrolled in a class.

The problem that I'm having, is running a report that identifies which accounts are overdue AND unpaid.

It should be simple, however, the way the system figures out if an account is unpaid, is based on the total payments made for a family against the total purchases. This way if there is an over payment on one account, it can be carried over to other accounts (or if we want to partly refund an enrollment but leave the amount in credit on that families record for later use).

So, when I get a script to search for any accounts that are past the due date AND unpaid, it will pick up every account in the past associated with a family who hasn't fully paid a single account (be it overdue or not).

The obvious fix would be to not carry over the calculation of payments in the way that is currently happening. Rather only associate payments to each account. However, for the above reasons, I think this is going to be difficult - particularly with so many records already in the system.