Blank fields still contributing to calculation

[Adam Abrams]

Hi! This feels like it will be tricky to describe concisely, but I'll do my best. Hopefully the picture helps. To summarize in one line:

Summary fields in my crosstab report work for most records, but in part of the report they are blank for no apparent reason. BUT, other fields that depend on these "blank" fields for their totals have correct amounts in them!

The details, in a nutshell:

My database is a list of donors to a charity, along with each donor's individual donations (in a related table of course).

I've created a crosstab report using the technique described in Filemaker.com's Reports tutorial. It "mostly" works correctly but I'm getting some irrational displays for only certain records.

I need to provide a grid-style report on donors who have a regular monthly donation. The report shows the months across the top, donors down the left side, and shows everyone's total donations for each month. Down the right side is a summary of everyone's total donations for the year. Along the bottom are total donations for each month, and for the year, from everyone.

On the Donations table, I set up fields to facilitate my crosstab layout. MonthDeposited calculates the month each donation was received. There are also 12 summary fields, one for each month, and that donation's amount gets copied into the appropriate one (i.e. If a donation is received in March, it goes into the March_Donation field). Finally, there are 12 summary fields that calculate the total donations for each month - JanTotal, FebTotal, etc.

The crosstab layout includes a "Sub-Summary when sorted by donor" section, with the donor name, followed by the 12 monthly total fields, and one more at the end that "totals the totals". I also sort the report by the type of donation (this info comes from a popup list for consistency).

And the report works great. It has headings for the different donation types, followed by a list of the donors for that type, with each donor's monthly totals and their total for the year. See image below.

BUT...

One type of donation gives me only empty space where those donors' monthly totals should go. You can see this in the image attached below.

AND YET...

The annual total for these donors is correct! It's as if the fields have the correct numbers in them, but I just can't see it. Except that I can click in the fields of the report and there really is nothing there.

MORE WEIRDNESS!

Originally it was just one group of people, whose donation method was "Online," who had blanks in the report. So that entire section only, had blank amounts (but correct annual totals in the last column.) And for these people... if I change the method of their donations to other types, in order to make their donations appear under the other categories... everywhere they appear, their donations are STILL BLANK. And yet all the subtotals and grand totals are still correct!

IS THIS A CLUE?

Trying to troubleshoot, I put all those summary fields into the Donations layout so I could browse the records and watch the values. But the "JanTotal" etc. fields stayed blank as I browsed - until I clicked in one of them. Then a number appeared. Then when I went to the next record, all 12 were filled in as they should have been all along.

Have I inadvertently set up these fields to only update when I "commit" or click on them somehow?

And yet they DO work... they're just invisible... sometimes.

Apologies that this was a difficult problem to describe - hope I include enough information for someone to suggest possible solutions.

Many thanks!

adam

[comment]

Can you post a copy of your file with some fake data to demonstrate the problem?

---

BTW, what do you do with donors who have donated by more than one method?

Edited March 18, 2011 by comment

[Adam Abrams]

Here's a "dummy" file which has been genericized but with enough data to show the problem. The donors who should appear in the report have "Currently has OGP" checked under the "Additional Info" tab of their listing. (OGP = On Going Pledge.)

To see the problem, go to the Reports layout (there's a handy button for it), and hit "Create OGP Report".

Interestingly, I find that NONE of the donations show up in the report here, though most do in my "real" database.

I added all my summary fields used in the crosstab, to the Donations layout (another top-level button). Note that they remain empty though some of them should be showing figures...

what do you do with donors who have donated by more than one method?

They would appear multiple times under different headings if they donated different ways. Not really an issue as people don't tend to switch methods of donating.

THANK YOU for checking into this!

DonorDatabase.fp7.zip

[comment]

The ReportYear field in Globals should be a global field, I think.

Interestingly, I find that NONE of the donations show up in the report here, though most do in my "real" database.

These would be the donations that have receipts - they can "see" the report year through the x relationship between Receipts and Globals.

[Adam Abrams]

The ReportYear field in Globals should be a global field, I think.

Aha! Brilliant - thanks. I knew there would be something like that, something I'd never have even thought about...

These would be the donations that have receipts - they can "see" the report year through the x relationship between Receipts and Globals.

Right! THAT'S what's different about those donations - the online ones don't require us to provide a receipt.

I think I'm only about halfway there on the subject of table relationships - it's the concept I'm least confident about even after having made a lot of headway in other areas. Especially "x' relationships. Even though I've employed them already, I want to do more studying of the principles involved until the penny drops and i really "get it"!

Oh, and I found another totally off-the-wall mistake of mine that was throwing my results off - I'd set the Donations layout, when entering it, to automatically set the Type field to Direct Deposit via a script trigger... which I'd (wrongly of course) thought would just set up a "default value" for the Type popup when adding a new donation. But I was too clever for my own good - it also was changing the Type of already entered donations when I simply viewed them through that layout. Oops! I think that led to confusion about what Type a donation was, and the erratic results of my Report.

Anyway, thanks for your truly priceless assistance! I hope to be enough of a confident Filemaker whiz to someday pay back the forum for the incredible help I've received.

All the best,

adam

[comment]

Good. BTW, you could eliminate 22 fields from your solution by using a repeating calculation field to split the amount into months, and setting the summary field to summarize individual repetitions.