Calculation Almost Works

[millrene]

I have been struggling for over a week to solve this problem and I am hoping somebody can see my problem.

I am trying to get a beginning balance calculation to work. I have a doantions table, disbursement table and a cause table. The cause table keeps tracks of what a donation was given towards. I need to get a calculations that will show me the beginning balance for each Cause. This would be Donations minus Distributions for each Cause Code (ProjectID).

I have created a calculation that shows me the beginning balance for donations in one field BegBal_R. This is based on what reporting month the user selects. So if they want the beginning balance for January 2007 it would show all donations prior to January 2007. I have set up the same thing for Distributions and the field name is BegBal_D These calculations are working fine.

My problem arises when I try to do a calculation that is BegBal_R - BegBal_D. This calculation works if there has only been one donations and one distribution. Or if there has only been 1 donations and no distributions. If there has been multiple donations it will take the first distribution for that cause and muliply it by the number of donations.

For example one cause had four donations for a total of 285.16. It had one disbursement of $103.81. It calculates the beginning balnce of a negative 130.08, which is 285.16-(4*(103.81))

I don't have any fields defined as repeating. I have the donations table and distributions table related based on a Project ID. Every time a donation is made it is assigned a Cause and the cause has a Project ID. Every time a distribution is made it is assigned a Project ID.

I hope somebody can see the error. I appreciate your time looking into this problem.

Thanks so much.

Rene

[Stuart Taylor]

can you post an example file?

[millrene]

I was afraid you would ask for that. Unfortunately the database is not a local copy. It has been uploaded to FileMaker server. Our Server Admin guy can't remember the password to the FileMaker Admin so we can't get a local copy right now. (He is busy). Maybe tomorrow.

Thanks for offering to look.

[millrene]

I have finally gotten my file off of the server and ready to post. Now I find it exceed the limit to post to this forum. It is an application that we bought from FileMaker and I have customized it to meet out needs. Since it was a pre-built application it has two files and tons of tables, the smallest I can get it is 1.55 MB. I guess there is no hope unless anybody has an idea...

[Ender]

If you haven't already, Save As a Clone, then just import or add a small sample of records in the relevant table(s). You can thin it down more by deleting layouts and tables that are not relevant to the question.

Then use a compression utility to zip and shrink the file a bit further for posting.

[millrene]

Thank you Ender. You gave me the courage to delete some of the files that aren't part of my problem and get it slimmed down. I have the file attached. It isn't going to look pretty once you open it because I had to delete so much. It is also password protected (sorry). You must use ADMIN and FMSA (in caps). Once you get it open go to Scripts and run the only script that is listed. It will take you to a layout where you will select the month and year. Please select January 2007 because I know the data for that time period.

The first page of the report won't show you the problem. Go the next page. You will see Beg Bal_R and Beg Bal_D and Total Beg Bal, which is suppose to be Beg Bal_R minus Beg Bal_D. In some cases it works correctly, but in some cases it doesn't. I explained what it does in my first post if you want to check that. If anybody can solve this mystery it would be so wonderful. I have been pondering it for the last two weeks. Ugh!

FileMaker_Donations.zip

Edited February 9, 2007 by Guest