11 Replies Latest reply on Mar 4, 2016 12:13 PM by dtcgnet

    Differing Find Results


      This seems like a weird glitch - am I doing something wrong?  When I do this saved find:

      Screen Shot 2016-03-01 at 12.21.22 PM.png


      . . . I get 105 donors.  When I look up the same criteria a different way I get 108 donors.  The second way is step by step: find for MailingList=Yes, then find for GiftDate>1/13/2015 and constrain set, then find for GiftDate<1/13/2016 and  constrain set again.  For some reason when I do the finds this second way and sort on last name, I get 108 donors, including 3 donors who didn't give in the range at all:


      Screen Shot 2016-03-01 at 12.27.22 PM.pngScreen Shot 2016-03-01 at 12.28.08 PM.pngScreen Shot 2016-03-01 at 12.27.49 PM.png

      Shouldn't both of those ways to find that date range achieve the same results?

        • 1. Re: Differing Find Results

          your constrain searches use ">"  and "<" , while your range search includes the start and end date.

          • 2. Re: Differing Find Results

            I agree with siplus. Method 2 will eliminate anyone whose gift date was on 1/13/2015 or on 1/13/2016. In Find mode, you could just enter 1/13/2015...1/13/2016 instead of >1/13/2015 and <1/13/2016.


            Also, just to be precise, your range covers one year and one day. If you want it for just one year, adjust it.

            • 3. Re: Differing Find Results

              Doing Finds on related records in a portal can be problematic. I'd recommend doing your Find in the Gifts table and then if needed go from that found set to the related Mailing List records.

              • 4. Re: Differing Find Results

                Thank you for all 3 answers.  I will fix my dates to be an exact year, but that isn't the issue in this case because the 3 oddball records that show up aren't even close to the beginning or end dates (see green screenshots above).


                In response to Tom Fitch's answer, I tried changing the find order (first GiftDate finds, then MailingList=Yes find) but that still included the 3 oddball records. Next I tried the GiftDate finds not via the portal since you said its problematic, but I ran into 2 issues.  First I can't do all my finds in the Gifts table, because the MailingList field isn't in there (and I'd rather not add it because any future corrections would then need to be made in two places).  The second issue is that I think I'm not a typical FileMaker user in that I only deal w/ my one database, so sometimes I find big gaps in my FM knowledge. My understanding is that the Gifts table is kind of an adjunct (which I added to make my database relational) to my main database file, not a file in its own right. Is that correct?


                It seems like the bottom line is that I just have to remember never to do finds via a portal (although my saved find which works well is also through the portal so that doesn't really make sense either). This is why I'm not a fan of FileMaker.  It can do so much, but requires background knowledge (for example that certain reports only work in Preview - I wasted a lot of time once trying to troubleshoot a layout that was fine, but I was in Browse). For a user like me who only needs to perform certain tasks once a year, I need to write up lots of notes to capture that knowledge once I've slogged through it the first time.


                But back to the issue at hand - I can use my saved find and get accurate results which is good.  What is bad is that I have lost confidence in my future finds being correct. Not sure there's an answer to that, alas. 


                Thanks again for your help.

                • 5. Re: Differing Find Results

                  Try one find:


                  Click in UCM Mailing List::Mailing List and enter Yes.

                  Click in Gifts::Gift Date and enter 1/13/2015...1/12/2016.


                  Perform find.


                  One thing to consider: What is going on is that you've got some people who have provided more than one gift, therefore more than one Gifts record is related to some of your donors. When you found mailing list = yes, you found all people who'd ever given. Your second find constrained that to all donors who'd given after 1/13/2015. Then you reversed that and provided only one criteria, < 1/13/2016 and constrained. Try what I've outlined above and see how it works.

                  • 6. Re: Differing Find Results

                    Thanks for answering.  Logically, if I constrained all 3 of my finds (which I did) it seems like none of my finds would be reversed, so I should get the same result as the range find.  I didn't though.


                    Your answer is helpful however because I learned something - I hadn't worked w/ the range find before and wasn't successful in using it because I had thought there was a space in between each of the periods.  Using the range find now I do get the correct result.  Guess I just have to get over being bothered that the 3 step search doesn't work even though logically it should!

                    • 7. Re: Differing Find Results

                      Actually, the range find and your 3 step find are not the same logically. The range find asks for all donors that have mailing_list=Yes AND donation_date from 1/13/2015 through 1/13/2016 inclusive. This checks for Mailing_ list=Yes and the date of EACH donation to be in the range. When you do the 3 step find/constrain you are asking for 1) anyone on the mailing list; 2) anyone with ANY donation > date; 3) anyone with ANY donation < date. So the result of that includes all those who gave both before AND after your desired date range. This is what happens when finding via a portal in that all child records of the parent are compared.

                      • 8. Re: Differing Find Results

                        It can be a very fine distinction, but, it's important.


                        Example: 3 people are on the mailing list (Mailing List = "Yes")

                        We want to know about donations in 2015.

                        Person 1 made no donations in 2015, but is on the list.

                        Person 2 made two donations in 2015, and is on the list.

                        Person 3 made 1 donation in 2015, and is on the list.


                        It is true that 3 donations were made in 2015 by people on the list.

                        It is true that 2 donors on the list made donations in 2015.


                        If you did the find from the context of the donor list, you'd find 2 records, since the number of records returned is the number of donors.

                        If you did the find from the context of the gift list, you'd find 3 records, since 3 gifts in 2015 were made by people on the list.

                        • 9. Re: Differing Find Results

                          What's weird about that bdb0001 is that it doesn't do that - (unless I'm misunderstanding something). If it showed all gifts before and after when using the 3 step find/constrain, it would include 19 after the date range I want, and hundreds before.  For some reason there are just 3 that show up, and their dates are within a narrow range (see screenshots above) 2/22/2016, 2/23/2016, and 2/25/2016. (Was Mercury in retrograde that week? just kidding). Is it possible that something was different February 22 - 25 in how I was entering data? Some setting off or in some other mode or something? 


                          And isn't the 3 step find/constrain still the same logic as long as I'm constraining the last 2 finds?  In theory, I'm only looking from within the group already selected out, right?


                          Here is the screen where I enter data, in case that gives any clues.  The top part of the green Gifts tab is a portal to the Gifts table.  The rest of the info in in the MailingList table.


                          Screen Shot 2016-03-04 at 12.59.35 PM.png

                          • 10. Re: Differing Find Results

                            dtcgnet - see my reply above.  Your explanation is very clear, thanks, but the numbers don't work.  Some donors have given every year so more than just those 3 would show up if that was the problem.

                            • 11. Re: Differing Find Results

                              I think what's going on is...


                              Your first find finds all "Mailing List" = "Yes". You are looking at Donors, so Donors who match "Yes" are found.


                              When you enter your second find and say "constrain", the found set will either decrease or remain the same. The found count cannot increase. The effect of your second find is to say, "Of these people I'm looking at right now, please omit people who have made NO gifts after 1/13/2015."


                              After the donors who haven't made ANY gifts after 1/13/2015 were removed, you then asked, "Ok. Now, of these donors, please omit people who have made NO gifts before 1/13/2016."


                              Take one of your three donors that seemed odd, #2344.

                              1. Is he a yes? Yes.

                              2. Did he make ANY gift after 1/13/2015? Yes, so he isn't omitted.

                              3. Did he make ANY gift before 1/13/2016? Yes, so he isn't omitted.


                              That's why the range works where the multiple finds didn't. For #2344:

                              1. Is he a yes? Yes.

                              2. Did he make a gift of some sort between 1/13/2015 and 1/13/2016? No, so he is omitted.