5 Replies Latest reply on May 15, 2016 6:57 AM by beverly

    Stuck with Joining Tables

    comrecce

      Dear FileMaker Community,

       

      I’m an absolute beginner with no knowledge at all regarding FileMaker.  All the knowledge I have so far is thanks to the YouTube channel of Guy Stevens.  I’m a big music fan and I was looking for a way to store the items I want to buy.  I created a number of Tables and I’m completely stuck on how to join the necessary Tables into the final Table.

      This is what I have done so far. 

      Created Tables:

       

      Artists: Id, Artist, Genre, Picture

      Discography: Id, Artist, Title, Type, Release Date

      Track Listing: Id, Artist, Title, Track_Number, Track_Title

      Releases: Id, Artist, Title, Year, Label, Catalog, Notes

       

      The Type field in the Discography Table is either an Album, Compilation, Single & EP or DVD/Video

      The Label field in Releases is of course the Record Label

      The Catalog field in Releases is actually the order number

       

      The first problem I encountered is the container/Picture field.  I can insert pictures but I want them the same size as the files I need to insert. Is there a way that they automatically take the original size?  Another problem is the Track Listing.  When is it only one CD for example than there is no big deal but for instance take a box containing like 15 CD’s how could I manage this?

      The biggest problem for me is to get the necessary data from this Tables into the final Table called Wishlist.  In attachment I placed an Excel file to show how I more or less see the final outcome of this Table.  It would be nice that the output could be shown in three tabs (info, Track Listing, Releases).

      Can someone please help me out?  Please know that I would really appreciate this.

       

      Kind regards.

       

      P.S.  please note that the file in attachment is just a quick sketch. 

        • 1. Re: Stuck with Joining Tables
          GuyStevens

          I think your tables are not optimal.

          You make a table for discography.

          But that is something that you get automatically when you list an artists releases.

          So I would make the release table a little bigger to also contain the following info:

          - Type / Format

          - Release Date

          - Label

          - Catalogue Number

           

          Then when you list all the releases of an artist you get their discography. There is no need to have a separate table for this.

           

          For a release with multiple cd's I would add to the Track listing table a field where you enter the cd number.

          So if you have a double cd release you simply list all tracks on cd1 and then all tracks on cd2.

           

          The container field is always going to have one size. It doesn't resize depending on what you put in there.

           

          The wishlist would simply be a list where you select a release from either a dropdown or a button on the Release layout that add's that release to your wishlist table.

          So your wishlist table contains a ReleaseIdFk field and maybe other fields like:

          - Date added to Wishlist

          - Remarks

          - ...

           

          All the related data can be gotten from the related tables.

          There is no need to duplicate data or to get a bunch of data from the other tables into the wishlist table.

          A simple Release ID will do the trick.

           

          Greetings Guy

          • 2. Re: Stuck with Joining Tables
            GuyStevens

            You actually don't have any join tables.

            Artists is related to Releases and that is related to TrackListings.

             

            Wishlist is a separate table that is related to (probably separate Table Occurrences) of the other tables.

             

            Greetings Guy

            • 3. Re: Stuck with Joining Tables
              comrecce

              Hello Guy,

               

              First of all I like to thank you for your quick reply on my question.  Because of my personal lack of knowledge it is sometimes hard to explain what one really want to achieve.  Next to that English isn't my native language.  I'll hope I'm not breaking any forum rules but I use reference websites like http://www.allmusic.com/ to do my research.  Purely as an example I like to use the band Blondie.  So I'll type in the search box, on top of the home page "Blondie" as search term, than I click on the Discography tab.  Next I click "All" to get the full overview of Blondie's Discography (Albums, Compilations, Singles & EPs and DVD/Videos).  Suppose that in the listing I click 1976 - Blondie than I get more information on that specific album.  I'm only interested in the Artist, the title, the release date and a picture of the album.  Next we have the track listing.  If you click on Releases you'll see an entire list of all the releases of that specific album. Thanks for the tip in case the album would contain several discs.  It would be nice that I could build my table Wishlist based on three tabs (Info, Track List, Releases) per record.  I'll hope this would give you a better understanding regarding what I'll hope to achieve.  All other listed information like Duration, Genre, Styles, Recording Date, Album Moods, Album Themes, etc.,.. have little or even no importance to me. I guess I confused terms like join and relationships in regard to tables.  You are right that Albums are related to an Artist, Track List and Release are related to  specific Albums, Compilations, Singles/EP or DVDs/Videos.  My main concern is how I'm going to initiate this whole process and create the necessary relations to all the tables.  Any help is more than welcome and much appreciated. 

               

              Kind regards.,

              • 4. Re: Stuck with Joining Tables
                GuyStevens

                Well, figuring out how the whole thing fits together, what tables you need and how you need to relate them is basically the job of the developer of the database.

                If that is you and you don't have a clue how to start this then I suggest you learn a little more about Filemaker first.

                You already found my Youtube channel and if you look at the descriptions of my video's you will find links to my Udemy courses, one of which is free.

                I suggest you learn Filemaker before you start your own project.

                You can't expect someone else to make your file for you.

                Once you get your head around the basics of Filemaker you will start to see what tables you need and how they need to be related to each other.

                I see you having about four tables:

                Artists

                Releases

                TrackListings

                and Wishlist.

                 

                The Releases are related to the Artist.

                The Tracklistings are related to the Releases.

                And the Wishlist is related to The Releases, Artists and Possibly Tracklistings. This is all through the Release ID.

                 

                So I hope that gets you on the way.

                 

                Greetings Guy

                • 5. Re: Stuck with Joining Tables
                  beverly

                  Guy is spot-on! I might start someone on database design by saying. Write it all down (on paper, for example). How is this done if this is a paper-based method? How is this done if this is a web-based method? What steps must be taken to get from here to there. comrecce, it seems like you are beginning to do that. You can begin this type of design at the same time you are learning FileMaker. Go through the tutorials on this forum, take training class if needed, figure out the "how it works" by any means. All the while thinking of your end goal. This will serve you well in the end. I spend as much time (or more) planning before every actually doing (developing) in the database. Countless hours are saved by not needing to back-track wrong paths. By all means run some of your planning by this forum. And/or get a 'coach'/'mentor' who will help you. It may cost, but far less than paying someone to do it and you will know what & why in the end.

                  beverly