7 Replies Latest reply on May 19, 2017 3:19 PM by beverly

    Handling Identical Part Numbers With Different Revisions

    newboard

      I am attempting to create an inspection sheet for our Quality Lab. Right now, all parts will be on revision 1. However, once this is up and running, these parts will receive revisions. What will ensue is a table with multiple identical part numbers, but different rev levels. What I want to happen, is that whenever an employee makes a new inspection sheet, FileMaker will only request the data from the newest rev.

       

      My proposed solution is to have another field that will be 0 if it's not the newest, and 1 if it is the newest. Then the inspection sheet will only request data from records that have a 1. I have not yet figured out how to do this though.

       

      If someone else has a better solution though, I am all ears!

        • 1. Re: Handling Identical Part Numbers With Different Revisions
          Jason Wood

          In your situation I would probably suggest having 2 tables - parts and revisions. Whatever fields can change between revisions, provided you want to track how they change - these would be in the revisions table, and whatever fields won't change would be in the parts table. It's possible that the parts table would only have a couple fields (uuid & part number, date modified, etc.), or it might have many fields (part name, dimensions, etc.)

           

          You'd have a relationship from parts to revisions where revisions is sorted by date so that the newest revisions are first. That way whenever you lookup a part number, you can look up current revision info by just going a step deeper through the graph.

           

          There are a lot of details that will change depending on your exact needs - hopefully this will get you started.

          • 2. Re: Handling Identical Part Numbers With Different Revisions
            newboard

            I currently have a three table relationship going. The top layer just has part number and customer number. This table will have new entries on it, but should never really change besides that. The next layer contains the part number, part description, part revs, and a UUID. This is linked to the first table via the part number. The final layer is where all of the inspection data will be stored. This is linked to the first layer via UUID. This is really where I need FM to be able to retrieve data from only the top rev.

             

            You mentioned "step deeper through the graph". I'm not sure what you mean by this.

            • 3. Re: Handling Identical Part Numbers With Different Revisions
              beverly

              "Product Life Cycle" may be a term worth researching. Part numbers with revisions is in the life cycle for manufacturing and sales of products.

               

              Sent from miPhone

              • 4. Re: Handling Identical Part Numbers With Different Revisions
                newboard

                These are built in functions of Filemaker? Would I be able to do variable life cycles? Because some parts may never receive a rev, and some will rev faster than others.

                • 5. Re: Handling Identical Part Numbers With Different Revisions
                  Jason Wood

                  Sounds like you have this:

                   

                  Inspections ---- revisions ---- parts

                   

                  And what I'm suggesting is that you have this:

                   

                  Inspections ---- parts ---- revisions

                   

                  and set the relationship to revisions to sort by timestamp (newest first) so that you're always pulling in the newest revision.

                   

                  If you want inspections to have a permanent relationship to what was the most recent revision at the time you added the part to the inspection, then you'd set a field to auto-enter the uuid of revisions when you enter a part number. Then through a separate relationship from Inspections to revisions, you'd pull in the rest of the data directly from the revision.

                  • 6. Re: Handling Identical Part Numbers With Different Revisions
                    newboard

                    Okay I'm going to start to try and plug through this. I think you've set me in the right direction.

                    • 7. Re: Handling Identical Part Numbers With Different Revisions
                      beverly

                      Sorry, no. Just a term to research. Include 'filemaker' in the search and see if you find anything as well. There may be good diagrams of how to structure this type of database (not FM), but that may also help you.

                      beverly