1 Reply Latest reply on Nov 11, 2011 3:36 PM by philmodjunk

    Beginner Question on Find



      Beginner Question on Find


      Context:  I have a table of Donors, and a table of Gifts.  

      The Donors table has the typical address list content,  the Gifts table includes Gift_Amount and Gift_Date.  The two are related by a simple "id_no."

      It's easy enough to design a report with Donor's and their gifts, but I need to print a report that includes only those donors who have donated between a start_date and end_date.

      I've been able to gather the start_date and end_date via a dialog box and assign them as global variables, and I've even been able to create a portal that filters the gifts based on that date constraint.   I've used a summary field to total just those gifts that meet the date constraints.

      However, I've solved the wrong problem - I am displaying gifts that meet the date constraints for the current Donor, but what I want to do is find Donors based on whether or not there are one or more gift records between the start and end date.  

      Perhaps a simple way to state it, I need to find all the records in Table 1 which have related records in Table 2 that satisfy a simple "greater than this but less than that" constraint.

      Thank you in advance

        • 1. Re: Beginner Question on Find

          When you perfrom a find on a layout based on Table 1, but specify criteria in fields from the related table 2, you will get the records in table 1 that have related records meeting your specified criteria--which is what you want here.

          Enter find mode on your donors table.

          Put Date1...Date2 as criteria into the Gifts::Gift_Date field.

          Perform the find.

          A script for this might look like this:

          Enter find mode [] //clear the pause check box
          Set Field [Gifts::Gift_date ; $$start_date & "..." & $$end_date ]
          Set Error Capture [on]
          Perform find []

          $$start_date and $$end_date would be the global variables with the dates set from your dialog box. You'd need to do this before performing the steps of the above script. You can also use global fields in place of global variables in this script.