12 Replies Latest reply on Mar 15, 2016 10:05 AM by danhmill

    Finding a range within ordered text value list

    danhmill

      I have a value list of martial art belt ranks. As you can imagine the ranks are hierarchical with a specified order. For example. First belt = white, Second belt = yellow, Third belt = green, etc. I would like my end users to be able to search a range of these belts using my "rank field" and common FM search operators (e.g. find White Belt...Green Belt). I've searched high and low and must not have the correct terminology to find a solution. Any help appreciated.

        • 1. Re: Finding a range within ordered text value list
          mikebeargie

          create a second field that auto-enters the word value based on the rank number. Then allow users to switch on that.

           

          Should be a simple auto-enter, like this:

          Case(

            students::rank = 1 ; "White" ;

            students::rank = 2 ; "Yellow" ;

            students::rank = 3  ; "Green" ; 

            etc...

          "")

          Uncheck the box under auto-enter for "replace if contents already exist", that way when you change the rank number, it will change the auto-enter here as well.

          • 2. Re: Finding a range within ordered text value list
            FabriceNordmann

            You typically have a key / value pair here, so I think the easiest way, if you don't want fancy scripting and triggers is :

            - create a table with 2 fields : Key (number) and Value (text)

            - create a value list using the two fields in this order, but showing only the second field.

            - create records like 1 / White belt, 2 / Yellow belt…

            - in your existing records of your table, replace each existing value with its corresponding key.

            - in layout mode, use a local menu instead of a dropdown list, that's the only control that will display only the value and not the key.

            You're set.

            • 3. Re: Finding a range within ordered text value list
              danhmill

              Thanks gentleman....appreciate the help. 1-more-thing I don't mind fancy scripting so feel free if you have another approach. For clarity with either suggestion from Mike or 1-More...could the end user then search using the text value as the range? What I want to avoid is the end user having to equate a number to the rank. For instance my end users know that black belt follows red belt but may not intuitively know that this scenario equates to "belt #12" following "belt #11". Would your solutions change with that understanding? Thanks again!

               

              edit: In example above user would search for records by typing "Red Belt...Black Belt" in the available rank search field.

              • 4. Re: Finding a range within ordered text value list
                mikebeargie

                My solution would, because you would have two separate fields, one with a number, and another with the textual representation of that. You can then search or even quickfind on the text field OR the number value.

                • 5. Re: Finding a range within ordered text value list
                  FabriceNordmann

                  I'm missing something here: how would the auto-enter be evaluated in find mode?

                  • 6. Re: Finding a range within ordered text value list
                    mikebeargie

                    the auto-enter value has nothing to do with find or browse mode. It's just a value that gets automatically entered into a field whenever the numeric value is changed. It's a stored value, just like if someone typed in "white" or "yellow".

                     

                    Thus, it's available to search in find mode just like any other normal field.

                    • 7. Re: Finding a range within ordered text value list
                      FabriceNordmann

                      OK but then how does it solve the problem since he wants to search for

                      Green…Blue

                      and not

                      4…6

                      ?

                      • 8. Re: Finding a range within ordered text value list
                        FabriceNordmann

                        Fancy scripting then

                        Here is a sample file. The problem is it would be nothing more if the script trigger onModeExit would work as expected in find mode (perform the script before the find). Unfortunately, it never worked.

                        Which means you have to perform the find by script and prevent other ways using custom menus.

                        • 9. Re: Finding a range within ordered text value list
                          mikebeargie

                          Yeah, it's impossible to really search text data by range natively, you need a trigger on that field that only works in find mode to copy the value back as a number to your other field.

                           

                          Or you could create search fields that have a two-part value list, where a number is selected but it shows the text value to a user. You would need to create a reference table of belts though with a record of each number/text combo.

                          • 10. Re: Finding a range within ordered text value list

                            This is an interface issue I think.

                             

                            As a application developer, it's a good idea to REMOVE the interface internals, or understanding how FM, in this case, works, from users and handle these selections yourself -- behind the scenes. Give users an idiot-proof way to ask for things or do searches. Never let a user type in directly to a field in Find Mode, for example.

                             

                            To wit, since you can't rely on users correctly typing in a Find command without possibly misspelling belts and such, how about an interface where they just click the belts they want and the list, in order updates automatically?

                             

                            The interface example above isn't meant to be "polished", but it demonstrates that the user doesn't have to know (and should be expected to know) anything about FileMaker "Finds".

                             

                            The internals of a program should never "bleed" into the interface: it should always be abstracted.

                             

                            Users appreciate automation - even if they don't know all the work you did to make it happen for them.

                             

                            -- m

                            • 11. Re: Finding a range within ordered text value list

                              Quick update to my post above...I guess the correct logic should be that if they click any combination of belts, it needs to include all the belts between the first and the last?


                              Hope this is helpful in some way.

                               

                              - m

                               

                              • 12. Re: Finding a range within ordered text value list
                                danhmill

                                Thanks everyone for the help and suggestions. I'm likely to pursue a combined attack for my solution using bits of several suggestions. While I'm busy tackling other system features I will report back if/when I resolve the issue. Thanks!