4 Replies Latest reply on Jun 4, 2014 7:58 PM by eliotr

    How add “join table” records?  (... when a parent MAY be absent)



      How add “join table” records?  (... when a parent MAY be absent)


           I have 3 tables :  Events —< People-at-Events >— People.  (This is a bit oversimplified.)

           On the Events form, I want to be able to enter People who attend that event.

           Ideally I’d like to easily enter people who are already in the People table, and People who are not yet in it.

           For those not yet in, I obviously need to create both a People record and a PatEvent record.

           Currently (which seems not ideal), I have 2 portals on the Events form.

           #1  PatEvent         (for entering existing people)

           #2  People  Related       (for entering new people)

           A)  If “Ann” IS already in the People table, I begin typing her name into the (last row of the) PatEvent portal-1; the autocomplete, drop-down field finds her; and she is added.

           B)  If “Bill” is NOT in the People table, I enter his name and other data in the People portal-2, and (after clicking elsewhere — [to commit the record?]), his name appears in both portal-1 and portal-2.

           C)  If I MISTAKENLY think Carl is NOT in the People table and begin to enter him (like Bill), I get a “Duplicate Name” warning, and switch to method A).

           If there is a better to do this — ie, to be able to enter both existing and new people — I’d like to switch to it in a new DB I’m creating.  My current approach does not seem very elegant.

           I imagine this issue is faced by many people, because this structure is pretty basic.

           Probably irrelevant, but in case it might be useful: 

           I actually have a 3rd portal on the Events form:

           #3  People—ALL  (via a Cartesian “X-join” to a 2nd table occurrence of People)

           ... and an alternative way to enter Ann (who IS in People) is:

           D)   I scroll down Portal #3 (ALL people), select her record, and trigger a scrip which transfers her data to the last row of Portal-1 similar to “A)” above.)

           Thanks for any suggestions.

        • 1. Re: How add “join table” records?  (... when a parent MAY be absent)

               A) seems a bit problematic all by itself. It seems you have linked People to the join table by the name field. (The Auto-complete feature won't work with a value list that enters ID numbers from a two column value list of IDs and names.) The potential issue that I see is that people can have the same exact name and there's nothing described here to "catch" that possible issue.

               I'd enter each attending person's name into a search field such as that found in the "Starts With" search portal in the demo file that I am about to share. Once the person's name appears in the portal--which can list more than just the person's name to help avoid errors due to two people with the same or similar name, you click that person's name to add it to a new record in the Join table. If the person's name does not appear in that search portal, you click a different button which creates a new record in People, enters the name into this new record and then creates a new join table record linking the current event record to this newly created people record. All you had to do was click a button.

               Here's the demo file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               It's in the older File format so users with FileMaker 12 or newer will need to use Open from FileMaker's File menu to open this file and produce a new copy of the file converted to the newer file format.

          • 2. Re: How add “join table” records?  (... when a parent MAY be absent)

                 Thanks, Phil!  It's going to take me a while to work thru what you sent, but I'm off to a good start.  What you've suggested (and the demo) look very helpful: I think I'm going to learn some new things!  Thanks, again.

            • 3. Re: How add “join table” records?  (... when a parent MAY be absent)

                   It has since occurred to me that the script used in this demo file could also be set to check for "new people" and then create the needed linked records. It may be much simpler to implement and closer to what you already have:

                   FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


              • 4. Re: How add “join table” records?  (... when a parent MAY be absent)

                     Thanks, Phil! --  This really stretched me!  I first figured out (most of) your SimpleNameLookupDemo.  Very Sweet!    But to handle 0, 1, or >1 (duplicate) matches in a many-to-many context, I had to integrate aspects of both your files.  Finally, it seems to be working.  (98%).  I can post it to drop box if you think it might be a useful resource and/or if you'd be willing to glance at it and make suggestions or answer a couple questions.

                     But regardless, many thanks!    -  Eliot

                     (Re: "Ann":  I had been using an ID-based join.  I was mistaken about using autocomplete.)