3 Replies Latest reply on Oct 28, 2015 5:59 AM by Mike_Mitchell

    Find values between




      I have a calculated field which correctly computes the age next birthday of a person. I want to allow the user to find those records where for example the age next birthday is between 50 and 70. I have assumed that the user has no knowledge of filemaker whatsoever, so he is guided by scripts.


      The first button the user presses activates a script that puts the user in FIND mode.


      The user can then enter say >50 to specify the first request,


      The user can then press a button to specify a new request.


      He can then enter <70 to specify the second criteria.


      He then presses a third button to perform the find.


      However the results returned are not correct.


      If the user enters only a single request e.g find those above 50 then I get the correct answer. However multiple requests do not seem to get me the correct answer.


      Can anybody help ? Thanks


      kind regards


        • 1. Re: Find values between

          The reason you're not getting the correct result is that multiple Find requests function as a logical OR. So you're finding anyone > 50 OR < 70 ... which is basically everyone. You need to put "50...70" instead in the field. This defines a range of desired values.


          As an aside, it might be easier to do this all for the user in the scripting, if the range is always 50 - 70. You can write a script like this:


          Enter Find Mode [ ]

          Set Field [ yourField ; "50...70" ]

          Set Error Capture [ On ]

          Perform Find

          Set Error Capture [ Off ]

          If [ Get ( FoundCount ) < 1 ]

               Show All Records

               Show Custom Dialog [ "Error" ; "No records found" ]

          End If


          If you really want to get fancy, you can put two global fields with low and high values and let the user choose. If you do that, some additional error trapping would be needed for a good user experience (make sure both values are populated, make sure the low value is lower than the high value, etc.).





          • 2. Re: Find values between

            Thanks Mike - it was the AND piece that stumped me.


            On a similar theme, if I have a date field for the date of birth, how do I find all those people with birthdays in november ? I've tried /11/ etc but don't get an answer. Thanks


            kind regards


            • 3. Re: Find values between

              Just put "11" (without the quotes) in the field. FileMaker will change it to 11/*/2015 (or whatever the current year is).