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

    Turning two date fields into one search criteria.

    halsted

      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.
          Mike_Mitchell

          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.

           

          HTH

           

          Mike

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

            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.
              LyndsayHowarth

              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.
                halsted

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

                 

                Henrik