Portals Based on "Query Relationships"

May 7, 201411 yr

I have a database of Donors I have created a “Dashboard” type view that I would like to add some portals to display useful information on the “Front Page” of the database. For instance, birthdays of donors, Annual donors “month-before donation due” reminder, and “Late” donors, to name a few.

To accomplish this, I’ve created Table Occurrences for the desired Table::field and created a relationship to a global Dashboard::MatchField to create this “query relationship”. I’ve successfully accomplished this for the birthdays and “month-before” reminder listed above.

Where I ran into trouble was with “Late” Donors. The field that I’m attempting to create the “query relationship” with (Partners::DonationStatus) is an unstored calculation, which, if I understand correctly, will not work to display related records in a portal. What the field calculates is the status of Monthly, Annual, and Quarterly donors based on their last donation date (For instance, if the donor is a monthly donor and their last donation is > 31 days ago, they're "Late"). I thought about creating multiple criteria for the relationship between the TO and multiple global Match Fields to essentially replicate the calculation field, but that wouldn’t work because everything is AND in these relationships, so I couldn’t create different conditions (Monthly > 31days OR Annual > 365 days OR Quarterly > 91 days) for all the different partner types in one relationship. I wouldn’t object using this method to make this work, but I would really like to display all three types of Donors in the same portal, not three different ones.

Is there a trick or tip that can work around this problem? I could easily perform a find for this data, but it would just be so much more convenient to have an “at a glance” view of it when the database is first opened.