Related data chart questions

[halfdome]

I am trying to create a chart that I thought would be so simple...and now I'm down this rabbit hole trying to make it work. I have a database is a record of all our donors. What I am looking to do is create a chart that will display donation info by month on each donor record. I have four tables: Donors, Donations, Correspondence, and a join table called DonorCorrespondence. Donors and Donations are related through the DonorID field. Specifically, I'm trying to set up a column chart in a tab panel on a form view layout of the "Donors" table that will display data from the "Donations" table. The y-axis data is the sum of all donations in a given month by a specific donor and the x-axis is month. This post is the model for how I am going to set up this related record chart.

 

There are two additional elements that I'd like to have for this chart that I haven't been able to figure out for this situation:

 

1) I would like for it to dynamically display only the last 12 months of donation data

2) I would like for it to display the month (x-axis) even if there are no donation records for that month. For example, say I have a donor named John has given $100/mo for the last year. But one month, no donation comes from John. My chart would not show a month as blank for that month, it just wouldn't display anything for that month at all. Only months with donation records appear on the chart. Something like "JAN FEB MAR MAY" if April didn't have a donation. What I'm looking for is to have a visual cue as I flip through my donor records that they may have not given on a particular month.

 

I've seen posts that at least somewhat relate to both of these questions, but not in the context of a related records based chart.

 

Thanks for any help with this!

 

[MacEasy Computing Inc]

Hi,

 

For speed reasons and others I generally put my charts into variables.

 

 

 

So for you x-axis set your data like this

 

$$chart1xaxis = "Jan¶Feb¶Mar¶Apr¶May¶Jun¶Jul¶Aug¶Sep¶Oct¶Nov¶Dec"

 

for your y axis

 

Loop over or use sql query or whatever you want to get your donations by month (y axis data).

say there were donations of $50 in Jan and $250 in April,

Remember you need place holders for your blank months.

 

$$chart1yaxis = "50¶¶¶250¶¶¶¶¶¶¶¶"

 

Typically for large clients,  we have a nightly routine that updates their monthly sales numbers into a table for each client.   That way we don't have to wait for summaries or anything to occur in realtime for large data sets, it's all saved.  Then usually we just use the list function to get the sales data.  List(salesbymonth::amount) and set that into our variable on record load.

 

If you plan to have multiple client windows open you run into a problem with your variables, but you can learn about dynamic variable creation elsewhere so that each customer record has it's own set of variables for it's own chart, or just create a table of related data by month as mentioned above.