10 Replies Latest reply on Jun 24, 2014 8:58 AM by philmodjunk

    multiple primary keys

    timpf

      Title

      multiple primary keys

      Post

           Dear all,

           I am working on a database to manage courses (at a university) which are mapped to templates, which are mapped to modules (groupings of courses), which are again mapped to study programmes. An example:

           teacher A wants to offer the course "introduction to simulation" as an "introductory course" (template 1) in a module called "modelling and simulation" and at the same time as "special lecture" (template 2) in another module called "introductory courses for foreign students". The module "modelling and simulation" is part of the study programme in "Geoinformatics", the other one part of the study programme "Erasmus students".

           Modules may have several mandatory and/or optional templates and in order to identify the different courses as pertaining to a specific templates, the templates within a module are numbered. It may also be that a specific module can be used in more than one study programme.

           What I have so far works fine in terms of assigning the different courses via a long match list to a specific study programme. However, now I need a way to produce a pdf to show the mappings in a specific way. And what works fine in one direction suddenly does not work in the reverse direction, i.e. from study programme to courses. The bottleneck seems to be the match table from templates to modules (red in the picture), where in addition to the two foreign keys kf_modul_ID and kf_template_ID, I also need the template ordering number called Z_Template_OrdNr in the appropriate module.

           I cannot figure out how to include the three identifying fields into one primary key (I do have an auto-numbered kp for identification). Matching the tables works fine with the two foreign keys, but the identifying piece in that match is the ordering number which is at the moment an attribute of that match - this is certainly not how it should work. Without the ordering number, the path from module to template has several solutions and (as is proper) Filemaker uses the first matching one.

           Below is how my database looks at the moment. I apologise for the German / English mixture of terms. As you can see I have 5 "data tables" (Personen, Lehrveranstaltungen (courses), Templates, Module and Studiengaenge (study programmes) and 4 match tables (Z_Dozierende_LVA, Z_LVA_Template, Z_Template_Modul, and Z_Modul_Stg) because all of the relationships are many-to-many.

           I'm working with Filemaker Pro 11.0v4 and the database is uploaded to a server with Filemaker Server 11.

           Can you help me?

           Thanks, Sabine

      database.jpg

        • 1. Re: multiple primary keys
          philmodjunk
               

                    However, now I need a way to produce a pdf to show the mappings in a specific way.

               That seems to be the main question to answer here. But it is not clear to me what exactly you want to do with this data model that you are unable to do.

               

                    I cannot figure out how to include the three identifying fields into one primary key

               Combining data from multiple tables should not be needed to produce a primary key and you already have primary keys defined in your tables.

               I suggest that you make a detailed description of what you want to do with this data model that you cannot do.

          • 2. Re: multiple primary keys
            timpf

                 Hi PhilModJunk! The pdf I need to produce shows the description of the module on one page and on the subsequent page all the courses ordered by Template Order Number. Have a look at the attached files. The really important pieces are the matching of the numbered templates with the (numbered) courses - the numbers are necessary to do the matching. The images are from an earlier version of the database, which had other problems.

                 Image 1 : reproduction of pdf (easy to do)

            • 3. Re: multiple primary keys
              timpf

                   image 2: reproduction of pdf of the facing page showing the matching courses (can't work out how to do this - when I try I only get courses matching one ordering number at the time, not all of them in a single list)

                    

              • 4. Re: multiple primary keys
                timpf

                     As for what I want to do: I need the matching to work, which it does not at the moment.

                     Starting from a module, e.g. RsGeo-11-DID2, which is described in image 1 above, I need to figure out which courses (in Lehrveranstaltungen see image 0) are matched to which ordering number, i.e. to the template found under that number in the module definition. At the moment, the match works through the foreign keys from module_ID to template_ID, but using this match I loose the information which ordering number template_ID is assigned to in module_ID. How can I prevent loosing this?

                     Sabine

                • 5. Re: multiple primary keys
                  philmodjunk

                       I don't understand what you mean by "ordering number" and how it is supposed to work. Not being able to read your language also makes this difficult.

                       Modules--<join>----Templates-----<Join>-----Courses

                       Listing all courses or all templates for a specified module is not a problem. But a given course can be listed with any number of different templates associated with the current Module and vice versa. That is as a direct result of many to many relationship linking Templates and Courses

                       I suspect that your "ordering number" is supposed resolve that in some manner, but have no idea how that would work here.

                       So I see in your report that a record in Modules is the "parent" record for this report. Underneath that appear to be 4 courses listed. But the rest of the data on that first page is a mystery to me. I cannot tell what it represents.

                       The next page seems to list a number of courses under some kind of header--info about the Module?

                       But "template order number" is a mystery here as to where it is in your example, from what table it is stored and it's intended purpose.

                       I suspect that you'll need a different set of table occurrences and relationships in addition to those that you already have in place that avoid some of the many to many relationship issues by temporarily linking to a single Module--the Module selected for your report.

                  • 6. Re: multiple primary keys
                    timpf

                         Hi Phil, thanks for your patience!

                         The module that is shown in image 3 in the report is pre-defined, i.e. the name is given and the structure of the module, i.e. its constituent templates, are also fixed in legal documents. I just repeat under the heading of "Aufbau des Moduls" which means structure of the module what the structure looks like. In this case this module has 4 parts, i.e. the students need to take 4 courses of the specified types (that is what I call templates).

                         This is what the Ord.Nr. on the left means. #1 they need to take a course of the type "Seminar Fachdidaktik", #2, they take another "Seminar Fachdidaktik" and so on. Some modules have up to 19 different courses (electives usually). Now each semester the names of the courses which are of type "Seminar Fachdidaktik" change, depending who is giving them and other factors. In addition, students can choose from the courses of a specific type, which one they want to take for credit. This is the reason I need to list all courses of type X which can all be taken to satisfy the requirement of having taken this course #1. Now in order to list all potential courses for one module, I need to order the courses according to the #1 to #4 as defined in the structure. This is the list of OrdNr. in image 4 (which will follow).

                    • 7. Re: multiple primary keys
                      timpf

                           What I call type in the description above are the records in the Template table, or templates to use another name.

                           Image 4 shows the OrdNr., i.e. the # a specific course is allocated to and then additional information about the course such as the course number, its name, its teachers etc.

                            

                      • 8. Re: multiple primary keys
                        philmodjunk

                             So to complete this module, the student is required to select one course for each of the four listed "Templates"?

                             But in what table do you store that "number"?

                             My best guess is that this should be a value stored in a field in the Module to Template Join table: z_template_modul.

                             It looks like the second page of your report can be produced from a list view layout based on the Template to Course join table: z_LVA_Template. Go to Related records can take you from a Module record on your Module layout to all the courses for all the templates linked to that Module. But as you indicated in your first post, the problem is in then "linking back" to the correct Module record.

                             One way to do that is to add a new Tutorial: What are Table Occurrences? of Modules and link it's primary key to a global field defined in z_LVA_Template. The same script that uses Go to Related records to pull up this found set can also set that global field to the current Module's primary key. You can then refer to fields in that new occurrence of Modules to include Module data in your report.

                             But that still leaves out the template numbers in z_template_modul.

                             So now for a new question: what version of FileMaker are you using?

                             If you are using version 12 or newer, we can use ExecuteSQL to get to the correct template number for each course. Without it we'll need yet another set of table occurrences to get to that value.

                        • 9. Re: multiple primary keys
                          timpf

                               Hi Phil,

                               so far I stored that number in the Z_Template_Module table. 

                               Yes, I can produce the list of all the related courses through Z_LVA_Template, but I do need the OrdNr. in order to indicate which course can be taken for which "template". Using a List View I can even produce an ordered list of course lists, but trying to export this to pdf only produces one list per Ord.Nr. per page, and I need them all on the same page as in image 2.

                               I'm using Filemaker 11.0v4.

                               I do not have a lot of experience with table occurrences - so how do you imagine this could work without ExecuteSQL? 

                          • 10. Re: multiple primary keys
                            philmodjunk

                                 It'll be something of a complicated mess, which is why I didn't try to outline that approach if ExecuteSQL was available to make life simpler for us.

                                 In which table is Ord.Nr. defined?