1 2 Previous Next 15 Replies Latest reply on Apr 24, 2014 4:07 PM by cosmocanuck

    Getting portal to include and exclude from related tables

    cosmocanuck

      Hi all!

       

      Although I've managed to develop a successful database for a nonprofit I volunteer for, to track their donations and generate receipts, I now realize just how limited my FileMaker knowledge still is, as I try to add a key feature. I'm trying to wrap my head around more complex table relationships and filtering, but seem to keep falling down one rabbit hole after another. I hope that you fine folks can at least point me in the right direction.

       

      My DB has these tables (among others) and fields (among others)

      - Contacts

      _ID_contact

      ContactFullName (stitched together from other fields in this table)

      IsCurrentlyMonthlyDonor

      [etc]

      - Donations

      _ID_donation

      _ID_receipt_f (foreign key of receipt if it has been generated)

      NeedsReceipt (checkbox)

      [etc]

      - Receipts

      _ID_receipt

      [etc]

       

      I want to show all the receipts that currently need to be generated, in a portal on my "Dashboard" page (which is tied to the Globals table). I'd like the portal to show contacts who:

       

      - Are NOT currently a "monthly donor" (Contacts::IsCurrentlyMonthlyDonor is false, since their receipts are generated a different way just once a year)

      - Have outstanding donations with no receipt yet (NeedsReceipt is checked/true but _ID_receipt_f is empty for any of their related Donations)

       

      Ideally, each line in the portal would list the contact's name, and the total amount of their donations needing a receipt. But I'd be OK with it listing each donation on its own line, with some repeats of the same name, where there are multiple donations. Not as slick, but acceptable.

       

      Finally, a button would run a script to generate the receipts. That i think I can handle, I've done it elsewhere in this DB!

       

      But I've run into a multitude of questions and problems. Mainly, I can't figure out how to display the required subset of info in the portal.

       

      Key questions:

       

      - Should this be done via multiple table relationships, or by filtering within the portal itself?

       

      - Should the portal be linked to Donations or to Contacts? I'm thinking Contacts, because one Contact is, in turn, linked to many Donations... but maybe that's backwards.

       

      - I can't figure out how to get the portal to show only Contacts that have related Donations whose _ID_receipt_f is empty (let alone filtering out folks with monthly donations).

       

      - I tried making Global fields for the criteria in question - "HasMonthly" and "HasRelatedReceipt" both as empty text fields, then comparing them in the table relationship. But it resulted in no portal content at all.

       

      I feel like there's some small key principle I'm missing that would make this easy.

       

      I am continuing to tinker but hope one or more folks here can provide a little direction...

       

      Many thanks!

      Adam

        • 1. Re: Getting portal to include and exclude from related tables
          Mike_Mitchell

          Adam -

           

          When you're puzzling something like this out, it's often best to take a step back and think of it in terms of "things" (or "entities", in database-speak). Write a one- or two-sentence description of the goal, completely ignoring the existing database structure. In this case, it might look something like this:

           

          "Display a list of all contacts who currently have donations without receipts."

           

          OR

           

          "Display a list of all donations, including contact information, that have not had receipts issued."

           

          That description will identify what your solution will entail. So if you want a list of contacts, then your portal points to Contacts. Conversely, if you want a list of donations, then your portal should point to Donations.

           

          From this description, I think what you want is a list of Contacts:

           

          Ideally, each line in the portal would list the contact's name, and the total amount of their donations needing a receipt.

           

          Let's look at it this way, then (since that's your ideal). Do this:

           

          1) In your Donations table, create a calculation field "AmountReceiptNeeded". Use this calculation:

           

               Case ( IsEmpty ( _ID_receipt_f ; Amount ; "" ))

           

          2) In your Customer table, create a calculation field "AmountReceiptNeededSum". Use this calculation:

           

               Sum ( Donations::AmountReceiptNeeded )

           

          3) Show the new "AmountReceiptNeededSum" field in the portal, which should be based on Customer.

           

          4) If you want, you can filter the portal to show only those records where AmountReceiptNeededSum > 0. (But be careful with this one; you may end up with performance issues.)

           

          HTH

           

          Mike

           

          (P.S. - You don't need a separate field for "NeedsReceipt" - at least not one that a user has to manipulate. By definition, if the receipt foreign key is empty, the donation needs a receipt. If you want to display a checkbox for searching purposes, just make it a calculation field.)

          1 of 1 people found this helpful
          • 2. Re: Getting portal to include and exclude from related tables
            cosmocanuck

            Mike, thanks immensely for this. I think it was just the directional assist I needed (plus a pointer to some functions I probabably wouldn't have thought to use). I'll give it a try and let you know. Have a great Easter!

            • 3. Re: Getting portal to include and exclude from related tables
              cosmocanuck

              Thanks Mike, I got it working beautifully! Names and summary amounts. Even showing just those with AmountReceiptNeededSum > 0 was no apparent performance hit, as it's a relatively small database.

               

              BTW, the syntax for the AmountReceiptNeeded field needed to be slightly different than your example:

               

                   Case ( IsEmpty ( _ID_receipt_f); Amount; "")

               

              not

               

                   Case ( IsEmpty ( _ID_receipt_f ; Amount ; "" ))

               

              But once I worked that out, it was perfect.

               

              Of course, it all now leads to further questions...

               

              I'd like to filter this portal to exclude those with an ongoing pledge or "OGP" (they're done differently). So in my portal filtering calc field, I put the following (the table occurence of Contacts being used is called ContactsForReceipts):

               

              ContactsForReceipts::AmountReceiptNeededSum > 0

               

              and

               

              IsEmpty ( ContactsForReceipts::CurrentlyHasOGP )

               

              As I say, the first condition works great, but adding the second one makes no difference. I have contacts still showing in the portal for whom CurrentlyHasOGP is definitely empty (did a search with "=" in that field and they showed up).

               

              Additionally, I'd like to allow the user to filter these results by date, or at least to restrict to the current year. Trying the latter, I made a global boolean checkbox field called ShowCurrentYearOnly. Then I added this (preceded by one more "and") to the calc:

               

              Case (

              Globals::ShowCurrentYearOnly = 1 ;

              Year ( DonationsForReceipts::Date Received ) = Year ( Get(CurrentDate) )

              )

               

              But now I get NO results in the portal.

               

              I thought my logic was airtight, but maybe I'm expressing it wrong...?

               

              Thanks again,

              adam

              • 4. Re: Getting portal to include and exclude from related tables
                erolst

                Case (

                Globals::ShowCurrentYearOnly = 1 ;

                Year ( DonationsForReceipts::Date Received ) = Year ( Get(CurrentDate) )

                )

                 

                should be

                 

                Case (

                  Globals::ShowCurrentYearOnly ;

                  Year ( Max ( DonationsForReceipts::Date Received ) ) = Year ( Get(CurrentDate) ) ;

                  True

                )

                 

                because

                 

                1. if the flag is not checked, you want to see ALL records that pass the other filters, and

                 

                2. Year ( DonationsForReceipts::Date Received ) returns the first record via that relationship, which doesn't allow for a reliable test; you could try Last() (depending on the sort order …)

                 

                Dependings on your workflow(s) and rules, Max() or Last() may not be waterproof, either; but FM's calculation engine can be a bit pedestrian at times … maybe use ExecuteSQL along the lines of

                 

                " SELECT (*) FROM DonationsForReceipts WHERE donorID = ? AND \"Date Received\" BETWEEN ? AND ? " ; portalContactsTO::primaryContactID ; Date ( …) ; Date ( … )

                 

                See the attached file for some pointers.

                1 of 1 people found this helpful
                • 5. Re: Getting portal to include and exclude from related tables
                  cosmocanuck

                  Thanks so much! However.... I hate to admit this but I'm still on FMP 11, so can't open your attached file! Can you possibly save it back a version... or would that disable some of the goodies?

                  • 6. Re: Getting portal to include and exclude from related tables
                    keywords

                    Why don't you download a trial version of fm13 to open erol's file.

                    • 7. Re: Getting portal to include and exclude from related tables
                      cosmocanuck

                      Well, this was the strangest thing, and perhaps I should start a new thread for it, but when I revised the calc. script for AmountReceiptNeeded (the new field I added for each Donation) to the following, it brought FileMaker to a standstill. My Mac's Activity Monitor showed FM using 98% of the CPU!

                       

                      Here's the formula:

                       

                      Case (

                       

                      IsEmpty ( _ID_receipt_f) &

                       

                      (

                         Globals::ShowCurrentYearOnly 

                         & 

                         Year (Donations::Date Received) = Year (Get ( CurrentDate ) )

                      );

                       

                      Amount;

                       

                      IsEmpty ( _ID_receipt_f)

                      &

                      Globals::ShowCurrentYearOnly 

                      ;

                       

                      "";

                       

                      IsEmpty ( _ID_receipt_f);

                       

                      Amount;

                       

                      ""

                       

                      )

                      I'm including a comparison with the global Boolean checkbox field "ShowCurrentYearOnly" and with the year of the receipt in question, with 3 comparisons:

                       

                      1. If no associated receipt AND ShowCurrentYearOnly is checked, AND year of this receipt is the current year, AmountReceiptNeeded gets set to the Amount.

                      2. If no associated receipt AND ShowCurrentYearOnly is checked, but last test must've failed if we made it to here, receipt must not be the current year, so AmountReceiptNeeded gets set to empty.

                      3. If no associated receipt and we made it here, ShowCurrentYearOnly must be unchecked, so AmountReceiptNeeded gets set to the Amount.

                      4. There must be an associated receipt, so AmountReceiptNeeded gets set to empty.

                       

                      Is that too complex a calculation for a few thousand records (8357 to be exact) in the Donations table? Or is there some other way I can do this kind of calculation or approach this problem? Having a single line in the calc field was no problem, but the above version has brought FM to its knees.

                       

                      All of this was my idea of how to show the correct total amount for each receipt in the portal, since that number is different if we're filtering out past years (or any other possible number of filtering options I may want to introduce). Maybe this isn't the way to do it...?

                       

                      Thanks!

                      adam

                      • 8. Re: Getting portal to include and exclude from related tables
                        keywords

                        A couple of things (OK, three):

                         

                        1.     In your calc you are using the wrong operator: "&" is simply a join; the operator you require if you want to specify a set of conditions is "and".

                        2.     You can make use of the Let ( ) function to greatly simplify and possible speed up your calc, eg:

                                       Let ( [

                                       noReceipt = IsEmpty ( _ID_receipt_f) ;

                                       current = Globals::ShowCurrentYearOnly = 1 ;

                                       thisYear = Year ( Donations::Date Received ) = Year ( Get ( CurrentDate ) ) ;

                                       receiptValue = Amount ;

                         

                                       completed = noReceipt and current and thisYear ;

                                       pastYear = noReceipt and current ] ;

                         

                                       Case ( completed ; receiptValue ; pastYear ; "" ; receiptValue ) )

                        3.     By way of comment, your calc delivers the same result for two of the three possibilities, which in my view is a weakness, because you cannot tell which test has delivered the result.

                        • 9. Re: Getting portal to include and exclude from related tables
                          erolst

                          '&' is the string concatenation operator that you have used incorrectly; you meant to use AND.

                           

                          You can express your calculation more concise as:

                           

                          Let ( [

                            currentOnly = Dashboard::currentYearOnly ;

                            isCurrent = Year ( dateReceived ) = Year ( Get ( CurrentDate ) )

                            ] ;

                            Case (

                              IsEmpty ( _fk_receiptID ) and ( ( currentOnly and isCurrent ) or not currentOnly ) ;

                              amount

                              )

                          )

                          Wrote this while keywords was posting his answer …

                           

                          Find attached an fp7 version of the earlier sample file; not sure if the portal filter calculation is exactly what you want/need, but it may give you some ideas.

                          • 10. Re: Getting portal to include and exclude from related tables
                            erolst

                            keywords wrote:

                            […]

                            Case ( completed ; receiptValue ; pastYear ; "" ; receiptValue )

                            […]

                            By way of comment, your calc delivers the same result for two of the three possibilities

                             

                            As does yours …

                            • 11. Re: Getting portal to include and exclude from related tables
                              keywords

                              I recognise that; I was making no attempt to guess what a third option might be.

                              • 12. Re: Getting portal to include and exclude from related tables
                                cosmocanuck

                                Thanks a million, erolst, for the new file!

                                 

                                For & vs. "and", it all comes from returning to FM after a fairly lengthy absence, to revise and improve this DB I created a few years ago! Clearly I'm still pretty rusty. Thanks for the heads-up, I really didn't think that little calc could grind FM to a halt!

                                 

                                As for the mysterious third option... what I'm trying to achieve is, I want AmountReceiptNeeded to show the amount of the donation (Amount), only if there's no receipt for it yet... but if I've filtered results to only show the current year, AND this donation's year doesn't qualify, I still want AmountReceiptNeeded to be empty. So only two options, I guess, not three. It seems I've still overdone my logic. I'll give it a rethink!

                                 

                                Actually, having absorbed your last entry, erolst, that appears to be what I'm looking for. Thanks!

                                 

                                I want to mark Mike's AND erolst's answers as Correct since they address the two different things I wound up asking about... but couldn't do that of course, sorry Mike but tremendous thanks to you too! I will mark this as solved since if I need further help I suppose it should be a new post at this point... many thanks to all of you.

                                • 13. Re: Getting portal to include and exclude from related tables
                                  cosmocanuck

                                  OK, I think I should keep this going in order to build on what has been contributed in this thread...

                                   

                                  Erolst, in digging into your example file, I realized after some serious head-scratching, that your dashboard portal works in part due to the "X" relationship between its table and the Donors table. (Example files are indeed wonderful things!) I'd thought I still had to test for the "Show Current Year Only" global variable, comparing it to the donation years, as part of the portal filter, but - presumably due to that global flag being part of the X relationship, I see that I don't. (You did have a note in the portal filter script wondering about that third condition of mine.) Indeed, I remove my "third condition" and the checkbox works just the same - perfectly! I guess the relationship as defined, "takes care of it".

                                   

                                  My own DB didn't work right until I connected my tables the same way.

                                   

                                  But i don't quite "get" why that is. Is it that, the X relationship only brings in records where the fields on both sides of the "X" evaluate to True? Since it's not about them having the same value...?

                                   

                                  If that's the case, I wonder about what I had intended to try to achieve next. Instead of a single checkbox for "Current Year Only", I had hoped to also offer more sophisticated search features to the users, like a couple of fields to specify a date range. But do I need to add an "X" relationship for any other such search field I add? And my guess above as to the guiding principle of "X", leaves me mystified as to how I'd set up those new relationships. Suppose I do have two date fields, a "greater than" and "lesser than". Those are in the Donations field, not the Donors field to which my portal is connecting. How do I have these also affect the display in the portal?

                                   

                                  Most of the examples of the X relationship online seem to use a "self join" as the example, i.e. viewing other records of the same table while on a particular record. But that's not what I'm doing...

                                   

                                  I can certainly work with the DB as it is now - my problem I started out with is basically addressed now, and thanks so much for everyone's help! But, naturally it leads to these further questions. Much appreciated if anyone has time for some further thoughts. Thanks!

                                  • 14. Re: Getting portal to include and exclude from related tables
                                    erolst

                                    Don't confuse those things:

                                     

                                    1. "x" is called the Cartesian operator and implements a relationship that shows all records from the other table; you can connect any two fields using "x". In ancient times, this was so often implement by using utility constants that FileMaker, Inc. made it an official relationship operator in FM7.

                                     

                                    This is the basis for the portal; show them all and let the filter sort 'em out …

                                     

                                    2. Check out how relationships work and the cumulative filtering effect for chained TOs the further you go down a chain of TOs.

                                     

                                    If you have

                                     

                                    Dashboard --< (x) Contacts --< (contactID) Donations

                                     

                                    you can still use a portal into Contacts with a filter that targets Donations, since the relationship from Contacts to Donations filters the Donations records by contactID and thus gives you the correct donation dates to filter against. (That's quite a mouthful …)

                                     

                                    Amend the portal filter with something like

                                     

                                    // other filters …

                                    and

                                    Let ( ds = Dashboard::dateStart ; Case ( ds ; ds ≤ Min ( Donations_forContactsFiltered::dateReceived ) ; True ) )

                                    and

                                    Let ( de = Dashboard::dateEnd ; Case ( de ; de ≥ Max ( Donations_forContactsFiltered::dateReceived ) ; True ) )

                                     

                                    which allows you to use the date filters selectively and separately. The thing with combining all those filters is to keep track of what impacts what.

                                     

                                    Note that using all those filters over a network and/or with many records could become reaaally slooow … consider pre-summarizing the data you want to filter on, by shooting them into the Contacts record each time you add or edit data (max date / min date / unreceipted amount etc.) for a contact, and use filters implemented on the relationship level. Since relationship predicates by definition filter against indexed data, this is usually a lot faster.

                                    1 2 Previous Next