1 2 Previous Next 26 Replies Latest reply on Dec 23, 2011 8:40 AM by JonGreyson

    Create X number of records based on value list choice

    JoelSchwartz

      Title

      Create X number of records based on value list choice

      Post

      Hello,

      I have a student records database that I use for my teaching.  I track attendance (portal on main layout) and receive payment for students in "sessions" of ten lessons. I would like to be able to change the length of these sessions through a value list.   So if I choose a session of ten lesson for one student it would create 10 attendance records for that student.  If I choose 5, it creates five. How do I do this?

      Thanks for your time

        • 1. Re: Create X number of records based on value list choice
          mgores

          I did something similar where you could enter the number of sessions, the type of session and the total price.  I wrote a script that would then create a record for each of the sessions, with the price per session.  Then had layouts where you could view a list of sessions the client had booked showing those coompleted and those remaininig with fields to enter date completed and other relavent info.

          I used global fields to enter the booking info, then clicked a "Book: button to run a script that set variables from the global fields which looped through and created the records in a Sessions table, then cleared the globals.

          • 2. Re: Create X number of records based on value list choice
            philmodjunk

            Set Variable [$StudentID ; value: Students::StudentID ]
            Set Variable [$NumbSessions ; value: Students::NumbSessions //this would be your drop down field ]
            Freeze Window
            Go To layout [Sessions]
            Loop
               Exit Loop If [$NumbSessions < 1 ]
               New Record/Request
               Set Field [Sessions::StudentID ; $StudentID ]
               #You may need additional set field steps here to set up the new sessions record
               Set Variable [$NumbSessions; Value: $NumbSessions - 1 ]
            End Loop
            Go to Layout [original layout]

            • 3. Re: Create X number of records based on value list choice
              JoelSchwartz

              My goodness Phil, you just blew my mind! 

               

              Another thing I need, if it's not asking too much, is a field that counts how many fields were created.

               

              The fields are set up like this

              "Lesson Date", "Missed lesson?" (popup yes or no), and finally I would like "Lesson count" (if a user enter 5 lessons in the value list, this will label the fields 1-5)

               

              Thanks a million, I'm going to study that script to try and fully understand.

              • 4. Re: Create X number of records based on value list choice
                philmodjunk

                No fields were created, only records.

                You cannot create fields on the fly in FileMaker. Instead, you create records in a related table and use one or more portals to display them.

                I've assumed this relationship for your database:

                Students::StudentID = Sessions::StudentID

                StudentID is an auto-entered serial number in the students table.

                This allows you to use a portal to Sessions on the students layout to list all sessions records for that student. And there are ways to display a number in each portal row so that no field need be added for numbering the sessions.

                • 5. Re: Create X number of records based on value list choice
                  JoelSchwartz

                  I'm sorry, my mistake.  I would like to have a label on each new record created, which is not unique (a student might have several sessions of 10).  I would like to know which lesson number we are on in the current session.  

                  Your script works perfectly, I just want a count of how many records were created using the script and for this number to be reset each time a new session is created.

                  • 6. Re: Create X number of records based on value list choice
                    philmodjunk

                    Hmmm, I percieved "session" and "lesson" as equivalent and now see that they are not. How are you grouping the records created by this script to be part of just one "session".

                    To answer the specific question, remember this comment in the script? "You may need additional set field steps here to set up the new sessions record"

                    That's the point were you can use Set Field to assign values to aother fields in the record. If you add a variable such as "$LessonCount" you can use it to count the sessions as they are created and assign the current total to each record as it is created.

                    Set Variable [$StudentID ; value: Students::StudentID ]
                    Set Variable [$NumbSessions ; value: Students::NumbSessions //this would be your drop down field ]
                    Set Variable [$Lessoncount ; value: 1 ]
                    Freeze Window
                    Go To layout [Sessions]
                    Loop
                       Exit Loop If [$NumbSessions < 1 ]
                       New Record/Request
                       Set Field [Sessions::StudentID ; $StudentID ]
                       #You may need additional set field steps here to set up the new sessions record
                       Set Field [Sessions::LessonNumber ; $LessonCount ]
                       Set Variable [$NumbSessions; Value: $NumbSessions - 1 ]
                       Set Variable [$Lessoncount ; value: $Lessoncount + 1 ]
                    End Loop
                    Go to Layout [original layout]

                    Clever programmers will notice that this script could now be simplified slightly as you don't really need to increment one counting variable while decrementing the other.

                    • 7. Re: Create X number of records based on value list choice
                      mgores

                      Could you just use the $NumbSessions variable to set a LessonNumber field to number the sessions 1-10 (or whatever that number would be).  You could also set a calculation field in the student table to Count (sessions::datecompleted) to see how many sessions have been done

                      and conversly Count (sessions::sessionID) - Count (sessions::datecompleted) to get how many are left

                      • 8. Re: Create X number of records based on value list choice
                        philmodjunk

                        "Could you just use the $NumbSessions variable to set a LessonNumber field to number the sessions 1-10 (or whatever that number would be)"

                        Well, that's what I meant by: "Clever programmers will notice that this script could now be simplified slightly"Wink

                        If you use the $NumbSessions variable, you number them in reverse order as the script is currently written. I tried to keep script changes to a minimum here but with numbering them in ascending order to lesson confusion when the records are displayed in an unsorted portal. That said, you could specify a sort order on the portal to get the desired ascending order to the numbered records.

                        • 9. Re: Create X number of records based on value list choice
                          mgores

                          sorry, must have been writing my post while you were posting yours.  I didn't see yours Smile

                          Guess I was thinking the order should not matter since they would all be created at the same time.  The numbers wouldn't be significant until you started entering lesson specific info to each of the records.

                          • 10. Re: Create X number of records based on value list choice
                            philmodjunk

                            It doesn't matter much at all, but might be confusing to the "newbie" when they see the reverse order. You can actually remove this step:

                            Set Variable [$NumbSessions; Value: $NumbSessions - 1 ]

                            and change the exit loop if step to:

                            Exit Loop If [$LessonCount > $NumbSessions ]

                            • 11. Re: Create X number of records based on value list choice
                              JoelSchwartz

                              Thanks so much Phil and Mark, this works perfectly. 

                              • 12. Re: Create X number of records based on value list choice
                                JoelSchwartz

                                Phil, to answer your question.  I do not have anything in place to group the lessons into a particular session.  Not sure how this would work....

                                • 13. Re: Create X number of records based on value list choice
                                  philmodjunk

                                  You've described your set up in terms of students, sessions and lessons and that you can have more than one lesson for a given session. What is a "session"?

                                  • 14. Re: Create X number of records based on value list choice
                                    JoelSchwartz

                                    A session is a group of lessons.  E.g. the student pays for a session of X lessons. I can appreciate your confusion!

                                    1 2 Previous Next