8 Replies Latest reply on May 23, 2012 3:37 AM by microbatteryshop

    search between 2 values



      search between 2 values


      hi, first i am completely new to filemaker. And staring to learn with each step. But this i cannot solve.

      lets say i have a  table  as follows

      result / minimum value / maximum value / system

      A123 / 0 / 5 / NIMH

      A47845 / 5 / 10 / NICD

      a1684 / 5 / 10 / NIMH

      A984 / 10 / 20 / NIMH

      A5218 / 20 / 30 / NIMH

      I have a field in a layout where i write "9 "and another "NIMH"

      in the 3th field   I need the result.

      so a search looks for the value 9 ( what is between 5 and 10)

      and it must be NIMH. the result a1684 is written in my 3th field.

      Ive looked everywhere for a solution as GET or Find or LOOP. And after 8 hours i give up. even youtube is not working

      Is theire someone who can help me Laughing please

        • 1. Re: search between 2 values

          This can be done with a self join relationship or with a scripted Find. Either can work. The best option may depend on what you want to do with the result after you have entered this data and gotten your result.

          If you just need to see the result, use the self join relationship:

          Let's call your table "Values". In Manage | Database | relationships, make a new table occurrence of values by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as SelectedValue.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          Values::gSystem = SelectedValue::System AND
          Values::gValue > SelectedValue::minimum value AND
          Values::gvalue < SelectedValue::Maximum value

          If you define 2 fields, gSystem and gValue--which can be given global storage in field options, you can put them on a layout and place the Result field from Selectedvalue next to them. Then, when you select a system and enter a value, this field will automatically display the desired result.

          If you want to find and work with/view the actual record, use a script that performs a find. Define the same two fields and this time they must have global storage specified in order for this script to work:

          Enter Find Mode [] //clear the pause check box
          Set Field [values::System ; Values::gSystem ]
          Set Field [Values::Minimum value ; "<" & values::gValue ]
          Set Field [Values::Maximum value ; ">" & values::gValue ]
          Set Error capture [on]
          Perform Find []
          IF [ Not Get ( FoundCount ) ]
             Show custom dialog ["This value does not fall on the range of an existing record for the specified system."]
          ENd If

          • 2. Re: search between 2 values

            thank you very much Phil,

            Will these also work when used in filemaker go on an ipad?

            I tried it today. i duplicated the table and called it values, i made the 2 new fields as global storage.

            and i made a layout for the table( all in) with a button to activate the script( option2) 

            what happens: when i hit the button the value gSystem and gValue are in the fields and all the rest have a search icon and i must hit search on the tool bar.

            when i do that it tells me no records are found. However you can see in the pictures it is in the table.

            why do i need to hit the search in the tool bar? And why does it not found the line?

            i had something simular with my own search funstion. When i use the quick dsearch it works.

            but the idea is that my wife enters the values and auromaticly the numbers is written in the other table.

            thanks for looking at this


            • 3. Re: search between 2 values

              here is the table

              • 4. Re: search between 2 values

                As far as I know it will work the same in FM Go as it does in FileMaker. I'd be very suprised if it doesn't.

                It sounds like you have this step in your script:

                Enter Find mode [pause]

                instead of this step:

                Enter Find Mode []

                To get this step, you have to clear the pause check box like I indicated in my previous post.

                That eliminates the system requiring to to click a button in the status area but doesn't address why it is not finding records.

                Possible issues to look at:

                Do the two search fields really have global storage? This must be specified by you in the Field options dialog that appears when you double click the field in Manage | Database | Fields (Click the storage tab.)

                Do you have the correct field types?

                If "bebat nr" is your system field, make sure it is of type text. Make sure that your min and max fields are of type number.

                Did you accidentally use the wrong inequality operators in the search criteria?

                If you are searching for a value where the min is less than 5 and the max is greater than 5, you won't find any records.

                • 5. Re: search between 2 values

                  Laughing YES ITS WORKING ( after 3 days)

                  Thanks for the help phil.

                  My problem was a wrong translation. the "Perform Find []" is called "Zoekopdracht uitvoeren [] " in my dutch language.

                  I took the wrong one, so instead of activating the search, it activated the search bar.

                  Many, many thanksLaughing

                  • 6. Re: search between 2 values

                    sorry phil i have to ask info again.

                    it works nice, but what i did:

                    in (window1 )table one i use a button to make a new window, that opens the layout from the "value" table, than i do your function and at the end i set the result ( by set field) into my first window( table). I close the sript and the window( with the value table).

                    result: in window( table 1) i have my result from the search. perfect.Laughing

                    HOWEVER when i make a new record in table ( window1) it does not work any more. it only works with the first record in the first table.

                    in table1 i used id_bebat ( just indexed) and made a relation with ID_bebat ( indexed and generating an ID number)

                     i've changed the Values::gValue and so with $gValue because i was thinking it is the global storage that blockes the system. But i have the same result.

                    • 7. Re: search between 2 values

                      New window will complicate things a bit. Can you post your actual script? I realize that I may not be able to read it due to being in dutch, but I'm curious to see exactly how you implemented the new window step into the original concept--particularly the part where you use set field to update a record in the original window.

                      If you use the script, you do not actually need any relationship between the two tables for the script to work.

                      • 8. Re: search between 2 values

                        This was my solution and it works perfectly. ( it could help others if needed)

                        I've  copied the field values form my first layout into variables  , changed to the layout with the correct data table.

                        Copied the variables into the correct fields, and started the search script above mentioned.
                        than i copied the result again in variables, changed to the original design and copied it again in my result field.
                         and yes it works perfectly Laughing