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. :)