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

Title

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

Post

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

    ContactsForReceipts::AmountReceiptNeededSum

 

    and

 

    // 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.

 

Thanks!

Adam

 

 

 

 

 

 

Outcomes