8 Replies Latest reply on Sep 10, 2009 1:05 PM by njoyce

    searching with dates



      searching with dates




       I have a database of studies and the date that they have been submitted (among other data points).  I want to create a report that has information on studies submitted within a specified time period. So, for example, I want the user to click on a button which then brings them to a search page and have them enter in the time frame.  Ideally, I would like it be so that they enter in a date and filemaker creates the report using studies submitted since this date, however, I have also created a time interval field (date_submitted - get(current date)), so that if that's not possible they can enter in a number of days (e.g. the past 90 days).  Is there a way to do this (either option?)




        • 1. Re: searching with dates

          One method: You can enter a date range while in find mode: 1/1/2009 ... 1/10/2009


          You can do this manually or with a script.


          Here's a scripted method I often use:


          Define two global date fields: gDate1 and gDate2

          I put them on my search layout and format them with calendar widgets for ease of use.


          I add a button to trigger the following script:


          Go To Layout [Select layout where you want the user to see results]

          Enter Find mode []

          Set Field [Yourtable :: yourdatefield ; Yourtable :: gDate1 & "..." & Yourtable :: gDate2 ]

          Set Error capture [on]

          Perform find []


          That's the basic idea that you can dress up with additional code as needed.

          1. You can count the number of found records and show a list if multiple records are found or go to a detail layout if just one is found
          2. You can check to see if gDate2 is empty and just enter gDate1 in that case so that the user can specify a single date by leaving the second field empty
          3. You can use custom dialog to tell the user that no records where found if the above script found zero records
          4. and so on...

          Edit note: updated set field expression to correct omitted table reference in calculated expression.

          • 2. Re: searching with dates

            Thanks so much for your rseponse.  I think though, that I'm not quite advanced enough to know exactly how to interpret it, so let me know if I have this correct (or where I'm getting confused):


            1. In the "main table" I created two global date fields (gdate1 and gdate2)

            2. I have a separate layout called "search", and I put the two global fields on this search layout

            3. I have a button on the primary layout associated with the "main table" that will run a script which searches between two global fields and returns a found set, and then goes to a report (performs the script associated with the report).


            so, when someone goes to the "search" layout and enters the dates and then clicks "search for studies between these two dates", the script will look like:

            Go To Layout [main table]

            Enter Find Mode

            (and this is where I get confused) Set field [main table;submission date:: gDate1 & "..." & gDate2

            Perform Find


                 In the "Set field" script step, what should I be entering into the calculation field?   when I try to enter the calculation above ( gDate1 & "..." & gDate2), filemaker tells me it cannot do it. I'm sure there is something I'm doing wrong, I'm just not quite sure what...


            • 3. Re: searching with dates

              I see a typo in my original script example


              In you case, you should enter:

              submission date::gDate1 & "..." & submission date::gDate2


              Each field needs a table reference and I left the second one out on my example script.

              • 4. Re: searching with dates

                I'm sorry, I still can't seem to get this to work. 


                I have a page with the two global date fields.  The person enters a date into each and then presses a button.  the button has this script:


                go to layout ["Main IRB page" (main IRB page)]

                enter find mode []

                set field [main irb page: submission date; main IRB page::gdate1 & "..." & main IRB page:: gdate2]

                perform find[]

                perform script [studies submitted with time period]


                submission date is the date that I want to search on (e.g. i want the submission date to be between gdate1 and gdate2.  Am I missing something?  thanks again so much for all your help.

                • 5. Re: searching with dates
                     Are you sure that submission date is really a date field instead of a text field?
                  • 6. Re: searching with dates
                       yeah, it is...
                    • 7. Re: searching with dates
                         sorry, to be clear, yes, it is a date field
                      • 8. Re: searching with dates

                        i got it!  thank you! I made a ridiculous  mistake of forgetting to make sure that they were both global field (i had turned that part off initially when it wasn't working hoping to see if it changed).


                        thank you so much!