Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4635 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hello all. I have an issue for which I have been using the same solution for a long time but was thinking that perhaps I am missing something and one of you brilliant types may have a better way to solve this problem.

We have several tables that keep numerical data that needs a running balance, with a total at the end. However, it is also necessary sometimes to pull a report which is a sub-set of the data in the total, say for a particular date range. The problem is if you restrict the data to a sub-set of the data, the balances will only be for the sub-set of the data.

I have been solving this up to now by using a process which finds a subset of the data up to one day prior to the desired sub-set, getting the balance of that data, creating a new "opening balance" record with that data, and then running the report including this opening balance record. This works. However, there are always drawbacks on using "temporary" records, such as if the system fails in the middle of one of these reports, you now have data that does not belong there.

Here is the original data set.

dataset.png

Now if you do a sub-summary report sorted by account and date, you get this:

subsum.png

So far, so good. However, when you filter the data by the "filter dates" shown in the original dataset, you end up with this result, which is wrong:

subsumwrong.png

By running a process that gets the previous balances of the accounts, getting their balances "pre-filter" date, and creating records, one can active the desired result:

subsumcorrect.png

However, I am hoping there is a more elegant solution?

I have attached a sample file with the data in the images.

Thanks all!

Balances.fp7.zip

Posted

How about keeping the running balance as a stored field in the account table?

Posted

Unfortunately that is not a great solution for us. Much of our data is uploaded, or is entered automatically by other processes. Data may be entered into this table "out of order" so it means that every time a record comes in, it would have to reset that field throughout the entire table... :hmm:

Thanks for the suggestion Fitch!

Posted

Yes - I run into this quite often. Unfortunately, I do not think there is a way around producing Balances Report without some sort of batch procedure at the time of the report. It usually requires storing opening balance in a table (or somewhere) and even creating temp records.

Please check out this little example file I whipped up. Hopefully it will get you going in the right direction.

Balances_dwdc.zip

Posted

Hi Don,

Thanks for your example and your comments. You may have missed that I wrote I actually have solved this as well, years ago and do it with a process which creates opening balance temp records, and then deletes them. I was just hoping to hear that someone much smarter than me has an obvious simple way to do this that I have been overlooking for years (this happens to me a lot on this forum!).

Unfortunately, it seems that in this case I need to stay the course.

Posted

That's cool. I agree, it would be nice if FMP handled this type of stuff a much better. I have another idea that I will experiment with. If I stumble upon something I will share it with you.

This topic is 4635 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.