6 Replies Latest reply on Jun 11, 2014 1:37 PM by philmodjunk

    best way to set up a relationship

    FeldyNYC

      Title

      best way to set up a relationship

      Post

           I have a included a screen shot of four tables.  I would like to have an art fair.  I would like to choose a gallery at the art fair and then I do I have the choice of artists to choose from that represent the gallery in a drop down. (I have that relationship set up elsewhere before).  Then I would like to create a note.  What is the best way to do this?  What table should I reference?  How should I set up the relationships?  Thanks.  Feldy

      Screen_Shot_2014-06-11_at_12.57.36_PM.png

        • 1. Re: best way to set up a relationship
          FeldyNYC

               full gallery table

          • 2. Re: best way to set up a relationship
            philmodjunk

                 For any given Art Fair record, you can link to many different artists. Any given Artist might link to many different art fairs. This is a many to many relationship.Start with these relationships:

                 Start with these relationships:

                 Art Fairs-----<Artist_ArtFair>-----Artists 7

                 Art Fairs::__pkArtFairID = Artist_ArtFair::_fkArtFairID
                 Artists 7::__pkArtistID = Artist_ArtFair::_fkArtistID

                 You can place a portal to Artist_ArtFair on the Art Fairs layout to list and select  Artists 7 records for each given Art Fairs record. Fields from Artists 7 can be included in the Portal to show additional info about each selected Artists 7 record and the _fkArtistID field can be set up with a value list for selecting Artists 7 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

                 When it comes to Notes. Are you planning on recording notes on each artist that participates in the fair or notes on the art fair as a whole? A text field in Artist_ArtFair can record notes on each Artist specific to their participation in that art fair. Or you can link a notes table to Art Fairs in order to record multiple notes about the Art Fair as a whole.

            • 3. Re: best way to set up a relationship
              FeldyNYC

                   Thanks, what if I have a specific gallery to see who is showing that artist at a specific fair?  I am in the process of following your post.   Thanks for your help, you are very efficient and super knowledgeable.

                   Feldy

              • 4. Re: best way to set up a relationship
                philmodjunk

                     Each ArtFair record should be linked by Gallery ID to the gallery hosting the Art Fair.

                     There are multiple possible options. One method is to use a pair of "Master Detail" portals on your Gallery layout. The "master" portal would list all art fairs linked to that Gallery. Clicking a button in the portal row of the Master portal then updates the "detail" portal to list the artists showing works in that particular art fair.

                Need layout solution for nested portals...

                     PS. Questions about how to set up many to many relationships occur so frequently in this forum that I set up a layout and calculation fields where I can enter a poster's table and ID field names and a calculation produces the text that I then post as a response.

                • 5. Re: best way to set up a relationship
                  FeldyNYC

                       There are many art fairs/ year - each with a specific name and many galleries.  There could be 2 or more galleries that represent the artist, that is why I had asked that.  I will look at the link you provided on parents, children and grandchildren.  thanks, feldy

                  • 6. Re: best way to set up a relationship
                    philmodjunk

                         IF you are not referring to a gallery that hosts the art fair and thus have more than one gallery to link and they link to the artist rather than the art fair, you'll need different relationships for that part of your system than one I have described thus far.

                         To start, you'll need a many to many relationship to establish the link between artist and gallery as each gallery can be linked to many artists and each artist can be represented by more than one gallery.