2 Replies Latest reply on Sep 17, 2012 4:06 PM by TuxfordC

    Date related Find problem.



      Date related Find problem.



                               I thought that I had solved this, but it has come back to haunt me.  I have a simple Table, recording donations to a Church.  The three relevant fields are;  ID a six figure number;  Amount_Given in $ and decimals;   Date in DD/MM/YYYY format.  The Table can record giving over several years and my need is to extract totals for individuals to claim Tax exemptions to their donations.   I set up a script with  Find  Amount_Given:[>0] ,  and Omit records where the Date fell outside the Tax period.  This appeared to work well until I found that anyone with a date outside to the Tax period was being Omitted completely.   As soon as the program found these non-complying dates, all of their records - including the ones that did fall inside the Tax year - were deleted from the Found Set.

              The Record contains a field for Total_Given and File maker was correctly relating this to the ID's and totalling the giving each person (ID) but as soon as  Filemaker found a date that was outside the specified period, it omitted everything related to that ID.   I guess that my logic is wrong, but how to fix it?

           Tuxford C.


        • 1. Re: Date related Find problem.

               Do you have two tables here, one for the donors and one for the donations?

               I'd guess that you do and that you are performing a find on a layout based on donors instead of a layout based on donations as that would match the results that you are getting. When you specify find criteria in the fields of a related table, you are telling filemaker to find (or omit) records in the layout's table (donor) if at least one record in the related table (Donations) matches that criteria.

               You shouldn't encounter this problem if you perform the find on the donations based layout and once you have a set of records found on that layout, you can sort them by donor ID to group them and compute a total for each donor. It's even possible to set up such a layout so that each donor is listed on a single row with just the total listed if you do not wish to list each of their individual donations.

          • 2. Re: Date related Find problem.
            Yes, there are two tables. The donors table which is relatively large, and a much smaller one for donations. They are related by the donor ID number. The receipt layout and the Find are based on the donations table and only the donor's name from the other table is used in printing of the receipt. I will rebuild the script so that donor sorting is done after we have found the date specified records and work from there.  There have been problems with the 'Year' Function which I tried using to simplify the script.  The beginning and end of the fiscal year are fields in a separate documents table (related to the donor table) which holds data that is rarely changed. 'Year' did not produce anything from calculations using date fields from the documents table. Will report progress in a couple of days. TuxfordC.