8 Replies Latest reply on Sep 23, 2013 6:53 AM by philmodjunk

    Capture Value List Line Number



      Capture Value List Line Number & Associated Character String


           BACKGROUND: I have a DB with two related tables. This system tracks the scores students earn on periodic assessment tests.  The 1st table holds the bulk of test results data.  The 2nd table is utilized as a reference table and contains 21 records with the Minimum Passing Scores for each of the assessment test categories.  These reference scores are passed back to the 1st table by way of a lookup for comparison and further data manipulation.  I have used a data lookup instead of a dynamic relationship between the two tables because the Minimum Passing Scores for this new student grading system may change at some point in the future as the program matures and the user wants to preserve the original grading values in old records if the reference table scores are ever changed.

           PROBLEM: I am working to improve the speed of data entry in the 1st table by automating the process as much as practicable through calculation/script.  Presently, I have a script-in-progress that captures certain field data from the current record, creates a new record, and sets those same certain fields in the new record with the captured data values.  Now, I want to take this automation a step further by having calculation/script make a predictive assumption about what the user would most likely place in a certain field of the duplicated record, but still allow the user to choose otherwise.  Specifically, I want it to look at the value in the "Test Category" field -- type text with an associated value list -- from the current record, determine which return-separated line that value came from within the associated value list, and automatically place the value from the next line down in the value list to the same field in the new, duplicated record.

           After the "Duplicate" script runs and the predicted value is found, placed and highlighted in the field of the new record, the drop-down will remain extended in that field, drawing the user's attention to it, and the user can commit the field with the predictive value or select another, if they desire.

           QUESTION: What would be the procedure to capture the line/row of a currently selected value in a Value List and get the value from the next row down in that same list?

           My research seems to indicate that calculation/script manipulation of value lists is not such an easy thing to do.

           Thanks to all here who volunteer your time and skills to the FileMaker community.  It REALLY makes a difference to those of us who are less-skilled. :)

        • 1. Re: Capture Value List Line Number & Associated Character String

               It isn't that bad, honestly. smiley

               Have a look at the script step: "ValueListItems ( fileName ; valueList )"

               This returns a list of the items in the value list in the specified file (use  "Get(FileName)"  for the current file). Search the list for the position of the desired value and use "ValueCount( test) " to determine the number of values up to that position. You can get the next value using "GetValue ( listOfValues ; valueNumber )"

               Have a look at the script-steps documentation to get acquainted with them. You could use them in a script, but perhaps also as a calculation in the 'auto-enter' value calculation of the field concerned. I'd say use a script first to see if it works and then decide where you would like to put the procedure. 

               Good luck with it!

          • 2. Re: Capture Value List Line Number & Associated Character String

                 If you base your value list of test categories on a table--not a bad idea anyway since you never know, they might change in the future too, you can use a number field in that table that sequentially numbers the records in the order that they appear in your value list. Then you could set up a relationship to an occurrence of this new table with the X operator to match to all category records and then GetNthRecord can use the Sequence number of the currently selected value to access data in the sequence + 1 related record.

            • 3. Re: Capture Value List Line Number & Associated Character String

                   Phil, I decided to try your suggestion to add another reference table, especially after running the idea past my wife (the school teacher for whom this solution is being developed).  She agrees with your assumption that the Assessment Categories may change in the same way the Minimum Passing Scores may change.

                   ACTIONS: I added another reference table (table 2) and related it to the existing Student Records table (table 1)  as you suggested (I've never used the X operator before so I learned something there).  I can see and select the value list from inside Table 1.  Almost good-to-go!

                   PROBLEM: Everything went together well until I tried to use the GetNthRecord function in order to drive the 'predictive' functionality I'm looking for.  Wanting to use it in my script, I applied the "Set variable" script step which provides for calculation.  I'm not sure how to proceed from here.  As far as I can tell, the numeric sequence number to be used in the calculation is not a part of the currently selected Assessment Category field in table 1 despite the fact that the table 2 sequence number is visible right alongside the associated table 2 category when the user clicks into the Assessment Categories dropdown field ("Also display values from second field" is enabled so the value list sorts properly through use of the corresponding sequence number field).  The sequence number originating in table 2 doesn't seem to be resident anywhere in the 1st table.  Right?  Wrong?

              QUESTION: How do I capture the sequence number in the current record of table 1 and include it in the GetNthRecord function so I can use that number + 1 to fetch the next Assessment Category row down in the reference table 2?

              • 4. Re: Capture Value List Line Number & Associated Character String

                     So you would have these relationships:


                     TestResults::Category = TestCategories::Category (Might even be better to use ID numbers instead of the actual category)

                     Then to reference the "next" category from that selected you would use this expression in a set field or set variable step:

                     GetNthRecord ( TestCategories|All::Category ; If (TestCategories::Sequence < Max ( TestCategories|All::Sequence ) ; TestCategories::Sequence + 1 ) )

                     TestCategories|All and TestCategories would both be Tutorial: What are Table Occurrences? with the same data source table of test categories and the Max function protects against getting the ? result should you duplicate a test Result record that has the last test category selected.

                • 5. Re: Capture Value List Line Number & Associated Character String

                       Okay, here's what I have up to this point.  I've provided a bunch of screenshots in the hope that it helps assess the situation.

                       SPECIFIC THINGS I'VE NOTICED:

                       a. The relationships on the relationship graph are not the one-to-many variety as you illustrated in your above post, Phil.  I tried making different relation combinations and FileMaker interpreted some of them as one-to-many but overall they were relating the wrong things.  Not sure what I've done wrong here.

                       b. The "AssessCategories|All" value for the currently selected Assessment Category ("Identify Shapes," in this case) is not correct.  The Data Viewer indicates the value to be "First Name."  I have captured the true value, as seen in the Main Form View, in the variable "$CurrAssessCategory."

                       c. The sequence number for the AssessCategory::SequenceNum DOES NOT show a value in the Data Viewer.  I'm guessing this is the primary reason the GetNthRecord function to fails to work.

                       Any ideas for the failure (other than my feeble, novice skill set)?


                  • 6. Re: Capture Value List Line Number & Associated Character String

                         Jan, Thank you for your response.  For now, I'm trying a different tac to get at a solution.  I will keep your idea handy, however, as I may use it in future.

                         Thanks again!

                    • 7. Re: Capture Value List Line Number & Associated Character String

                           Phil, HOLD THE PHONE!  I think I found the problem.  I went back to fiddling with the occurrence relationships and found that the problem lay with the way I connected "REF_AssessCategories" to "StudentRecords" (red table occurrence to upper-right purple occurrence).

                           My lack of understanding regarding data modeling theory and application along with admitted weakness in the proper creation of table occurrence relationships is really holding back my progress with FileMaker.  I continue to read on the subject and attempt to dissect and kind of 'reverse engineer' successful FileMaker databases to get a better grasp of the subject and practical application but progress is slow and fitful.  If anything, I wish FileMaker would step up its efforts to provide a deeper understanding of this seemingly difficult subject to prospective FM developers at a reasonable price point!  I'd wager that thousands of people have given up on FileMaker (and other database development platforms) over the years due to this one problem area.

                           Anyway, in the end I abandoned using the purple numeric ID field and instead linked the purple "AssessCategory" field, type text, to the red AssessCategory field, type text, and everything seemed to fall into place (see attached image).  Curiously, the relationships now appear to be 'many-to-many' and not the one-to-many that you described in your example post above so I'm not sure how it's working but it is.  The 'predictive' feature I was looking for seems to be functioning pretty much the way I imagined it and I'm cruising through data entry on a much faster basis now, thanks to you and your patience.

                           I sincerely appreciate it!

                      • 8. Re: Capture Value List Line Number & Associated Character String

                             If you haven't already clicked on it and read it (better yet followed the tutorial steps in it to create the sample demo file), the link that I provided on table occurrences may prove useful to advancing your understanding of Table Occurrences.