Every layout specifies a table occurrence in Layout Setup | Show Records from. A table occurrence is a "box" in your relationships graph. It's roughly similar to a SELECT * FROM Table X query, with no WHERE and no ORDER BY clauses, but with "joins" possible in the form of the relationships specified for that table occurrence box in your relationships graph.
So going to a layout first before performing a find establishes a "context" for that find. It specifies the table from which records will be found to produce a "found set"--again somewhat similar to a record set object produced by an SQL query, but accessible only from the "context" of a particular window and table occurrence specified by that Go TO Layout step. (if you have several layouts based on the same table occurrence, then they share the same found set current record and sort order within a given window.)
In your script, I can't see what find criteria was specified in line 3 so I don't know how you are attempting to find records on that layout.
Double click that script line and tell us what criteria is hidden inside that script step. (there's another way to script finds so that the criteria is not hidden like this--making for easier review of a script.
The Find in line 3 finds ALL the records in the lookup table. I search on the ID for values >0. I had another diagnostic that told me 49 records were found, which is all of them.
Would like ot know about the other way to script finds...!
If you are going to produce a found set of all records in the table, you only need use the show all records step, not a perform find script step.
But there are better ways, both using a scripted find and by using a relationship.
But to help you with that, I need to understand how you need this to work.
I get that you have a table of Hardness values that "converts" the original measurement into a different scale or value and that not every possible value can be listed in this table.
But how, if you were just using paper and pencil would you use such a value to get the results that you need? Are you trying to find just the "closest" value from the look up table? The closest value the same or smaller? Closest value the same or larger?, The values immediately above/below the measured value so that you can interpolate a value from the table?
And could it be that there's a formula that could calculate the value from the given measurement instead of looking it up from a table?
I tried to post this earlier but Comcast killed the Internet just before I clicked send. I think this will work for you.
Let's start like a FileMaker developer:
Create a relation ship between table a and table b
Create four fields and use Lookup
The lookup optioin in the field maker dialog offers what you are trying to do for next higher value and next lower value.
You might use four fields:
1) Lookup (do not copy)
2) Lookup Higher (set radio button)
3) Lookup Lower (set radio button)
4) Evaluation calculation
; Lookup Higher: Lookup Higher
; Lookup Lower; Lookup Lower
When you enter data, the four fields will lookup in the related table and only one should find a value.
Try it and see if it works in a copy of your file.
How it work on paper: a test value is obtained (or a series of values are averaged) and a table of equivalent values is consulted. You find your value, which is typically between two values in the table and state that your value is in the range of low to high, the records that bound your value. As you said, the values immediately above and below - to give a range as the answer.
The folks that have modeled this empirical data do come up with an answer, but it is typically slightly different from the accepted standards that everyone uses. So there are formulas, but they can't be used.
"The folks that have modeled this empirical data do come up with an answer, but it is typically slightly different from the accepted standards that everyone uses. So there are formulas, but they can't be used."
If you can identify a rule that explains those differences, a formula is still possible.
Reply #4 by gofmp spells out one way to use a relationship to get the values above and below your measured value.
A pair of ExecuteSQL queries could also retrieve these values as could a pair of sorted relationships using inequality operators.
All in all, the looked up value approach seems simplest here.
Thanks Phil and Gofmp. You've given me something to chew on and some insights.
One last question on behavior of the script, as it was first posted....That is, in the first loop iteration, the "exit loop" condition was met. There should have been some number of iterations before that happened,
What is going on here?
I expected the loop to iterate over the found record set, but it seemed smarter than that.
Your script has quite a few problems. One of them is that it doesn't iterate at all as there is no step included to go to the next record.
If you need to loop through the records of a found set, not what you need here, this basic outline should be used.
Select layout with correct context
Pull up desired found set
If needed, sort the found set
Go to Record/Request/Page [first]
"Process" current record in found set
Exit Loop If [Put expression here to exit the loop if expression evaluates as "True"]
Go to Record/Request/Page [exit after last ; next ]
Exit loop is used if you need to exit the loop before you reach the last record of the found set (as you needed in your original script.) Go to record [next] advances the current record to the next record in your found set. The "exit after last" will exit the loop if there is no next record to go to.