5 Replies Latest reply on Mar 15, 2014 6:57 PM by philmodjunk

    To Join Table or not to Join Table

    EdwardMcgee

      Title

      To Join Table or not to Join Table

      Post

           Grettings! I learn a lot here.

           Using FM Pro 12 on iMac OSX Mavericks

           My database is of an art collection. I want a portal to show me the titles bought from a certain dealer, but cannot get it to work. It looks like there is a relationship issue. Have I used the wrong table as the join table? Should it be TITLE>---DEALER---<ACQUISITION? Or should I use a join table at all?

            

      Screen_Shot_2014-03-15_at_2.04.12_PM.png

        • 1. Re: To Join Table or not to Join Table
          philmodjunk

               Does the same record in Title need to be linked to more than one record in Dealer?

               Does the same Dealer need to be linked to more than one record in Title? (That one seems an obvious yes, but let's be sure...)

               If your answer to both questions is "yes", then you need a join table and it is correctly set up. If it's not working for you the problem lies with either your layout design or how you select/enter values in the fk fields Acquisition.

               If your answer to either question is "No" in every possible situation, then you do not need a join table at all.

          • 2. Re: To Join Table or not to Join Table
            EdwardMcgee

                 Thank you.

                 Dealer needs to be linked to more than one Title; Title is linked to only one Dealer. Which means the acquisition info should really be in the Title table, yes? How can this be done?

                 As to the layout issue, Acquisition::_fkArtDealerID is a drop down list referencing Dealer::Art_Dealer_Name as the first field and Dealer::__pkArtDealerID as the second field. Acquisition::_fkArtworkID is an Edit Box. Both are numbers.

            • 3. Re: To Join Table or not to Join Table
              philmodjunk

                   In terms of your layout design, you have your fields in your value list reversed. The __pkArtDealerID field should be specified for the value list's first field, not the second.

                   You are correct in what you need to do to eliminate the Acquisitions table. You need to create corresponding fields in Dealer and then import the data from Acquisitions into Dealer. If you have FileMaker Advanced, you can copy and paste field definitions from Acquisitions to Dealer.

              • 4. Re: To Join Table or not to Join Table
                EdwardMcgee

                     Thanks again. At first glance it looks like my import worked, although I put the purchase details in the Title table rather than the Dealer table; wouldn't that have created duplicate Dealer records with new serial numbers?

                • 5. Re: To Join Table or not to Join Table
                  philmodjunk

                       Yes the data should go in Title as it is specific to the item and not the dealer.