step one: verify that the field is number.
if the field is assigned as "text"
then it's a range (for humans), but not as FileMaker can interpret.
if the field is broken into two:
start = 1
end = 5
then they are searchable, can be shown "visually" as
start & "..." & end
on reports and records
step two: determine a way to break the value into two parts
If the separator is " - " or "-" or "..." or ??, then how you divide the parts will need to be changed. If you were consistent in getting the data as similar patterns, then dividing will be easier.
step three: once separated, the two values can be used in relationship, finds, etc. in a more efficient way that are real numbers and not text representations of a range of numbers.
I don't know how you'd actually implement this, but it sounds like you want something like this:
inv >= 21 and inv <= 99999; "me";
inv >= 100000 and inv <= 299999; "her";
see OP for a clue that this is TEXT. once divided out, then a Case() statement could work.
Step 1. Make sure you can do this by hand first.
Step 2. Write down how you did it by hand since that's what you need to tell FMP.
(Don't expect FMP to do something until you can do it by hand. "IF OR CASE" is a premature question.)
Once you can clearly document how to get what you want by hand, it is usually easy to code. I would write down all relevant samples and come up with ideas.
(The coding should be the easy part.)
HOPE THIS HELPS.
This could be done with neither case nor If, but with a relationship that matches using inequality operators, but I advise that you use a different method of linking records as this method can be a real source of trouble as the Suppliers that you do business with change over time. Linking by a supplier ID unique to each supplier makes a lot more sense.
The earlier posts address the problem you are facing; this is my response only to the IF vs CASE question.
Either will do the same tests in a function when there are only two options. I recommend using Case rather than If always, because Case is easily expandable if/when additional options are needed down the road.
1 of 1 people found this helpful
Phil is usually right, but the bottom line in becoming a great SW developer is to "look before you leap".
IOW, figure out "how" to do something before going to the shiny box with the keys on it (aka, the computer). A piece of paper, a pen, and some thought. If you can't solve it on paper, how can the computer do it? (Ahh, it can't).
If you can master problem definition and step-wise refinement of that problem, there's almost no problem you can't solve.
I've seen many projects fail with developers go to the computer too soon. Half of my compiler class got a big fat "F" for the final grade since they kept hashing with not well thought out solutions (went to the computer too soon). My group waited until the 12'th week and others thought we were crazy. Yet, when we started coding, it all fit. We all got big fat "A"s.
People on this forum will always try to give you the "how" with FMP right away, but once you master step wise refinement, you won't need them for basic "how to" stuff.
The Code Complete book is a good first step in becoming a real developer.
HOPE THIS HELPS. (again)