6 Replies Latest reply on Mar 24, 2015 9:00 AM by cosmocanuck

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

    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

       

       

       

       

       

       

        • 1. Re: Replacing portal filter script with a table relationship - can't quite get it
          philmodjunk

          You can't use a null value to match records. A match field that is empty does not match to any records on the other side whether they are empty or not.

          You'll need to devise match fields that are not empty when they need to match values in order to show in your portal.

          And I see that you have global or unstored calculation fields on both sides of this relationship as indicated by the "T - connector" that identifies such match fields. That won't work either. You need stored, indexed fields on the "far" side of the relationship at least. By that, I mean that if you are referencing related records in Table 1 from the context of Table 2, the match fields in table 1 must be stored and indexed values.

          • 2. Re: Replacing portal filter script with a table relationship - can't quite get it
            cosmocanuck

             

            Thanks Phil. So, given your comments, I'm unclear on the appropriate way to recreate my calculation comparison, "if AmountReceiptNeededSum > 0", using a field comparison. 

            Do I set up a number field in my Dashboard table with a value of $0.01, and then do a ">=" comparison? 

            And since my Dashboard table's fields will be on the left side when I set up this relationship, is the above even possible? Or does it have to be 

                 [$.01]  <=  AmountReceiptNeededSum

            ?

            Thanks!

            • 3. Re: Replacing portal filter script with a table relationship - can't quite get it
              philmodjunk

              You can just use the value 0 for your match field on the dashboard side of the relationship.

              But I don't know how amountReceiptNeededSum is calculated. Your screen shot shows that it is currently an unstored calculation and that won't work for for a relationship evaluated from dashboard. You'd need to figure out a way to get this value into a stored, indexed field in every record of Contacts. That might be possible with a transactional implementation where each time a value that  is used to compute this sum is modified, it triggers a script to update a number field in Contacts.

              Or you might define an ExecuteSQL() function call in a calculation field in Dashboard that returns all _ID_Contact values in a return separated list. You can then use this calculation field as a match field to _ID_Contact in an occurrence of your Contacts table.

              • 4. Re: Replacing portal filter script with a table relationship - can't quite get it
                cosmocanuck

                Thanks. I’m attaching a screenshot of the actual portal, just FYI…. 

                 

                So, just to make sure I'm clear on this conceptually:

                 

                - The portal filtering technique I have been using works perfectly, but this is because that comparison, made in a calculation field, can look at unstored fields with no problem (i.e. "ContactsForReceipts::AmountReceiptNeededSum", aka "AmountReceiptNeededSum has a value").

                - If instead, I want to do this comparison in the underlying table relationship, I can't compare "0" with the AmountReceiptNeededSum field because it's unstored.

                 

                Correctimundo?

                 

                As for the “AmountReceiptNeededSum” field…

                 

                It all starts on the Donations table, which has these relevent-to-our-discussion fields:

                 

                ReceiptNeeded? (boolean checkbox)

                AmountReceiptNeeded

                 

                The AmountReceiptNeeded field takes the same value as the donation… IF “ReceiptNeeded?” is checked (and thus is a Boolean “yes”).

                 

                One wrinkle (and the explanation I had forgotten for how the “Show Current Year Only” checkbox works on that portal in question) is that it will NOT take that value, and will remain empty, IF the field on the dashboard called “ShowCurrentYearOnly” is checked. 

                 

                “AmountReceiptNeededSum”, unsurprisingly, is the total amount, from all related donation records for a contact, that needs a receipt.

                 

                 

                My understanding, then, is that in order to create a new indexable, stored field for each donation, I’d have to trigger it being filled (or not) with the same value as AmountReceiptNeeded, via a script trigger on the Donation Amount or (and?) the “ReceiptNeeded?” fields. 

                 

                But as you say, that would happen only when the value of either is modified. How do I go through all the existing donations, and get that field filled up?

                 

                You wrote…

                 

                you might define an ExecuteSQL() function call in a calculation field in Dashboard that returns all _ID_Contact values in a return separated list. You can then use this calculation field as a match field to _ID_Contact in an occurrence of your Contacts table.

                 

                Not quite sure how this would work, but perhaps my above explanation makes it moot? Let me know… I do hope I can avoid executing any SQL, I must admit…!

                 

                Thanks!

                • 5. Re: Replacing portal filter script with a table relationship - can't quite get it
                  philmodjunk

                  One of the reasons for using a portal filter is that it can use the value of an unstored calc where you can't in a relationship. 

                  Replace Field Contents can update such a field for your existing data. 

                  • 6. Re: Replacing portal filter script with a table relationship - can't quite get it
                    cosmocanuck

                    Right, I should have remembered that Replace Field Contents is my friend in this situation... thanks!

                    And thanks for all your help on this.