6 Replies Latest reply on Oct 16, 2013 12:11 PM by NikkPilato

    Using multiple table relationships/auto populate issues



      Using multiple table relationships/auto populate issues



           Hi all. It's been many a year since I frequented the forums, and now I am back to humbly ask for help with my new database. I am a music teacher, and many years ago set up a database for the following categories/tables:
           1. Student Information (name, phone, email, etc.)
           2. Instrument Inventory (brand, model, serial #, etc.)
           3. Lockers (locker#, combination, size, etc.)
           I have a new job now, and have recreated the database using my brand new copy of Filemaker 12...I decided to get adventurous and make life easier for myself…while harder at the same time. This is what I want to be able to do:
           1. When in the Student Information table, I want to be able to enter all their necessary information. Additionally, I have set up a relationship to the Locker table so that if a locker has been checked out to a kid, it shows up in the Student Info table (but it cannot be modified from here, only from the Locker table). So far, so good…this is working well, and I have no issues.
           2. When in the Inventory table, I want to be able to check out an instrument to a student and see what locker the instrument is in. To that end, I have linked the Inventory and Locker tables together. Right now, when I enter a locker # for an instrument, it auto-populates the lock's serial # and combination (and those fields are not editable in this table). This works like a charm so far. I have also linked the Inventory and Student tables together. Right now, if I type in a student's name and he/she already exists in the Student table, it auto-populates the rest of their data (very handy!).
           I run into the following problem:
           If the student does not already exist in the student table, I am able to enter their name, but am unable to add their email, phone, and student ID because those fields are set to auto-populate from the Student Table via the relationship. I don't want to lose this functionality, but I also want to be able to enter their necessary info from the Inventory table if it does not exist already, WITHOUT having to switch back over to the Student table. 
           3. In the Locker table I have set up relationships not only to the Inventory table but to the Student table as well. I've set it up so that when I enter an instrument's serial # to a locker, it auto-populates the rest of the info. So far, so good.
           I also have set it up so that, like the Inventory table, I can enter a student's name and the rest of the field auto populates. However, I have the same problem. I want to be able to enter the student information here and not have to go to the student table. Because I have the email/ID/phone fields set to auto populate via the relationship to the Student table, I cannot edit here. 
           Is there a better way to do what I want to do? Any help at all would be appreciated.

        • 1. Re: Using multiple table relationships/auto populate issues

               It would be very helpful to see the actual relationships that you have defined--one way is to upload a screen shot of Manage | Database | Relationships.

               And what exactly do you mean by "auto populate". If you have auto-enter settings copying this data from the student table, then this would appear to be the wrong approach to use. Instead, you should be using your relationships to simply place the fields from the related student table occurrence onto your layout. Then, selecting a student should establish a link to that student's record in the student table and any fields from that table can be set up to be editable if you want to do that from the current layout.

               If you are copying the data, you face a real nightmare trying to update everything should there be a need to change that student's data as it exists in multiple places through out your system.

               If you are not copying data, then some aspect of your relationships is not set up like it should be.

          • 2. Re: Using multiple table relationships/auto populate issues

                 "And what exactly do you mean by "auto populate"."

                 I think I am doing what you described, perhaps I just used the wrong terminology. Here is a screenshot of my relationships...



            • 3. Re: Using multiple table relationships/auto populate issues

                   You'll have trouble if you get two students with the same exact name, if a name is entered incorrectly and you don't immediately correct it or if a student changes their name (it can happen with kids via a step parent adoption or such).

                   So the first recommendation would be to define an auto-entered serial number in the Student information table and use it to match to records in other tables in place of the name. Then names changes/corrections don't break your links to other tables.

                   But that's a new issue for you to think about. Let's focus in on one layout and see where the problems lie.

                   I'm guessing here, that what is happening, say on the lockers layout is that you are entering the name of a new student in the Lockers::User Name field, but then can't enter data into any fields from the student Information table such as: "class". If so, you can double click the line from Lockers to student information to open up an Edit Relationship dialog and there you can click "allow creation of records via this relationship" for the student information table. Then, when you enter data into the class or any other field from Student Information, a new related record will be created in Student Information and you can enter data into the fields from that new record.

              • 4. Re: Using multiple table relationships/auto populate issues

                     Ok...I got that working, thanks!

                     Can you tell me a little about the first part of your response? Is this basically a student number that *I* give them when entering their information? Or is there a way to get a random number code generated for them? 

                     Or...perhaps it would just be wisest to use their student ID# as the main field relationship....

                • 5. Re: Using multiple table relationships/auto populate issues

                       Neither. It's a serial number--not random that is automatically entered into this field in each new record. (Any existing records can be updated using Replace Field Contents.) Go to manage | Database | Fields, select or create a number field. Double click it to open Field Options. Then select the option for "Serial number" on the auto-enter tab.

                       The ideal primary key (that's what we are actually discussing here):

                  1.           Has no meaning other than that it uniquely identifies one specific record in one table. No secret code such as the year, an last name initial or any other such should be permitted to be "encoded" into it.
                  3.           Is never, ever changed.
                  5.           Must absolutely be Unique. (Random numbers can't be guaranteed to be unique, you might get the same number twice.)
                  7.           Is generated internally in the database itself. Values from sources outside the db--such as their student number, can't be guaranteed to meet the requirements of 1 2, and 3.
                  • 6. Re: Using multiple table relationships/auto populate issues

                         Many thanks for all the useful comments, Phil. Much appreciated!