5 Replies Latest reply on Aug 12, 2017 7:02 AM by beverly

    Create Records from Value List

    iwright

      Hello,

       

      I'm creating a database to track student data and create report cards. Report cards have their own table and access student data through a "Students" table using student ID number. I have a value list containing all student ID numbers. I would like to generate a record in the report cards table for each student ID in the value list, but I cannot figure out how to script it or if it is possible to do so.

       

      Is it possible to create a new record for each value in a list and if so, how would I go about doing that?

       

      Any help/hints are appreciated.

       

      Thanks!

        • 1. Re: Create Records from Value List
          fitch

          It's fairly straightforward, something like:

           

          Set Variable[ $idList ; yourValueList ]

          Set Variable[ $count ; ValueCount($idList) ]

          Go to Layout[ report cards ]

          Loop

          Set Variable[ $i ; $i+1 ]

          Exit Loop If[ $i > $count ]

          New Record

          Set Field[ studentID ; GetValue( $idList ; $i )]

          End Loop

          1 of 1 people found this helpful
          • 2. Re: Create Records from Value List
            jbrown

            Afternoon. I'd be glad to help out.

             

            Let me make sure I understand: you want to create a record for each student in the report card table and populate a field there with the student ID.

             

            This certainly can be done. Let's walk thru the steps:

             

            1. Get a list of all the IDs of students.

            2. Loop thru the list and create a record for each value in the list.

             

             

            There's a few ways to do each step:

            1. Since you have all the student IDs in a value list you can use the function called ValueListItems. This gathers all the values in the list. Set this to a variable:

             

            Set Variable [$IDs ; ValueListItems (Get(FileName) ; <<YourValueList>>)]

             

            2. Then go to the report card layout and start a loop:

             

                 Set variable $i  1  //create a counter variable

                 set variable $max [ValueCount ($IDs);

            Loop

                 New record

                 Set Field [YourTable::YourField ; GetValue($IDs ; $i )

                 set variable [$i ; $i + 1];

                 Exit loop if [ $i > $max]

            End Loop

             

            Go to Layout [ original layout]

             

             

            That will help you get started. Feel free to continue to ask clarifying questions as you go. And also feel free to post in the New to FileMaker group about scripting in general if you're in need of that.

            1 of 1 people found this helpful
            • 3. Re: Create Records from Value List
              iwright

              Thanks for explaining all the steps! I got it working.

              • 4. Re: Create Records from Value List
                jbrown

                You're welcome.

                If you are interested in some refinements, here are some thoughts:

                 

                1. Depending on how many students you have, displaying the IDs in a value list is not too ideal. It gets long and hard to scroll thru. You might consider other ways to display those such as a portal.

                 

                2. I started my FileMaker life as an in-house developer for a school and set up a system similar to what you're doing. When I came to my current job, I found out that there's a rule about primary keys (in your case the student ID). Primary keys should be unique, hidden, uneditable, and shouldn't mean anything to someone looking at the system. And I know from experience that student Ids are meaningful to many people, though they are the identifying nature of a student. I just looked in a book of database theory and found this rule to be common. I think, however, FM developers may not follow this rule.

                Student IDs may eventually be reused during the history of the school district, so by definition they are not fully unique to each student in the district.

                 

                Instead a primary key should be unique and hidden and meaningless to a principal or teacher or student.

                 

                Since much data from testing sites and lunch system and such use the student ID as a primary key, importing that into FileMaker would require some work to find the student's primary key and then writing that to the child table instead of the primary key.

                 

                 

                Just a thought. 'Tis something to consider and not a 'you must do this'.

                • 5. Re: Create Records from Value List
                  beverly

                  Great answers! You have a value list based on a field in Students (the ID) and that's great for the value list. I see you have a correct answer. I'm going to present another option that may help you and/or others in the future.

                   

                  (from the Student table/layout)

                  Show all Records (manually is fine)

                  Export Records (select only the ID field and choose .CSV as the format. Name the file: studentIDs.csv)

                  (from the Report Card table/layout)

                  Import Records (select the file created above and "match" the field which would be your foreign key. Auto-enter yes)

                   

                  Et Voila! You have created the records you need in the other table.

                  Beverly