Replacing portal filter script with a table relationship - can't quite get it
Hi! I’m trying to sharpen my FMP skills, and one area I can’t seem to quite wrap my head around is this: I’m attempting to replace a filtered portal with a table relationship, to get what I hope is a speed boost via doing it at the relationship level.
But, althought I’ve gotten close, I can’t quite get it to work.
THE ORIGINAL SETUP (which is admittedly messy but works):
The DB is for a charitable organization. I had a portal on the “Dashboard” (attached to the Globals table), which showed records from a Contacts TO (called ContactsForReceipts), and was intended to show all Contacts who require a tax receipt. A Cartesian join between a field in Globals called “ShowCurrentYearOnly” (displayed as a checkbox) allowed only current-year donors to show up in the portal if checked.
(Full disclosure: I currently can’t recall how this checkbox does its job of filtering - if it still does! But that’s a problem for another day, I think - at least the relationship allows all the needed records to display. I’ll move on…)
To refine what’s shown (other than the “Show current year only” checkbox), I had been filtering the portal by a field in Contacts called “AmountsReceiptNeededSum”. It’s a calculation field which has the amount of that contact’s donations which haven’t yet been applied to a receipt (if any). This field is working correctly and showing an amount for the contacts who need a receipt; for others, it is empty.
The portal’s filter script looks like this:
// Has donations needing a receipt
// Donor doesn’t have an OnGoing Pledge, those receipts are done differently
ContactsForReceipts::CurrentlyHasOGP = ""
THE NEW SETUP
I”m actually making a number of changes so as to build my table structure in a hopefully more best-practices way. This entailed making a “Dashboard” table and putting my dashboard layout there, not on the Globals table.
I want to use the same script logic from the old portal, in my shiny new relationship setup. But since I can’t just use the same language, i.e. inquire if a field is empty, I presume I have to compare it to a global field which is set up for this purpose with no value, and represent a Boolean “false”. That’s what I did…. but it doesn’t work.
The relationship has two parameters:
ConnectAllContacts x _ID_contact, AND
BooleanNo (does not equal) AmountReceiptNeededSum
But ALL the Contacts are listed, not just the ones who need a receipt. I’ve tried:
AmountReceiptNeededSum > BooleanNo
AmountReceiptNeededSum (is not equal to) BooleanNo
But no joy.
I hope I’ve given enough info to make it clear to someone what I”m missing…. I have tried and tried but I just can’t see it.