running total in portal not updating

[acoustic_overdrive]

I have made a database to track my client's invoices. Each client may have many invoices and each invoice may have many line items, each with a value.

I wish to be able to see a list of a client's invoices in a portal with a total for each invoice and a running total showing the balance of their account increasing with each incvoice listed.

The problem I have with the way I have set it up is that when a line item is modified in an invoice, the running total in the portal in the clients layout is not being updated. Changing layouts and going forward and back one record does not update it, but quitting and reloading the file corrects the running total.

Could anyone have a look at the attached example and see what I'm doing wrong?

summary_update_prob.fp7.zip

[jgerton]

I got it to work by adding a field to the "Clients" table.

The field is a calc field with the following calculation:

Sum(invoices::invoice total)

Hope that's what you're looking for.

[acoustic_overdrive]

I don't think it's quite what i'm looking for, that gives me a static total of all invoices relating to one client, but I want the running total to be displayed in the right hand column of my portal on the client layout.

I did already achieve this using a running total summary field called "all invoices running total", but it will not update correctly when invoice line items are modified, without closing and reloading the file.

[comment]

1. Placing related summary fields in a portal is an undocumented feature. Although it seems to work reasonably well, esp. in ver.8, it has its quirks. Use it at your own risk.

2. Try using the Refresh Window [Flush cached join results] script step to get the portal to update.

[acoustic_overdrive]

Thank you, "Refresh Window [Flush cached join results]" worked perfectly.

If this is an undocumented feature, is there a better way I should be doing this? All I want to do is create a running balance. Payment receipts will also be shown in the same table so each entry is either an invoice with a negative amount or a receipt with a positive amount, thus giving a running balance showing how the account ran over time.

I saw this post and a similar thing is done using a "greater than or equal to" relationship. But when i tried to modify my solution to work like this it got a bit complicated as I found that I had to make my portal sort order behave the same way the inequality was behaving and I have to admit I gave up quite early. Having both invoices and receipts in the portal and some occurring on the same date made it a little more complex than the example file I downloaded.

[comment]

Until FMI get this working reliably (I believe what you see now is a preview of a planned feature), the conservative method is to produce a report in the local table.

If you need this in a portal, you could use the > relationship. It's much easier to implement than that demo would lead you to believe - but it will get slower and slower as the amount of records piles up.

Another option is to lookup the previous balance at record creation - however, this requires a cascading relookup when existing record are edited/deleted.

BTW, you can sort a relationship one way, and the portal/s based on it another way.

[acoustic_overdrive]

OK makes sense, thanks. I think I'll keep it this way for now and see how it goes, but i'll look into using the > relationship.