5 Replies Latest reply on Nov 8, 2013 10:33 AM by philmodjunk

    Puzzling Relationship/Portal Set up



      Puzzling Relationship/Portal Set up


           Hi all,

           I want a relationship/portal set up that I just can't get my head around, any help would be great.

           Basically, I have a database section for Exhibitions. In the Exhibition Detail layout, I want a portal that shows the contact details of all the Participating Artists. This seems easy to me if contacts just had one 'on/off' 'Exhibition' value, but they could have many, meaning I have created a seperate table called 'Exhibition History'.

           This table links with the Contacts table and stores details on all of the exhibitions that contact has participated in. What I want to make happen is for the portal in the exhibitions layout to look at the exhibition history table, pull out any exhibition titles that match its 'Exhibition Title' record, then reference this back to the contacts (using the match ID) who are participating, displaying their names, phone numbers and emails etc.

           If you know any easier way to do this or have any suggestions on how to implement it I would love to hear them! 



        • 1. Re: Puzzling Relationship/Portal Set up

               Some of the details of your current set up are not fully clear. You may need to adjust what I am about to suggest to get it to fit your actual setup:

               It appears that you have many to many relationship. A given exhibit can have contributions by many artists. A given artist can exhibit their work in many exhibitions.

               Start with these relationships:


               ExhibitionDetail::__pkExhibtDetailIDID = ExhibitionDetail_Artist::_fkExhibtDetailIDID
               Artists::__pkArtistIDID = ExhibitionDetail_Artist::_fkArtistIDID

               You can place a portal to ExhibitionDetail_Artist on the ExhibitionDetail layout to list and select a Artists record for each given ExhibitionDetail record. Fields from Artists can be included in the Portal to show additional info about each selected Artists record and the _fkArtistIDID field can be set up with a value list for selecting Artists records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Puzzling Relationship/Portal Set up

                 Ah that seems like exactly the kind of thing I want.

                 I have my Exhibitions Table (playing the role of your ExhibitionDetail) and a Contacts Table (Artists), so do I just need to create the ExhibitionDetail_Artist table and create the ids to link them up?

                 This seems very simple but as I have all of the contacts and exhibitions already inputted it seems hard to get my head around (I started using filemaker a few weeks ago so step by step child-like descriptions would be greatly appreciated!)

                 Thanks for your help 

            • 3. Re: Puzzling Relationship/Portal Set up

                   You'll need to add that "join" table and populate it with data in order to link an exhibition record to the artists that contributed to the exhibition.

                   Here's a demo file that matches up contacts to events (think artists to exhibitions) that may be helpful to look at: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   Assuming that you are using FileMaker 12, open this file from the File menu to get a copy converted to the .fmp12 format.

              • 4. Re: Puzzling Relationship/Portal Set up

                     Perfect, I've got it working! Thanks Phil. Now on to trickier things:

                     Is there a way I can have a portal where when one begins to enter the name of a contact or exhibition it autocompletes based on the database and fills out all the other info?

                     Eg. If I was viewing Exhibition 1 and wanted to add 'Jon' I could start typing J and it would give the option to autocomplete and fill out Jon's number, email and website.

                     Likewise, if I was looking at Jon's record, I could start typing 'Exhibi-' and Exhibition 1 would pop up.

                     Is this easy to implement?

                     Thanks again,


                • 5. Re: Puzzling Relationship/Portal Set up

                       "Easy" is a slippery term. "Easy for me" may not mean "Easy for you". wink

                       Here are some demo files that demonstrate different auto-complete style methods for looking up data. All require scripting as well as relationships to implement. For files with .fp7, you can open them from FileMaker 12's File menu to get a copy converted to the .fmp12 format:

                       A "name look up" drop down list where auto-complete works but the ID value is looked up and used to link records instead of the name:

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

                       "Search portals" that update with each keystroke to only list matching related records with a button to click to select that item as well as two more auto-complete enabled drop downs: