8 Replies Latest reply on Nov 18, 2014 12:18 PM by CamillaBarker

    Relationship design

    CamillaBarker

      Title

      Relationship design

      Post

      Hello All, I recently received lots of help for this matter on this forum and it would all be working fine if I hadn't come across a further complication to my issue that I am not sure how best to make work. Please bear with me as this is my first time using Filemaker Pro. Please see the image with this post to see my database.

      My issue is with the Table / TO "ArtistPhotos". Here's what I would like to acheive:

      -All ArtistPhotos have one Artist (hence the relationship created in the diagram)

      -Some ArtistPhotos may have one Artist as well as one Gallery

      -Some ArtistPhotos may have one Artist as well as one Exhibition but not a Gallery

      -Some ArtistPhotos may have one Artist, one Gallery and one Exhibition

      If I make second occurrences of the Gallery and Exhibition tables as parent tables and link them to the ArtistPhotos table as a child table which is what I think I am supposed to do, please might you be able to help me understand how to present the information in record creating portals on the various layouts so that there are fields showing the gallery, artist and exhibition the photo is linked to but that when there is no link, that particular field just remains blank and does not have to be filled in. Equally in ArtistPhotos I'd like to be able to sort by photos linked to galleries, or photos linked to Exhibitions, or both. (They will all be linked to an Artist.) I bilieve that if I set the relationships all up correctly this last sorting issue will be easily self expalanatory but just in case it isn't I'd welcome any tips.

      Your help would be much appreciated,

      Thank you,

      Camilla. 

      Screen_Shot_2014-11-18_at_12.14.24.png

        • 1. Re: Relationship design
          CamillaBarker

          PS: my issue to date has been that as soon as the Artist is linked to the ArtistPhoto, it then lists automatically the first Gallery and exhibition that Artist is also linked to rather than linking only to to Artist and then separately to whichever gallery and/or exhibition is specified, or to neither gallery nor exhibition if none are specified. 

          • 2. Re: Relationship design
            philmodjunk

            In your relationships, as designed, your ArtistPhotos records are not linked to ANY gallery. The artist is linked to galleries and there's no relationship to link a given photo to a given gallery.

                                 Artists-----<ArtistPhotos>------------Galleries
                                                        v
                                                        |
                                                  Exhibitions

            Artists::__pkArtistID = ArtistPhotos::_fkArtistID
            ArtistPhotos::_fkGalleryID = Galleries::__pkGalleryID
            ArtistPhotos::_fkExhibitionID = Exhibitions::__pkExhibitionID

            Your ArtistPhotos table is your actual join table linking artists to galleries and exhibitions by the photos shown in those locations/events.

            A portal to ArtistPhotos on your Artists layout can include fields from the Galleries and Exhibitions tables or you can set up a list view layout based on ArtistPhotos and include fields from all three of the other tables in this layout.

            • 3. Re: Relationship design
              CamillaBarker

              Thank you very much for your answer. 

              If I relate everything as you suggest, is there a way to create a portal in Artists to show the Galleries the artist is showing in without it repeating the same gallery many times for every photo that is joined to that gallery? For now I created a portal to ArtistPhotos (renamed PhotosAndJoin) as in the attached image. The two drop down boxes are for creating a new record when needed by stating the FKartists and the FKGallery but this way it gives a line entry for every time a photo has been linked to a gallery so there just ends up a long list of the same gallery. I would like each different gallery to appear only once. Is there a Filter formula that could resolve this please?

              All help is much appreciated. 

              • 4. Re: Relationship design
                FilmUser

                Is a gallery ever associated (related) to an artist, or a photo, independent of an exhibition? (eg representation or residency)

                Or is the relationship always in terms of an exhibition?

                • 5. Re: Relationship design
                  CamillaBarker

                  Hi Filmuser,

                  Yes a gallery will be related to an artist independent of an exhibition. 

                  A gallery can also be related to an artist without a photo but rarely.

                  An artist can have many galleries, many photos, and many exhibitions. 

                  Within all the photos, galleries and exhibitions an artist may have, some of those photos may be related to some of those galleries and / or some of those exhibitions. 

                  A photo will have one artist and it may have a gallery and / or and exhibition.

                  So no, the relationship is not always in terms of an exhibition. 

                  My original relationship diagram shown above was working great until I realised I needed sometimes relate both galleries and exhibitions (as "parents") to photos (as the "child")

                  Any tips?

                   

                  • 6. Re: Relationship design
                    philmodjunk

                    With the relationships that I mapped out earlier, you can place portal to Galleries on your Artists layout and each gallery that is linked to at least one photo by that artist will be listed in that portal and without duplications.

                    • 7. Re: Relationship design
                      FilmUser

                      You've clarified some things here. I have a data base for my own work, which has an additional Image table, whose records are either files or negatives, which then relate to Prints (which then have dimensions).

                      I'm inclined to think you might want a TO of Galleries to relate through Exhibitions (as in the venue of the Exhibition), which would be in the upper right corner of your pic, in addition to a separate relationship of the Gallery to the Artist or the Print. Managing multiple TO's for different relationships gets tricky, so I'm interested in seeing who else weighs in here, as I can usually make this work, but I'm not as experienced in this area as some others here (such as Phil).

                      • 8. Re: Relationship design
                        CamillaBarker

                        Hi Phil,

                        Thank you so much again, as you say using the relationships you mapped out a Gallery, as opposed to a Photos, portal does the trick. 

                        Hi FilmUser,

                        Thank you, I will bear your comments in mind incase I come across extra challenges: and here I was with a perfectly functioning database right up until I wanted to link more things to photos!!

                        Thank you both for your time and help.