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.
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.
"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
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.
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.
Okay I'm going to start to try and plug through this. I think you've set me in the right direction.
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.