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:
students::rank = 1 ; "White" ;
students::rank = 2 ; "Yellow" ;
students::rank = 3 ; "Green" ;
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.
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.
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.
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.
I'm missing something here: how would the auto-enter be evaluated in find mode?
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.
OK but then how does it solve the problem since he wants to search for
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.
JudoBelts.fmp12.zip 67.1 K
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.
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.
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!