6 Replies Latest reply on Sep 16, 2013 2:19 PM by philmodjunk

    Help with Relationships

    deraymen

      Title

      Help with Relationships

      Post

           I am trying to setup the following:

           1. I have a parts table

           2. I have an Engineering master table, which I set yo have multiple engineering masters per Part (to address rev changes etc)

           2. I have setup an Operations table, which will have multiple operations for each Engineering Master.

           3. I have setup a RawMaterial table.  This is setup so that for each Operation, I can specify the raw materiasl(s) need to be issued.

           I set-up the relationships, by selecting to allow creation and deletion of entries within the child table.

           I am frustrated that everytime I create an entry in teh child table, it appears to create an entry in teh parent table.  For example, when I create an entry in teh Raw Material table, I see that a new entry has been created in the Operations table..  It is almost as if the  relationships are all one-to-one.

           On the relationships chart, I keep showing "crows feet" on both ends, and I can't figure out how to get it resolved, so it is only the "crows-feet" on the child side.

           I am sure I am doing some silly thing wrong but I can't seem to understand what.  We used to have Filemaker 5 and I am trying to set-it up in FM12.  So there is a bit of a learning curve issue here as well..

           Any help will be appreciated.

           Thanks

        • 1. Re: Help with Relationships
          philmodjunk

               What I understand you to be describing here:

               RawMaterials>------Operations>-----EngineeringMaster>---Part

               With those relationships, the match fields in the relationships should be set up as:

               RawMaterials::_fkOperationID = Operations::__pkOperationID
               Operations::_fkEngeeringMasterID = EngineeringMaster::__pkEngineeringMasterID
               EngineeringMaster::_fkPartID = Part::__pkPartID

               All the fields in my example that start with __pk should be defined as auto-entered serial numbers. All the fields that start with _fk should be defined as simple number fields. (You may have ID's from sources external to the database, such as a part number for your parts, don't use those in these relationships.)

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               

                    everytime I create an entry in teh child table, it appears to create an entry in teh parent table.  For example, when I create an entry in teh Raw Material table, I see that a new entry has been created in the Operations table..  It is almost as if the  relationships are all one-to-one.

               That should not happen automatically even in a one to one relationship. This suggests that your layout is not correctly designed for the data entry that you want to do and you may also have enabled "allow creation..." for the parent table--which is not needed, it is only needed for the child table and then only for certain specific layout designs.

          • 2. Re: Help with Relationships
            deraymen

                 Thank you for the response.

                 I have establsihed the primary and foreign keys as you described.  Please see attached.

                 Let me add another point.

                 The way I have set-up the layout is to have it based on the PARTS table.

                 Then I have created tabs for each of the other entries.

                 Engineering Master has its own tab, but within the tabbed view, I have added a portal to show the details of the Engineering master.

                 Another tab for the Operations - with a portal for the operations list..

                 etc...

                  

            • 3. Re: Help with Relationships
              deraymen

                   Sorry - forgot attachment

              • 4. Re: Help with Relationships
                philmodjunk

                     The relationship between PartFM and ENgMasgerFM is not correct for a one to many relationship from Parts to engineering master records. Note how you have a lack of "crows feet" in the diagram. What you have does not depect a relationship of one Part record to many Engineering Master records.

                     What is the purpose of the Sequence_NO fields in your relationships?

                     I can now see why you get a new record in a child table, you also get a new record in another table.

                     If you create a new record in RequirementFM from a layout based on PartFM, matching records must exist to connect the current record in PartFM to a record in EngMasterFM to a record in OperationFM to the new record in RequirementFM. If you enabled "allow creation..." for each of the tables in this chain, any records in that chain of linked records that are missing will be automatically created.

                • 5. Re: Help with Relationships
                  deraymen

                       OK:

                       1. How do I set the relationship to be a one-to-many?  Or in other words - how do I establish the "crow's feet" as I would like it to be -  I can't seem to figure that out.  I went and re-checked the relationship between teh Part and EngineeringMaster, and it is set to allow creation and deletion of records on the Engneering Master side, but not on the Part side. My assumption is that is teh way the parent-child relationship is established - with teh crow's feet showing up on the child side.

                       2. The Sequence Number is to make sure that the Requirement table entries (which have the raw part info) have a matching Operation line.  In other words, if I have a five step operation, I don't want to have raw part assigned for operation step 6.  If there is another way of defining it, that'd help.

                       3. As far as layout, I wanted to have one layout with the tabbed sections for all teh tables involved in teh process.  I'd rather do this than have multiple separate layouts.  Are portals limited to just the next linked tables where there is a relationship established?

                  • 6. Re: Help with Relationships
                    philmodjunk

                         1. see my first post in this thread. I gave you examples of one to many relationships for each of your tables.

                         2. You may or may not need an additional  or different relationship. I don't know enough about how you need your database to work to tell you at this point. Would I be correct that for a given Operations record, you can have many values in your Sequence field for many steps in the operations process? The current relationship will only allow you to see/edit/create/delete Requirement records for the sequence you have currently selected/entered in the OperationFM::Sequence No field. You won't be able to see all the Requirement records for the entire Operations record at one time without adding an additional relationship to a different occurrence of Requirements that does not match by this additional pair of fields.

                         3. Yes, but you will either need to change the design of your layout--possibly adding more layouts in place of your tab control or modify the relationships. The relationships you have (or the ones that I show in my first post) do not support the current layout design. Note that it's possible to simulate a table control but clicking what looks like a tab control "tab" is actually  a button that changes layouts. I'm not suggesting that at this time--just pointing out that you have a lot of options for how you design your layout or layouts.

                         4. With your current set of relationships, the problem is establishing which parent record should be the parent of a newly created child record when you are creating something other than an EngineeringMaster record on your layout. If you create a new Operation record, clearly it should be linked to the current Part record. But which of many EngineeringMaster records for that part should be the parent of this new operation record?

                         How you answer that question will then drive my suggestions for how to alter your design to get what you want.