4 Replies Latest reply on Jan 25, 2015 10:12 AM by BrooksLeisure

    Multiple Related Data

    BrooksLeisure

      Title

      Multiple Related Data

      Post

      Hi,

      I am using FM12. I have 2 tables; Machines List and Parts List

      I would like to show related data in each table.

      Multiple machines can use the same part and each machine can use several different parts.

      For Exampe: if I have 5 machines and 3 parts

      Machine 1; Part 1 + Part 2 + Part 3

      Machine 2; Part 1 + Part 3

      Machine 3; Part 2 + Part 3

      Machine 4; Part 1

      Machine 5; Part 3

      I would like to show all the different parts available to a machine in the machine list and I would like to show all the machines that can use the part in the parts list. I presume the best way of doing this is via a portal, however I am unsure as to how to relate the tables.

      I have a Machine ID field in the machine list and a Part ID field in the parts list (each is set to auto enter serial number on creation)

      Once the tables are related I am also unsure as to how I will actually join the records, I have approx 500 machines and 100 parts so I guess I would be in the machine list side and add the part ID to each machine through the related portal?

      Many thanks in advance for any help

        • 1. Re: Multiple Related Data
          philmodjunk

          Start with these relationships:

          MachinesList-----<PartsList>-----Machine_Part

          MachinesList::__pkMachineID = PartsList::_fkMachineID
          Machine_Part::__pkPartID = PartsList::_fkPartID

          You can place a portal to PartsList on the MachinesList layout to list and select  Machine_Part records for each given MachinesList record. Fields from Machine_Part can be included in the Portal to show additional info about each selected Machine_Part record and the _fkPartID field can be set up with a value list for selecting Machine_Part records by their ID field.

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

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Multiple Related Data
            BrooksLeisure

            Just to check that im on the right lines here... I need to make a new table (machine_part) with one field (__pkPartID) as well as add a new field to my parts list table (_fkMachineID). And then relate as above?

            • 3. Re: Multiple Related Data
              philmodjunk

              You would create a new table with at least TWO fields, _fkPartID and _fkMachineID. There would be no new field added to your PartsList table.

              I somehow screwed that up. Here's what I should have posted:

              MachinesList-----<Machine_Part>-----PartsList

              MachinesList::__pkMachineID = Machine_Part::_fkMachineID
              PartsList::__pkPartID = Machine_Part::_fkPartID

              You can place a portal to Machine_Part on the MachinesList layout to list and select  PartsList records for each given MachinesList record. Fields from PartsList can be included in the Portal to show additional info about each selected PartsList record and the _fkPartID field can be set up with a value list for selecting PartsList records by their ID field.

              • 4. Re: Multiple Related Data
                BrooksLeisure

                Thank you Phil, I have it all working :)