Newbie question around relationships
I have a pretty simple question that I'm looking for help on. Let's say I have two tables - one with Contact Names (let's call this CONTACTS) and another with Student Classes (let's call this CLASSES). Each table has a unique identifier for every record and I have created a join table - let's call this ENROLLMENTS to create a many-to-many relationship between then.
Now, I have created a layout primarily based on my join table (ENROLLMENTS) and I want to use this layout for data entry. Here's where I'm getting stuck:
- If I enter in John Smith's CONTACTID, and I enter in a CLASSID number, I am able to create a new ENROLLMENT record that joins John to a particular class - no problem here.
- But for my users, I want to hide the primary fields altogether. I don't want my users to use CONTACTID and CLASSID numbers to create relationships between people and classes.
- I'm trying to find a way where I can do data entry such that if I enter in John Smith, and he already exists as a CONTACT, then FIlemaker should use his existing record to create the relationship with an existing CLASS.
- If I am entering in a NEW student, only then should Filemaker create a new student record and join it with an existing CLASS.
- When I type in a Student's name or a Class's name, it should auto-complete based on what is already in the database. Again, if it's a new, never before seen name, it should create a new contact; if autocomplete is used to successfully use a previous name, it should link it with that record instead of creating a new contact.
Now I realize that I can use pop-ups, value lists and drop-downs in filemaker. However, my CONTACTS list will be in the hundreds, so a drop down or a pop-up is no good. I need to use auto-complete and then match to existing or create new depending on if existing records are in the system.
Related question - let's say I have a DATES table and have a unique DATEID for every date on the table. If 5/25/2013 exists as a record on my table, and I am in data entry mode, and I pick 5/25 from a calendar dropdown, I want Filemaker to match it up with an existing DATEID instead of creating a new DATEID. But if I pick 6/17/2013 from the calendar dropdown and that date doesn't exist on my table yet, then I want it to create a new DATEID... is this feasible?
Can someone help me with this?