3 Replies Latest reply on Dec 22, 2010 8:22 AM by philmodjunk







      First a little background, I am a new database user, I own a jewelry making business.

      I have 2 tables, one is for the parts that I use to make my jewelry and the other is for the jewelry itself.

      I need a way to have multiple jewery parts lised on the jewelry layout, with their descriptions and photos.

      I have joined the part number field on both tables

      I have tried adding a field for the part number and have FM look up the description and the photo, it worked.

      When I copied those fields so that I could add another part number things went wrong.  After I tried to enter a second part number it changed them both to the second one I added.


      Any advice?



        • 1. Re: relationships

          Hello Jenifer,

          Here is my suggetion to you, Since you have two tables i.e. Jewelry and Jewelry parts, the relationships you want to establish among them should be like Primary key of Jewelry --> Foreign key of Jewelery parts.

          Means there should be a field like Jewelery Number present in Jewelry parts table as foreign key which is the primary key of Jewelry table. You just copy the Jewelry Number of Jewelry table and paste at Jewelry Number of Jewelry parts Table. Now just take relationships among them by joining Jewelry. Jewelry Number= Jewelry parts. Jewelry Number so that you can able to get which are the jewelry parts of a particular jewelry.

          Now to view the parts which are related to a particular jewelry in the jewelry layout you can draw a portal based on Jewelry parts table having all fields of  Jewelry parts table. It will show you all the parts related to a particular jewelry.

          I think it will work correctly.



          • 2. Re: relationships

            Table: Jewelry          Table: JewelryParts
            _kp_JewelryID    =   _kf_JewelryId
            (auto ent serial)       _kp_JewelryPart(auto ent serial)
                                            "double click on the relationship
                                            and check (allow creation in this table..."

            Make a portal in the Table: Jewelry...this portal will be based on table:JewelryParts.

            Also to keep the data intact in jewelryparts from changing...do a "lookup" for the information in jewelry parts

            Hope this helps usually a diagram is a lot easier to follow than just words



            • 3. Re: relationships

              Here's a demo file created by Comment that you may want to look at:  


              Think of Invoices as your jewelry table, Line Items as your list of parts that make up an item of jewelry and Products as a master list of parts from which you look up info while documenting the design of a given item of jewelry.