8 Replies Latest reply on Apr 18, 2017 7:48 AM by philmodjunk

    script:  lookup records in another table for comparison

    jzanter

      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.

        • 1. Re: script:  lookup records in another table for comparison
          philmodjunk

          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.

          • 2. Re: script:  lookup records in another table for comparison
            jzanter

            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...!

            • 3. Re: script:  lookup records in another table for comparison
              philmodjunk

              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?

              • 4. Re: script:  lookup records in another table for comparison
                JackRodges

                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

                Lookup Dialog.PNG

                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

                Case(

                  Lookup; Lookup

                ; Lookup Higher: Lookup Higher

                ; Lookup Lower; Lookup Lower

                ; "Error"

                )

                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.

                • 5. Re: script:  lookup records in another table for comparison
                  jzanter

                  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.

                  • 6. Re: script:  lookup records in another table for comparison
                    philmodjunk

                    "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.

                    • 7. Re: script:  lookup records in another table for comparison
                      jzanter

                      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.

                      • 8. Re: script:  lookup records in another table for comparison
                        philmodjunk

                        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]

                        Loop

                           "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 ]
                        End Loop

                         

                        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.