1 2 3 Previous Next 31 Replies Latest reply on Mar 15, 2013 11:27 AM by philmodjunk

    script to create new blank record in another table with matching ID#

    HeidiStewart

      Title

      script to create new blank record in another table with matching ID#

      Post


           I have a FM Pro 12 file with two tables "Faculty of Science" and "Mandatory Courses".  in the Faculty of Science table i have my individual students listed, seperated by ID#'s.  I want to create a button that when pressed will take the ID # from that particular student and create a blank record in the Mandatory Courses table with the student's ID# auto populated.

           I have already created the field ID# in both tables and put a relationship between the ID# fields.

           Im going to use (i think) a portal on my Faculty of Science record to show the list of Mandatory courses, but how do i filter it so that one student with a Dept of Biology shows only the Biology Mandatory Courses in the portal, and another student with a Dept of Physics shows only the Physics Mandatory Courses?  I have 5 different Dept's and each have 3-6 Mandatory Courses (ie: BIOL 6910; BIOL 6003 and PHYS 6997; PHYS 6106)  i would want the student whose Dept is listed as Biology to show the BIOL 6910 and BIOL 6003 but not the PHYS 699 or PHYS 6106.

        • 1. Re: script to create new blank record in another table with matching ID#
          philmodjunk

               I'm not sure that you need any such script.

               If all you want to do is list the mandatory courses for each record in Faculty of Science, this can be done with a simple relationship:

               Faculty of Science----<Mandatory Courses

               Faculty of Science::Major = Mandatory Courses::Major

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Faculty of Science::Major can be formatted with a value list of different majors.

               In Mandatory Courses, the Major field might read: "Biology" for course IDs: BIOL 6910 and BIOL 6003 and would read "Physics" for courses with IDs: PHYS 699 or PHYS 6106.

          • 2. Re: script to create new blank record in another table with matching ID#
            HeidiStewart

                 I'm sorry, let me clarify, in my "Mandatory Courses" table i have all of the courses listed as fields, and each record will be one student's ID# and either "Yes"; "No" or "N/A" for each of the courses.

            • 3. Re: script to create new blank record in another table with matching ID#
              philmodjunk

                   Then you have a "many to many" relationship. Any given course can be linked to any number of students and any given student can be linked to more than one course. This will require a third table and the following relationships:

                   Courses|Major>----Faculty of Science----<Mandatory Courses>----Courses

                   Faculty of Science::Major = Courses|Major::Major
                   Faculty of Science::__pkStudentID = Mandatory Courses::_fkStudentID
                   Courses::__pkCourseID = Mandatory Courses::_fkCourseID

                   Courses|Major and Courses are two Tutorial: What are Table Occurrences? with the same data source table. You would create one record in this table for each course that you might select as a Mandatory Course.

                   A portal to Mandatory Courses can be put on your Faculty of Science layout. The relationship between Faculty of Science and Courses|Major can be used in one of two ways:

                   1) It can enable defining a conditional value list that only lists courses for the student's specified major that you can use in the portal to Mandatory Courses to select and add required course records. This method does not require a script.

                   2) it can be used with a script that pulls up a list of required courses as specified by the Faculty of Science::Major field so that the script can loop through these records and create one matching record in Mandatory Courses, linked to the current student.

                   In either case, the portal displayes the mandatory courses and fields from Courses can be included in the portal row to supply course names, etc.

              • 4. Re: script to create new blank record in another table with matching ID#
                HeidiStewart

                     ok i'm sure what you're saying makes sense, but i'm lost.  I've been using FM Pro 12 for less than 2 weeks now!  lol  can you say that in english? 

                     right now my Faculty of Science table has 1 record for each student (FIelds include "ID#"; "Name"; "Dept"; "SIN"; etc).

                     then i have my Mandatory Courses table with records for each students completion of the required courses (FIelds include "ID#"; "BIOL 6910"; "PHYS 6106")

                     so when i enter records in my Mandatory Courses table i am specifying that stu (ID#) completed BIOL 6910 but not BIOL 6003, and PHYS 6106 won't even show up, because that stu is listed as Biology in field "Dept" on my Faculty of Science table.  each course field (BIOL 6910 and PHYS 6106) have a dropdown selection of "Yes"; "No"; or "N/A".

                     are you saying i need a new table that lists each of the courses (fields from Mandatory Courses table) as records and assigns them to one of the 5 Depts??

                     ok, but that still doesn't help me when i'm entering a brand new student in my Faculty of Science table that i can press a button and it will create a new record with matching ID# in the Mandatory Courses table.

                     am i way off?   Thank you for your help and patience!!!!

                • 5. Re: script to create new blank record in another table with matching ID#
                  philmodjunk

                       Your getting there. But I strongly recommend that you redesign your Mandatory courses table.

                       One record in Mandatory Courses should record data for a single course assigned to a single student.

                       Thus your fields, BIOL 6910"; "PHYS 6106"... become a single field, but you now have a separate record for each course for a given student. This gives you a great deal of flexibility as you can link a given student to any number of courses in order to list them as mandatory courses.

                       And use, you need a table of Courses where each course appears once as a separate record. And while you need data such as BIOL 6003 in that table, don't use that field as the __pkCourseID field. Use an auto-entered serial number field instead.

                       ok, but that still doesn't help me when i'm entering a brand new student in my Faculty of Science table that i can press a button and it will create a new record with matching ID# in the Mandatory Courses table.

                       That's why I said this in my previous post: (text in bold face added to clarify.)

                       

                            it can be used with a script that pulls up a list of required courses as specified by the Faculty of Science::Major field so that the script can loop through these records and create one matching record in Mandatory Courses [for each related course record], linked to the current student.

                       I'll be glad to share a script for that, but first you need to get your data model design correctly. wink

                  • 6. Re: script to create new blank record in another table with matching ID#
                    HeidiStewart

                         Hmmm ok, i created a new table called Courses with only fields in it "Dept" and "Course" i have 15 records in this table, this is where i've listed that BIOL 6910 is a course for the Biology Dept. etc etc.

                         Now i'm confused as to how i change my Mandatory Courses table ..... do you want me to have only 3 fields in this table also?  "ID#" "Course", this would be a dropdown list of the records from my Courses table and then a "Completed" with a yes; no; n/a option...... right??????

                         in this case i would end up having to enter 3-4 records for each student .... not sure how that'll work considering i want to show a summary on my Faculty of Science layout.

                    • 7. Re: script to create new blank record in another table with matching ID#
                      philmodjunk

                           i created a new table called Courses with only fields in it "Dept" and "Course"

                           I recommend that you also add a __pkCourseID field defined as an auto-entered serial number field to use to link it to other tables.

                           

                                Now i'm confused as to how i change my Mandatory Courses table ..... do you want me to have only 3 fields in this table also?  "ID#" "Course", this would be a dropdown list of the records from my Courses table and then a "Completed" with a yes; no; n/a option...... right??????

                           Close, but you need two ID fields, __pkStudentID and _fkCourseID. It is not a good idea to use "Biol 1001" type data as the CourseID field in these relationships.

                           

                                in this case i would end up having to enter 3-4 records for each student

                           That is correct, but you won't have to Manually enter them. With the script I have not yet spelled out, this can be done simply by selecting the correct department for that student from a value list.

                           We use such a scripted method to "pre-load" our customer receipts with the 4 line item records (listed in a portal) that will represent 90% of the items typically listed on these receipts specifically to reduce the amount of time needed to record the details of each transaction.

                      • 8. Re: script to create new blank record in another table with matching ID#
                        HeidiStewart

                             Ok i added in a Course ID field that auto serializes each course, so BIOL 6910 is Course ID# 1 and BIOL 6997 is Course ID# 2 etc.

                             i then added a Course ID# field to my Mandatory Courses table and linked it to the Course ID# field in my Courses table.

                             lol how am i doin so far?

                        • 9. Re: script to create new blank record in another table with matching ID#
                          philmodjunk

                               Not quite. You will need two table occurrences that refer to Courses instead of one. Each has a different relationship to link to Faculty of Science in different ways and is used for different purposes.

                               In Manage | Database | relationships, make a new table occurrence of Courses by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be Courses|Department.

                               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                               You can then update your relationships to be:

                               Courses|Department::Department = Faculty of Science::Department

                               Courses::Stu ID# = Faculty of Science::Stu ID#

                               If you didn't check out the link I provided earlier, you may want to read the tutorial in this link in order to bet a better understanding of Table Occurrences and how they control the function of your FileMaker Database: Tutorial: What are Table Occurrences?

                          • 10. Re: script to create new blank record in another table with matching ID#
                            HeidiStewart

                                 ok i looked at the Table Occurance link last time, i think i get the general idea.

                                 my confusion is coming from when you write this:   Courses|Department::Department

                                 what does this mean?  the line between Courses and Department and two :: between Department and Department. 

                                 You want me to add a relationship between the field Dept in my Courses table with the field Dept in my Faculty of Science table?

                                 and then you also want me to link the field Stu ID# in my Mandatory Courses table with the field Stu ID# in my Faculty of Science table?

                            • 11. Re: script to create new blank record in another table with matching ID#
                              philmodjunk

                                   with Courses|Department::Department

                                   "Courses|Department" is the name of the table occurrence box. "::Department" is the name of a field in that table. This is an example of one possible naming convention where you put the data source table name to the left of | and identify the match field used on the right. When you go to select a table occurrence from drop downs in other parts of FIleMaker, you'll find that you can tell which table occurrence you are selecting.

                                   My answer to your last two questions is "yes, but..."

                                   Yes, but each link is to a different table occurrence of Courses.

                              • 12. Re: script to create new blank record in another table with matching ID#
                                HeidiStewart

                                     ok.... i only have 3 fields in my Courses table, Dept; Course and Course ID.  i created a table occurance (copy) of my Courses table (it automatically named it Courses 2) so i linked Course ID from Courses to Course ID in Mandatory Courses, then i linked Dept from Courses 2 to Dept in Faculty of Science.  I also have Stu ID from Mandatory Courses linked to Stu ID from Faculty of Science.

                                     what's next boss?

                                • 13. Re: script to create new blank record in another table with matching ID#
                                  philmodjunk

                                       If you double click a table occurrence box you'll get a dialog popping up where you can change the name of the occurrence to something more desriptive than courses 2.

                                       Can you upload a screen shot of the occurrences and their relationships?

                                       If all is in place we can take a crack at a script that allows you to select a department student and automatically get a list of required courses.

                                  • 14. Re: script to create new blank record in another table with matching ID#
                                    HeidiStewart

                                         Here is the screenshot.

                                         Thank you! :)

                                    1 2 3 Previous Next