YTD total of a field

[dayspring]

I have a data base that tracks donations to our organization. The donor table is in a one to many relationship with with the donation table. Each donor's donation is recorded in the donation table through the year. The "donation_total" field is the amount of each donation received. Each donor will make several donations through the year. I want to create a calculated field that shows each donor's YTD donations. What expression could I use to do that?

[Stan Mathews]

Re: YTD total of a field

Create a calculated field

ty_yes= if(year(your_date_field) = year(date()),donation_total,0)

Then drop that calculated field in the appropriate footer and specify total.

You might want to create an inverse of your present set with donations as the parent linked one to one to donors.

[dayspring]

Re: YTD total of a field

Stan,
I get an invalid or incomplete expression. Here is what I did: expression.JPG In my first post I identified the total field as "donation_total" The correct name is "Total_Donation" as in the attached clip. The date field is: "Donation_Date".

[Stan Mathews]

Re: YTD total of a field

I wasn't clear.

Define the calculated field for the report.

report calc.png

[dayspring]

Re: YTD total of a field

Stan:

Thanks for your quick reply. I am sure you are used to dealing with people that actually have an idea what they are doing. I am trying to replace a dbase written in Lotus Approach. I was wanting to create a form with the YTD field in the form as shown in the clip, Form.JPGrather than a report. Is that possible in Alpha?

[Stan Mathews]

Re: YTD total of a field

Possible yes but difficult to explain. Can you attach a sample of your database or at least the set upon which the form is based?

[dayspring]

Re: YTD total of a field

Stan:

Here it is: The field that I want to create the expression for is the "Total_Year" field located in the "donation" table. I want it to give the YTD total for each individuals donations from the "Total_Donation" field. I want the "Total_Year" to appear in the "Donations Browse" Form so I can see, not only the date and amount of each donation, but also their total for the year. Thanks for your help with this. It is way beyond my expertise.

Roger
FILE.ZIP

[Keith Hubert]

Re: YTD total of a field

Roger, what total do you want to display if donations are from different years?

[dayspring]

Re: YTD total of a field

I basically save the table for each year in a file and then erase the data in the donation table and start over with a clean copy for the new year. So there will only be donations for one year. We use the information for receipt purposes and do not need to compare the data from one year to another. Any comparisons of one year to another can be done in a seperate accounting program that we use.

[Keith Hubert]

Re: YTD total of a field

Hi Roger,

Have a look at Alphasports Invoice and see how the Invoice Items are totaled on the form.

In your case I think it would be total = total(donation_Items->Total_Donation,GRP->Id_Number)

[dayspring]

Re: YTD total of a field

Keith,
Still getting invalid or incomplete expression. I tried several different combinations but the only thing close that does work is total(Donation->Total_Donation) that gives the total for all donations for all donors for the year. I need an expression that gives me the total for each donor.

Roger

[Keith Hubert]

Re: YTD total of a field

Hi Roger,

Did you look at the Alphasports Invoice sample?

Have a look at attached.

I changed link in set and used different field to get totals as per your screen shot.

[dayspring]

Re: YTD total of a field

Keith,

What you have there does work but it is not letting me add additional donations to an individual. If you go to the Donation form and click on the ID number it does a lookup for the different donors and then enters their ID number and name. You can go ahead and enter your new donation and when you save it the ID number changes to a different number and it will not allow you to change it back. Also in the Trace Window it is showing several error codes:
Warning [1]: Error(s) opening rules for field: TOTAL_YEAR
Warning [2]: Error adding calculated rule to database engine.
Warning [3]: Error(s) opening rules for field: TOTAL_YEAR
Warning [4]: Error parsing 'calc' rules expression.

Any ideas?

Roger

[Keith Hubert]

Re: YTD total of a field

Hi Roger,

You created the form as a duplicate but did not change any of the settings. eg: you have Add New Record button, but in properties new record is restricted. You asked how to get the total to show on the form, I suggested you look at Alpha Sports, did you do that?

Start with a blank form and see if you get the same errors.

[Tom Cone Jr]

Re: YTD total of a field

Roger,

Unzip the attachment to an empty folder. I've modified your form (Donations Browse) to display the donation total for each individual.

In your scenario there's no reason to filter the records down to current year donations, since you're doing that manually each year. By definition, all donation records are in the current year.

In two separate cases you've used the same name for a table and for a set. I strongly recommend against doing this. Use distinct names. For everything.

You will notice that the computed total for each individual is NOT in the browse object on your form. For reasons that aren't clear to me you have asked that the individual total be shown in the browse. This cannot / should not be done. Each row in the browse control represents a single physical donation record. In order to display the computed total for a given individual you'd have a column labelled something like "YTD total", and then the same computed value would appear on each row. This is horribly inefficient and is apt to be confusing to the user. Instead of a column of identical numbers, each of which would have to be separately computed, just put the ytd total on the form OUTSIDE the browse object.

If you're really talking about displaying a "running total" on each row of the browse that's a wholly different matter, and something you have not yet requested.

Lastly, I've removed your "new record" button from the form. Your form is based on a set containing two tables. Alpha Five stores data in tables not in sets. You, as designer of the form, must provide for CRUD functionality for both tables. (Create, Read, Update, Delete) This cannot be handled adequately with a single button.

Good luck with your project.