Although I've managed to develop a successful database for a nonprofit I volunteer for, to track their donations and generate receipts, I now realize just how limited my FileMaker knowledge still is, as I try to add a key feature. I'm trying to wrap my head around more complex table relationships and filtering, but seem to keep falling down one rabbit hole after another. I hope that you fine folks can at least point me in the right direction.
My DB has these tables (among others) and fields (among others)
ContactFullName (stitched together from other fields in this table)
_ID_receipt_f (foreign key of receipt if it has been generated)
I want to show all the receipts that currently need to be generated, in a portal on my "Dashboard" page (which is tied to the Globals table). I'd like the portal to show contacts who:
- Are NOT currently a "monthly donor" (Contacts::IsCurrentlyMonthlyDonor is false, since their receipts are generated a different way just once a year)
- Have outstanding donations with no receipt yet (NeedsReceipt is checked/true but _ID_receipt_f is empty for any of their related Donations)
Ideally, each line in the portal would list the contact's name, and the total amount of their donations needing a receipt. But I'd be OK with it listing each donation on its own line, with some repeats of the same name, where there are multiple donations. Not as slick, but acceptable.
Finally, a button would run a script to generate the receipts. That i think I can handle, I've done it elsewhere in this DB!
But I've run into a multitude of questions and problems. Mainly, I can't figure out how to display the required subset of info in the portal.
- Should this be done via multiple table relationships, or by filtering within the portal itself?
- Should the portal be linked to Donations or to Contacts? I'm thinking Contacts, because one Contact is, in turn, linked to many Donations... but maybe that's backwards.
- I can't figure out how to get the portal to show only Contacts that have related Donations whose _ID_receipt_f is empty (let alone filtering out folks with monthly donations).
- I tried making Global fields for the criteria in question - "HasMonthly" and "HasRelatedReceipt" both as empty text fields, then comparing them in the table relationship. But it resulted in no portal content at all.
I feel like there's some small key principle I'm missing that would make this easy.
I am continuing to tinker but hope one or more folks here can provide a little direction...