2 Replies Latest reply on May 5, 2017 7:10 AM by TSPigeon

    Setting up relationships

    sblake

      Last used FMPro about 20 years ago and did not get into relational databases.  Now working part time for a small association and came upon a problem that I know FM can solve.  Their data is now stored on spreadsheets with multiple tabs and 67 columns!!

       

      The challenge:

       

      179 companies

      Each company has from 1 to about 15 plants

      Each plant is certified every three years in one of three areas - I'll call them A, B, and C.

       

      So far:

      Each company can have MANY plants.

      Each plant can have only ONE company.

      Each plant can have MANY certifications, i.e. a new one every three years (and we have to keep historical data)

       

      Have to track the certification process:  when applied, when fee paid, when test submitted, when certified; then ongoing quarterly tests for next three years.  And then process starts over.  Want to show this in one easy to use layout.

       

      I haven't been able to show the company on a plant page.  Need unique ID#s??

       

      And  --- Certification type A can belong to many plants but its particulars -- when certified, when expires -- belong to only one plant.

       

      Have 8 more days on the Trial Version and want to demonstrate to them that FMPro is the way to go.  BTW, no one in this office had ever heard of FMPro.  Hope to make believers out of them.

       

      Thanks to any and all suggestions.

        • 1. Re: Setting up relationships
          beverly

          Welcome back, sblake!

           

          Every table needs a unique ID field on every record. These are the Primary keys. Then tables which 'relate' would also have a Foreign key field that would match the PK of it's parent:

           

          Companies::CompanyID_pk = Plants::CompanyID_fk

               Plants::PlantID_pk = ???

           

          Certifications may be a table which is joined to Plants, but not directly. Perhaps there should be a table between to link each plant to the different Certifications? That way you can also have an "expires" field that applies to that certification for that plant:

           

          Plants::PlantID_pk = plant_certs::PlantID_fk

                    plant_certs::CertificationID_fk = Certifications::CertificationID_PK

               plant_certs::expires_date

           

          Think through it a bit and see if you have other rules to define.

           

          When these all link together, you can even see the certifications for all plants in each company, along with all their expires_date!

           

          beverly

          1 of 1 people found this helpful
          • 2. Re: Setting up relationships
            TSPigeon

            sblake:

             

            Thank you for your post!

             

            I'm also going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!

             

            TSPigeon

            FileMaker, Inc.