AnsweredAssumed Answered

Replacing portal filter script with a table relationship - can't quite get it

Question asked by cosmocanuck on Mar 22, 2015
Latest reply on Mar 24, 2015 by cosmocanuck


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):


Screenshot 1


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 = ""





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.