script:  lookup records in another table for comparison

Discussion created by jzanter on Apr 17, 2017
Latest reply on Apr 18, 2017 by philmodjunk

Hi All!

I have this simple task to execute. I'm trying to do this using methods I would have used with Coldfusion + SQL and I keep stumbling and learning...my old ways are probably holding me back.


Anyway, in one layout, I take numerical user input into a number of fields, and use a calculated field to average that input on the fly. The data happens to be material micro-hardness values. I have a button to activate a script in which I want to take the value from the averaged field and look up values in another table, to convert to another hardness scale.


The lookup table with the other hardness scales necessarily doesn't have an entry for every hardness number that I try to lookup. Let's say my average value is 466. That number lies between two records, 458 and 471. I simply want to grab alternate hardness numbers from those two records. 


walking through,

1) seems I have to goto a layout where the lookup table exists - hardnessValuesTable - before doing the query, else, Perform Find, doesn't find anything - even though I can tell it what table and what criteria to select with

This the first thing I don't understand - why line 2, to show the lookup table, seems to be necessary.


2) return to the results layout - where the user is entering data.

3) Sort, set some variables (loop counter is just a temporary diagnostic)

4) Loop through the found records........except when I loop through - my diagnostic dialog in the first line of the loop actually suggests that the Find or the Loop has a premonition of what I was looking for, and it's nearly correct.

The first value from the lookup table triggers the second IF statement and the Loop is exited before the loopcounter increments the first time.

Or so it seems.

I intend to loop through all the found records (49 are found), but only one enters the loop.

I feel like I'm missing a basic lesson in FM.