Just giving this a bump to see if anyone has any suggestions in case this was missed - no one chimed in the first time around but I'm hoping many of you out there have had to tackle this before in similar Invoicing solutions.
I'll stab at this.
This is, I think, I familiar challenge for many.
I think I've probably tried most, if not all, of the ways around this over the years. My recommendation would be to set an "Open" flag that gets set by script trigger when entries are made in invoice line items or payment line items. Make sure you save the entire transaction in one go, though (see Todd Geist's writings on this), so you don't end up committing a quantity or price without committing the change to the open flag, or vice-versa.
1. With a calculated "open" flag: You could relate from Payments to Invoices where customer=customer (all the customer's invoices), then from there a self-relationship fom PAY_INV to PAY_INV_INV_open where inv::id = inv::id and calcOpenFlag = 1. It's a workaround for when you can't get the unstored calc on the "left", but be aware that this can be a performance killer if the one customer has a heck of a lot of invoices.
2. With a calculated "open" flag: You could script searching for open invoices, grabbing their keys, then use the "magic value lists" technique to create a value list based on the global variable holding your invoice keys. http://www.modularfilemaker.org/module/virtual-value-list/ Again, not great performance-wise because the search on the unstored open flag is going to take a while.
3. With a set "open" flag, but no script triggers: You could run a server-side script periodically (somewhat frequently) to find any modified payment or invoice line items and reset the flag for the related invoices. You might want to do this anyway even if you go the script trigger route, just to "bat cleanup" in case someone manages to edit a line item and get around your script triggers.
Options 1 and 2 tend to work for smaller solutions, but bog down for larger ones. Option 3 leaves you dependent on the timing of a server-side script. I've seen, and built, solutions that use each of these methods and combinations thereof, but I'd recommend in 20/20 hindsight that the script triggers are the way to go. It "feels" harder and less dynamic, but in the end I think you'll find it's actually less hassle, especially as the solution grows.
So, that's my two cents, for what they're worth. I hope this helps. Better yet, I hope someone posts a better and easier way for you. I'll be watching this thread.
Could you create a status field in the invoice that gets flagged as being paid when the invoice is paid in full. Then used that field for your relationship.
I had another "status" field that I was using to indicate the status of the Invoice (Paid, Open etc) and was using that to establish a relationship to the Payments table. I was hoping to do it without an additional field but it looks like it's not possible.
We can't rely on a server side script to run so it looks like we'll have to go with the trigger based approach.
Appreciate you chiming in.
Thanks - it looks like this is the way to go with a trigger that updates a secondary field to to track the "status" of the Invoice.