4 Replies Latest reply on Mar 26, 2010 1:41 PM by ninja

    Finding closest value.



      Finding closest value.


      I am a beginner using Filemaker Pro 8.5 on Mac OSX 10.5.8.  I have been tasked with creating a database for my companies interns, volunteers and freelance workers.  My boss requested that he can find not only specific values but values closest to the value searched.  For example, lets say that he wants to find out what we have paid a freelancer in the past for a certain tast, and I have a field that lists how much we have paid freelancers in the past.  He is willing to hire someone for $50 dollars so he performs a find in the wage field but we have never hired someone who works for exactly $50 dollars.  Is there a way to perform a search where the results would list values closest to $50 for example $49, $42, $35 dollars from closest value to furthest from the specified target? 

        • 1. Re: Finding closest value.



          So if I read you right, you want a found set of ALL the records, sorted from closest to farthest.


          Create a number field for him to put his $$ search value in..."Search"

          You already have the field of what you paid people "Fee"

          Create a third field call "Difference" which is a calc field of:   ABS( Search-Fee)


          Then sort the records (probably is a list view would be best) by the "Difference" field Ascending.  Have the freelancer name and Fee fields shown on the layout.


          Is that what you're after?

          • 2. Re: Finding closest value.

            Here's one approach:


            In Find mode, enter the range of values you're interested in (e.g. 30...50).  Perform the find and then sort the found set.

            • 3. Re: Finding closest value.

              This works, now I'm struggling to add more fields into the list view, as of now there are only four fields and I can't figure out how to add more fields including "Difference".  Besides that it works fine, thank you.

              • 4. Re: Finding closest value.

                Just Drag 'em on using the field tool in Layout mode.

                Or copy and paste the ones that are there (in layout mode) then double click the new ones to change thier designation.


                Fitting them is often the bigger challenge...remember that your line hieght can be increased to fit two rows if you need them.  Smaller fonts can help too, up to a point of readability.