10 Replies Latest reply on Mar 31, 2016 5:14 AM by RPeters

    Can a Calculation in a Parent Table Pull Info from Child Records??

    RPeters

      Hi Everyone,

       

      I work for a non-profit music festival, and I’m having some difficulty with a calculation.  I’m keeping track of donations by individuals.  Each individual has a "Status" which is a case calculation showing a different calculation result based on when they last donated.  I’m looking for a group of individuals who have donated money this year.  They would be considered a "2016 Season Sponsor".

       

      This is the relationship I have to keep track of incoming money: Patrons--->Income

       

      If someone has donated money this year, I'd like their status field to read "Current Season Sponsor".  I have a larger case because there's many different statuses a person could have, but here's the little tidbit of the case that isn't working:

       

      Income::Sponsor Reason = "2016 Season Sponsor";

      "Current Season Sponsor";

       

      This only works if the first entry in the Income list for a particular person is their 2016 Season Sponsor donation.  But we keep track of all the money coming in.  Some people bought concert tickets, or went to a fundraiser, and then made their season donation.  If this is the case, then their status doesn't read as it should.  It seems that Filemaker checks the first child record only, rather than seeing if any of the child records reads "2016 Season Sponsor".

       

      I hope this makes sense.  I know why it's not working, I just don't know how to fix it.  Ultimately, I guess what I'm wanting the calculation to do is search through all the Patron's child records in "Income" to see if there's any entries that read "2016 Season Sponsor".  If there is, then the Status field should read "Current Season Sponsor".  I know what I want, I just don't know if it's possible, especially for a beginner like myself!

       

      Any suggestions would be greatly appreciated!!

       

      -Rachael

        • 1. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
          coherentkris

          because you provided a tidbit of the failing case then here is a tidbit of an answer...

          You posed "Can a calculation in a parent table pull info from child records?

          The answer is yes.

          My guess is that you don't want an answer to your question but a solution to fix the case statement that is failing.

          To do that without guessing we will need more than a tidbit.

           

          ps. my guess would be that a calc in the parent will only pull the first record data from a child records across the relationship.

          • 2. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
            RPeters

            That is fair enough! I presumed the whole case didn't need to get posted since I know the rest of it works.  But nevertheless, here's what it looks like.

             

            Also, just as an fyi: our company is new to FMP this year.  We had Bento in the past, and all that was kept track of was a person's donation (ex. "Donation 2015") and this info was stored in the Patron table.  So I have all these old Bento fields of donation history still in the Patron table that I'm using in this calculation.  This year things are different since I can keep track of a patron's full involvement with our company (concert ticket purchases etc.) hence the child records that make things a bit more complicated in my mind.  But I have high hopes that it'll work out!

             

            Okay, here's the case:

             

            Case (

             

             

            Income::Sponsor Reason = "2016 Season Sponsor";

            "Current Season Sponsor";

             

             

            Donation 2015  ≥  500

            and

            (

            Donation 2014 ≥ 500 or

            Donation 2013 ≥ 500 or

            Donation 2012 ≥ 500 or

            Donation 2011 ≥ 500 or

            Donation 2010 ≥ 500 or

            Donation 2009 ≥ 500 or

            Donation 2008 ≥ 500 or

            Donation 2007 ≥ 500

            );

            "Donated in 2015, has donated in the past";

             

             

            Donation 2014   ≥ 500

            and

            Donation 2015 = 0 ;

            "Lapsed in 2015" ;

             

             

            Donation 2013  ≥ 500

            and

            Donation 2014  < Donation 2013

            and  not IsEmpty ( Donation 2014 ) ;

            "Lowered contribution in 2014" ;

             

             

            Donation 2013  ≥ 500

            and

            Donation 2014 = 0 ;

            "Lapsed in 2014" ;

             

             

            Donation 2015  ≥ 500

            and

            Donation 2007 < 500

            and

            Donation 2008 < 500

            and

            Donation 2009 < 500

            and

            Donation 2010 < 500

            and

            Donation 2011 < 500

            and

            Donation 2012 < 500

            and

            Donation 2013 < 500

            and

            Donation 2014 < 500;

            "New in 2015" ;

             

            "" )

            • 3. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
              fitch

              One way to do this is first get a list of all related Income:

              List( Income::Sponsor Reason )

               

              Then you can test the list for what you're looking for using FilterValues:

              FilterValues( List( Income::Sponsor Reason ) ; "2016 Season Sponsor" )

               

              This should give you the desired result. Do they make more than one season donation? If so you could use GetValue so you just get a single line -- FilterValues will give you a line for each Income record that matches.

              • 4. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                RPeters

                Thanks Tom!

                 

                I've never used "List" before, so my knowledge is suuuuper limited!  Here's what I typed in, but I'm missing something for sure!

                 

                List ( Income::Sponsor Reason )

                FilterValues ( List ( Income::Sponsor Reason ));

                "2016 Season Sponsor";

                • 5. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                  erolst

                  Tom Fitch wrote:

                  This should give you the desired result. Do they make more than one season donation? If so you could use GetValue so you just get a single line -- FilterValues will give you a line for each Income record that matches.


                  You just want to check if such a string** exists; so


                  not IsEmpty ( FilterValues ( List ( Income::Sponsor Reason ) ; "2016 Season Sponsor" ) )

                   

                  should do the trick.

                   

                  ** Edit: … whether such a line exists; FilterValues() only works with complete lines (value list elements), not sub-strings – so "2016 Season Sponsor Deluxe" would be ignored. (Tom, this is for Rachael; I know you know all this … ;-)

                   

                  Rachael –

                   

                  you are correct in that a simple field reference only reads the value from the first related record. Seeing if Filter()ing out a given value out of a List() of field values yields a result is a common method, so

                   

                  Case (

                    not IsEmpty ( FilterValues ( List ( Income::Sponsor Reason ) ; "2016 Season Sponsor" ) ) ;

                    "Current Season Sponsor"

                  )

                   

                  You could also do this in a generic way:

                   

                  Let (

                  theString = Year ( Get ( CurrentDate ) ) & " Season Sponsor" ;

                    Case (

                      not IsEmpty ( FilterValues ( List ( Income::Sponsor Reason ) ; "theString ) ) ;

                      "Current Season Sponsor"

                     )

                  )

                  • 6. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                    RPeters

                    Oooh!  I tried this one and it worked!!

                     

                    Case (

                      not IsEmpty ( FilterValues ( List ( Income::Sponsor Reason ) ; "2016 Season Sponsor" ) ) ;

                      "Current Season Sponsor"

                    )

                     

                    Thank you both!!  I don't really understand why it works, but that'll be a question for when we're in the off-season and I have time to really delve in!  (FilterValues and Lists are both totally new to me!)

                     

                    Anyways, thanks again to you both!  The fundraising committee is going to looooove me!

                    • 7. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                      erolst

                      RPeters wrote:

                      I don't really understand why it works

                       

                      Short version: if your related records have values in the relevant field like “Ticket”, "Fundraiser", "2016 Season Sponsor", then List() gives you:

                       

                      Ticket

                      Fundraiser

                      2016 Season Sponsor

                       

                      If FilterValues() finds any complete line that matches the desired value (regardless of position within the list), all these matching lines will be returned, and the result is “not empty” – which is what the Case() checks on.

                       

                      RPeters wrote:

                      The fundraising committee is going to looooove me!

                       

                      Here's hoping you're raising funds for what I consider a good cause …

                      • 8. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                        fitch

                        I guess I didn't explain very well. I was trying to show step by step how to think about and build the calculation. You weren't meant to use both calculations -- the second one was building on the first one.

                         

                        The reason I didn't add "not IsEmpty(  )" to my suggestion is that I forgot you wanted Current Season Sponsor as the result -- I was thinking you wanted 2016 Season Sponsor. Sorry about that.

                         

                        At any rate, if you're interested in learning why your first attempt didn't succeed, please compare your response to my last post with my concluding calculation in that post. They don't match.

                        • 9. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                          RPeters

                          I get it!!  Thanks for putting in newbie-words!

                          • 10. Re: Can a Calculation in a Parent Table Pull Info from Child Records??
                            RPeters

                            No problem Tom!  I really appreciate you taking the time to help me out!!   Thanks again!!