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.