11 Replies Latest reply on May 5, 2015 11:04 AM by ChrisWelsh_1

    Create Multiple Records at a time



      Create Multiple Records at a time


      I am creating a database for training. In general when someone here does training they often do one of two things. One person does multiple classes in a day or one class has multiple people. I would like to make a layout that allows me to create multiple record entries at a time.

      Example: With the input by user I can type in the persons name and it will auto fill all the rest of his/her data (I got this). Under that student information there is a line where the person can choose a class, day the class took place, complete/incomplete, score, renew date (calculated from the complete date and a field set with a certain number of days).

      My question is this. In the example above, how would I go about making this so that I can fill in the user information, then set it up so that the person can choose up to 10 courses from 10 lines (drop down chooser, etc...) and then create up to ten records for this person in the database with one input? In essence, create ten records for ten courses taken with one input.

      Or...reverse it, enroll Ten people from my employee database into a single class and make ten records for each person?

      I hope this is clear....

      Thanks for any help you can provide, or maybe push me to a tutorial that might touch on what I am looking to do?

        • 1. Re: Create Multiple Records at a time

          If you set up relationships and a portal, the very act of picking a class can also create the needed record.


          Client::__pkClientID = Registration::_fkClientID
          Classes::__pkClassID = Registration::_fkClassID

          Set up a portal to Registration on the Client layout and you can format _fkClassID with a value list of available classes. If you enable "allow creation of records via this relationship" for Registration in the Client to Registration layout, you can use the act of selecting a class in the _fkClassID field an action that both picks a class and creates a new record for recording data about that client's participation in that class.

          This is a deliberately simplified approach. Your operational practices might require a more sophisticated version of this basic technique.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Create Multiple Records at a time

            Hello and thank you for answering. I am not very savy at this, I mostly use more basic functionality so if I might just ask for a bit more clarification.

            I have 3 tables (none external).

            1. Students 2. Classes and 3. Records

            Student has the info for all students, Classes has all class info and Records is empty, it is where I want these class records to save to. So I was going to set this up as a layout on the Records.

            I have the student info  that will be brought in through a relationship with the First Name Last Name fields from Student table to Records (I assume that will begin a single record)

            Are you saying I should bring in a portal that draws from the Classes table with a drop down menu of classes? Allow it to create new records via relationship  and it will save those records with the student name and each class chosen in separate records in the Records table?

            • 3. Re: Create Multiple Records at a time

              Use Records where I used registration.

              Set up a portal based on Records, Link records to students and classes as shown in my previous post. (Clients = students)

              • 4. Re: Create Multiple Records at a time

                Okay thanks! I did it like this... I didn't want to create it on the STUDENT (client) layout so I did it in a new layout called ADD RECORDS:

                I have a layout to write to table named RECORDS

                I create a record when I enter into ADD RECORDS from the RECORDS MAIN layout. On the top I have a student information area that is linked by relation to STUDENT table to auto fill with the entry of the first and last name (will do it in the end by student ID if I can get those)

                I created a second instance of the RECORDS table RECORDS2 and brought that in as a portal. I then filled that with fields from the RECORDS table with the COURSE table related by COURSE NAME field (drop down) on RECORD2...This auto-fills the course code when course is selected.

                Every time a course is selected in the portal it creates a new record below the one I am on.

                this is fine but the problem is it is always one record ahead and when I save (I have it set to force a save not autosave) then there is always an extra record in the end. Is there a way, one the person hits save, to have it discard any empty records in the portal? Or is it just the second instance of RECORDS causing this and I should do it another way.



                • 5. Re: Create Multiple Records at a time

                  Every time a course is selected in the portal it creates a new record below the one I am on.

                  This is not the case. There is no extra record. When you set up a portal based on a relationship with "allow creation..." enabled for the portal's table, FileMaker puts what appears to be an extra blank row into the portal. This is the "add" row where you can create new records in the portal simply by entering information. If this row were not there, you'd need to use a button and a script to create new related record that would then appear in the portal. Sometimes that's the preferable design approach, but now you know that there is no extra record being added to the portal's table. (It's possible for fields from a different table other than the portal's to show data even in the "add" row if placed in the portal row.)

                  I don't really follow the logic to not using a layout based on students. Since you need to select different classes for each student, using a layout based on students would seem the simplest context to use here. Keep in mind that you are not limited to a single layout based on Students. You can create as many different layouts based on students as you need.

                  • 6. Re: Create Multiple Records at a time

                    The student info is not in the portal so I was afraid that if I built it on the STUDENT then it would input student info in the STUDENT table and not in the RECORDS table that is brought in through the portal. As it is, all the records outside (student) and inside (course) the portal ARE saving to the RECORDS table.

                    I have it set so that the user must hit a submit button to 1. Submit the records before they leave the layout 2. check that the student info fields have been filled, and then go back to the master records list...when I do that, it shows the row below the last row I filled in as a half filled row with just the user info (entered outside the portal directly to RECORDS), none of the course info (the fields that show in the portal which had yet to be filled and input into RECORDS2).

                    I will rebuild it based on Students and try again but the row that appears below the last filled row IS somehow being saved in the RECORDS table and shows below the last record with just the student info.

                    If I could solve this last issue, this will work well as it is doing exactly what I want it to do AND as a bonus it shows all the courses attached to the user as well (a transcript if you will).

                    Thanks for the assistance here, its been invaluable.

                    • 7. Re: Create Multiple Records at a time

                      Well,  I found a way to keep it from writing out that partial record. I reset the tab order and will rearrange my fields.

                      The tab order activated the empty field below once the last field above was completed (that last field was a drop down menu to pick a class status) since the last field (drop down menu) automatically (through tab order) activated the next record below (that being another drop down menu for class choice) because it was next in the tab order, it wrote the user info and then waited for the next input from the drop down menu.

                      So I took off any tab order so it would not activate unless you manually put your cursor in a field or selected a class in the first drop down menu. This seems to have kept the partial record from being automatically created in the next field. Apparently with the Student info outside the portal and the course inside the portal, all it takes is for the cursor to activate a drop down menu or field.

                      Not sure it is just the drop down menus that happen to be both the first AND last fields in the rows, or any field...but it seem to work now. I am thinking I will move the drop down menus from being the first and last in the row just in case :)

                      Thank you sir...if I am off base here, please let me know...any info is welcome.

                      • 8. Re: Create Multiple Records at a time

                        The student info is not in the portal so I was afraid that if I built it on the STUDENT then it would input student info in the STUDENT table and not in the RECORDS table that is brought in through the portal.

                        That doesn't really make any sense. A portal is just what the name implies, a window into another table. Data entered into fields in that portal will not save any data to the current Student record unless you add fields from the student table to the portal row--which would not make any sense to do. So I repeat my observation that you seem to be unnecessarily complicating your design.

                        The only data from Student that should be input into Records is the StudentID value that links a record in RECORDS to the current record in STUDENT.

                        • 9. Re: Create Multiple Records at a time

                          I do know the function of a portal :) Which is why I did it the way I did it. Correct me if I am wrong but doing it the way you are saying, it will save the records only with the StudentID and not the full name and student info? I want to save each record entry with the students full name, location, and department so we can have a master records list (RECORDS table in list view) and be able to search and sort the records by name, location, department or by class, class code, etc...Every person using this will not have access to StudentID to find the student and without the other student info Location, Department, etc...we will not be able to sort in the list view of RECORDS by these fields..

                          • 10. Re: Create Multiple Records at a time

                            My point is that there is no need to save multiple and redundant copies of this information.

                            Using a portal and look up fields is quite possible by the way, but copying the other data over from the student record is not a good idea and is unnecessary. Say you discover a mistake in the student info. If that mistake is in a field copied to records, correcting it on the student record does not correct it on any of the related records in your Records table where you copied this information. You'd then have to find and make identical corrections to each related record in Records. This can easily be avoided if you use the features that make FileMaker a Relational Database to access the data in the student record without making copies of it.

                            When you create the record in the portal, FileMaker enters the student ID of the current student record  for you. This then establishes a link between the portal's record and the current student record. All the data in the student record then becomes accessible to the new records created in the portal with no need to make a copy of it in that record.

                            You can set up a master list based on the records table occurrence and include any fields from the linked students record on that layout as needed.

                            • 11. Re: Create Multiple Records at a time

                              You are right...noticed almost right away that it would cause problems. Changed it to how you describe and it works well. Thanks and sorry for being so dense.