4 Replies Latest reply on Jun 25, 2012 7:05 AM by halsted

    Turning two date fields into one search criteria.


      Hi hope somebody can help, a rather unskilled filemaker guy.


      I’m a user of Filemaker and have been for more than 10 years.

      A mainly use FM for looking up business related information and for making rapports.

      I do not have any programming experience, but I’m rather curious.


      Hope somebody here can help me out with a problem I have been struggling with.


      I’m managing a sales organization, and want to publish our sales, to my local Account managers in a filemaker go Ipad database. Non of our AM have any experience with FM, so a fairly simple layout, mainly based on buttons will be necessary.


      What I want is, hit a button that takes you to a search layout.

      On that layout I want two date fields with calendar popup, so the salesman can press two dates that will bee turned in to the search criteria eg. 01-01-201231-12-2012 that can be used in find mode to search for sales in a specific period.


      I have been thinking ‘’global fields’’ but I can figure out how to make a script that can copy the two date from the two data fields and turn it into a search criteria. Please help.

        • 1. Re: Turning two date fields into one search criteria.

          Hi, Halsted.


          You can do this with a script that captures the two date fields. The date fields should be, as you suggest, global date fields. Then you can give the user a button to click that executes a script that does these things:


          1) Make sure that date1 is earlier or equal to date2. (This will prevent search errors.)

          2) Capture the date range in a variable using a calculation like this:


          $searchDate = GetAsText ( date1 ) & "..." & GetAsText ( date2 )


          3) Enter Find Mode.

          4) Set the desired date field equal to $searchDate.





          • 2. Re: Turning two date fields into one search criteria.

            Globals is correct.  Date1_global and Date2_global


            Script could be,


            Enter find mode

            Set field [ DATE: GetAsText(Date1_global) &"..." & GetAsText (Date2_global)]

            Perform find


            What is important is that you are setting your searched date field as a text string.


            Pardon my poor formatting, I am writing on an iPad

            1 of 1 people found this helpful
            • 3. Re: Turning two date fields into one search criteria.

              Hi Halsted,


              Mike has answered the question you asked... I just thought I'd throw in another option.


              Rather than finding dates in a range... you could create a relationship where date1 ≥ date and date2 ≤ date.... where date1 and date2 are global fields where they can just choose the date from the calendar popup. This would eliminate the need for a script at all if executed properly.


              That's my 2.0006 ¢ worth...


              - Lyndsay

              1 of 1 people found this helpful
              • 4. Re: Turning two date fields into one search criteria.

                Thanks a lot, your really made my day - it works perfect