I can't fully follow your description. Try posting an example where you spell out the numbers and values where this goes wrong.
Have you tried using a summary field set up as a running balance for this in place of a Get Nth Record calculation?
I did try working with a summary field, but for various reasons (way too long to go into now) it didn't give me the results I was looking for.
Hmm, let's see. I guess the best way to describe it is to give the code for the Get Nth Record calc:
Case (IsValid (GetNthRecord ( Billing to Billing by Client Reversed::Client Balance Due Reverse Order ; Get ( RecordNumber ) - 1));
GetNthRecord ( Billing to Billing by Client Reversed::Client Balance Due Reverse Order ; Get ( RecordNumber ) - 1))
Where Client Balance Due is the balance after the previous balance due has been added to the current charges and any payments have been subtracted. The reason for the Case / IsValid part is so that when there is not previous record (when the current record is the first bill that's been generated) it will return a zero value.
This works great for everything else, but when I create a filtered portal based on the same data that calculation thinks that there is no previous bill beyond the first date in the now filtered portal and throws everything off. What I need is a way to grab the previous balance value in the unfiltered portal and make a non-dynamic value out of it.
Is that making any more sense?
Maybe you could use a second relationship to get the previous balance. Or using the existing relationship, you could store the starting filter date while your script runs, change the starting filter date to get the previous balance, then restore the starting date.
The tricky part (maybe) is: what date do we change it to? If I get what you're saying, we want to get the value from one date earlier than the selected starting date. If the billing is done on a regular interval (monthly, weekly, etc) then it's not that tricky, but if you don't know how far back you need to go it might be.
Here's another idea: each time you do billing, store the ID of the previous balance record in the current balance record. Make a relationship (pastID::currentID) and you'll always be able to grab the info you need.
FWIW, in my experience with fiscal systems, it's generally better to not rely on unstored calcs. For one thing, generating reports in a large data set can become painfully slow. The corollary is that your methods of updating the stored balances must be rock-solid.
Ok, I'm giving up and going in a different direction. I'll use my current calc as part of a script when creating a billing record (one-time, not dynamic), but I need to know how to fix a record so it can't be altered after the original data entry. In other words, create the billing record, enter whatever data needs to be entered, then have the user run another script that eliminates the ability to further alter the record.
Any ideas on how to do that?
Search these forums for "lock" -- you'll find plenty of ideas.