1 Reply Latest reply on May 24, 2013 2:34 PM by philmodjunk

    Newbie question around relationships

    NeilBarman

      Title

      Newbie question around relationships

      Post

           Hi,

           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?

            

           Thanks!