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