3 Replies Latest reply on Sep 17, 2013 9:27 AM by philmodjunk

    The Lookup () That "Won't Become..."



      The Lookup () That "Won't Become..."


           I'm really stumped here.  My idea is to create a system of two related tables.  Table 1, Student Records, contains testing scores for multiple achievement areas which are then compared to a two-field matrix comprised of the testing category names and their associated minimal passing scores located in the related 'reference' table, table 2.  Testing scores from each student (there are 21 scoring categories per student record) are respectively compared to their companion minimum passing score on the reference lookup table and then calculation logic back in the Student Records table decides whether each result is "On target" or "Below Target."  

           MATCH FIELD (1st field, Student Record table): "FirstName" of type number.  (This field contains the student's numeric score for a specific testing category.)

           CALCULATION FIELD (2nd field, Student Record table): "FirstName_BloTarget" of type calculation with the result of type text.  Formula: FirstName_BloTarget = If ( FirstName < Lookup ( CA_TargetDataValues::ScoreUpperLimit ) ; "•" ; "" )

           LOOKUP FIELD (3rd field, on related Target Data Values table): "ScoreUpperLimit" of type number.  This field contains the minimum passing score for each of the 21 testing categories in the Student Record table.

           Question 1: I figured I need to use the Lookup () function inside a calculation field vs. using the Lookup dialog since I need to perform comparison logic on the match value and the found lookup value.  Is this true?

           Question 2: Do fields joining the two tables need to be the actual match and lookup fields or should the tables relationship be made in the more traditional sense with numeric ID key fields?

           Question 3. By my way of thinking, I don't necessarily want or need to copy the found value from the lookup table (table 2) into the match table (table 1) since I only want to compare the various student test scores with the lookup minimum threshold scores, do some logic within the 2nd field's calculation, and end up with either an "On target" or "Below Target" result being placed in that 2nd field (see below).  Must I copy the found value from the lookup table over to the match table if I don't really need it there?

           In a nutshell, I want the 2nd field, "FirstName_BloTarget," to fetch the student's score in the 1st field, "FirstName," show it to the 3rd field in the lookup table, ScoreUpperLimit, find that score's minimum passing value, compare it to the student's score, and finally plop one of two possible text results into itself. 

           I can't get this to work after pouring over this problem for hours on end, researching two FileMaker reference books and scouring the web.  It's a no-joy situation.  Am I on the right track or should I pursue another course of action to achieve the desired result?

      Many thanks to those who devote valuable time to guide novices like me!

           Platform: MacBook Pro, System Version: 10.7.5, FileMaker Version: FMPAdvanced 10, Developer Skill: Novice

        • 1. Re: The Lookup () That "Won't Become..."

               1. Not really. I've never used the Lookup() function, ever, in my many solutions. The reason being that I was always able to set up a relationship that accessed the data from the related table such that I never have had a use for this function. With the right relationship in place, your calculations can refer directly to fields in the related record.

               2. It depends on your data and what you need. When a value such as a student score needs to be compared to a reference table, it makes sense to use the student score as a match field in some cases. And you don't have to match values exactly. It's possible to match to a range of values in a number of different ways. (see my comment at the very end of this post.)

               3. No see my answer to 2.

               Here's how I would do it:

               Table 1: StudentScores. Each record records a single student score in a single category. If a Student is tested in 4 different categories, you have 4 records with that student's ID, category and score in this table.

               Table 2: ReferenceValues. Each record is one minimum score for one testing category.



               StudentScore::Category = ReferenceValues::Category 

               If Reference category has values for multiple grade levels, add another pair of match fields that match by grade level.

               Your field, of type calculation, would then have this expression:

               If ( Score < ReferenceValues::MinimumScore ; "Below Competency" )//put in quiotes whatever result is desirable

               Note: In California were I live, my kids get test result reports that fall into multple ranges such as "proficient", "below standard", etc Such is also possible for your reference table, but then you have to match by score as well as by category, using an inequality operator and a sorted relationship.

          • 2. Re: The Lookup () That "Won't Become..."

                 PhilModJunk, your great reply leads me to further questions.

                 1. I was pursuing the "lookup," snapshot-in-time approach to referencing data as opposed to the more common dynamic data relationship because the user doesn't want past records auto-updated with changing minimum passing score values, should they be altered in the future.  Apparently, this student scoring system is new this year and the related minimum passing scores (table 2, the lookup table) may change as the testing system matures over time.  Essentially, she wants to prevent past records from updating should the scoring see future change.  In this light is the 'lookup' the better way to go?

                 2. Regarding a single record for each individual score, to my knowledge, parsing the 21 category test areas this way will necessarily preclude all 21 scores from being viewed together on one row in the List View, correct?  She has stated that she'd like to view and input/edit all the actual scores in a List View or Table View.  To my understanding this won't be possible in the List View if each test score group is spread out over separate records.  Is there a work-around for this?

                 3. The reference lookup table is already one category per record, as you mentioned.

                 I'll fiddle with the structure and calculation as you've outlined and see what happens.

                 Thanks loads. 


            • 3. Re: The Lookup () That "Won't Become..."

                   1. But the look up function does not do this. An auto-enter calcualtion--which uses the same relationship based approach as a "dynamic" reference will do that.

                   2. This is not correct. You can set up a "horizontal" portal to display the data in  columns. Each value is from a one row filtered portal. This is a more complex layout design, but still more flexible than 21 different fields--which require 21 different relationships.


                   Your list layout would be based on Students--one record for each student and your portals would refer to different records for that student in Scores. (Each would fliter for a specified category.)