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!

[Cable]

Create a relationship with a reverse date sort and only show the first record?

[NewBoard]

3 minutes ago, Cable said:

Create a relationship with a reverse date sort and only show the first record?

How would I go about doing this?

[doughemi]

Revisions should be put in their own table, and related to the Parts table by Part ID. Sort the relationship by Revisions::date, descending. If you place fields from Revisions on a Parts table layout, only fields from the latest revision record will be seen.

[NewBoard]

3 minutes ago, doughemi said:

Revisions should be put in their own table, and related to the Parts table by Part ID. Sort the relationship by Revisions::date, descending. If you place fields from Revisions on a Parts table layout, only fields from the latest revision record will be seen.

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.

[Cable]

doughemi esplained what I was suggesting

[doughemi]

Here is a diagram of the organization of your solution. A layout of the Inspections table can display a field (such as rev_number) in the Revisions table through the chained relationships between the tables (with the intervening CustomerPartsJoin table).  If you put the field in a portal, you can view more than one record, but in this case, you only need to see one record. A portal is not required. Sorting the relationship as mentioned before will cause only the latest record to be that one record.

Note that if you someday want to view a report of all the revisions of a part, all you need to do is add a relationship between Parts and a new TO of Revisions.

[NewBoard]

On 5/19/2017 at 5:01 PM, doughemi said:

Here is a diagram of the organization of your solution. A layout of the Inspections table can display a field (such as rev_number) in the Revisions table through the chained relationships between the tables (with the intervening CustomerPartsJoin table).  If you put the field in a portal, you can view more than one record, but in this case, you only need to see one record. A portal is not required. Sorting the relationship as mentioned before will cause only the latest record to be that one record.

Note that if you someday want to view a report of all the revisions of a part, all you need to do is add a relationship between Parts and a new TO of Revisions.

Okay this looks like exactly what I need. Thank you very much, you are a life saver! I'm just wondering though, how does the CustomerPartsJoin table know which parts go with which customers? Will I have to manually enter the foreign keys for each on that Table?

Edit: I'm actually having quite a bit of trouble with this relational setup. Forms connected to the CustomerPartsJoin can find my customers and part numbers exactly as intended. However, I'd like forms related to Revisions (and Inspections eventually) to have a drop-down menu to pick a part number and customer. Trying to do so yields no desirable results, as all drop down menus I attempt to tie to Customer or Part number are completely un-useable. 

This is the List View of the CustomerPartsJoin table. I have hidden the Customer UUID selector behind the Customer Name box. I can freely chose customers and part numbers here.

This is the form view for the Revisions table. I have tried all sorts of combinations for the drop down menus. I've tried pointing them to the CustomerPartJoin table, pointing them to their own original Parts/Customer tables, pointing them at the UUID of the CustomerPartJoin table. All to no avail. I'm thinking that I'm just missing something very obvious as I'm still new to FileMaker. Or maybe it's not possible to do what I'm trying to do with the current relationship setup.

Edited May 22, 2017 by NewBoard
Needed further clarification

[doughemi]

There are any number of ways:

• A script run by a New Part button on a Customers layout which stores the customer number in a variable and then creates a new record in the CustomerPartsJoin table and enters the fk.
• A dropdown list on a CustomerPartsJoin layout that shows customer names but also populates the fk field with the customer number.
• A manual or scripted or partially-scripted method that mimics the way you assign a new part to a customer now.

to name a few.

[NewBoard]

37 minutes ago, doughemi said:

There are any number of ways:

• A script run by a New Part button on a Customers layout which stores the customer number in a variable and then creates a new record in the CustomerPartsJoin table and enters the fk.
• A dropdown list on a CustomerPartsJoin layout that shows customer names but also populates the fk field with the customer number.
• A manual or scripted or partially-scripted method that mimics the way you assign a new part to a customer now.

to name a few.

I have to ask, why did you split part numbers and customers? Wouldn't it be easier to have a field for customer within the Part Number table?

[doughemi]

Is every part number sold to one and only one customer?

Does every customer own one and only one part? One and only one unit of a given part number?

Will this absolutely always be true?

IF your answer to all of these questions is yes, then do it your way. If it turns out you're wrong, you will have to rebuild your structure to accommodate multiple parts and/or customers each and every time they occur.

If you have a "something"(customer, part, inspection) it is called an entity. If that entity has a one-to-one correspondence with another "something", the second entity can be assumed to be an attribute of the first and can be a field in the first table. If you have an entity that can apply to more than one instance of another, it is a one-to-many relationship and the two should be in separate tables. If you have two entities that have multiple relationships with each other (which is the usual case with customers and products--many products can be sold to many customers), then the separate tables for each entity should be connected with a join table.

7 hours ago, NewBoard said:

 However, I'd like forms related to Revisions (and Inspections eventually) to have a drop-down menu to pick a part number and customer. 

Just create a new Value list showing all values from Customers::ID and Customers::Name to use with the dropdown.

[NewBoard]

15 minutes ago, doughemi said:

Is every part number sold to one and only one customer?

Does every customer own one and only one part? One and only one unit of a given part number?

Will this absolutely always be true?

IF your answer to all of these questions is yes, then do it your way. If it turns out you're wrong, you will have to rebuild your structure to accommodate multiple parts and/or customers each and every time they occur.

If you have a "something"(customer, part, inspection) it is called an entity. If that entity has a one-to-one correspondence with another "something", the second entity can be assumed to be an attribute of the first and can be a field in the first table. If you have an entity that can apply to more than one instance of another, it is a one-to-many relationship and the two should be in separate tables. If you have two entities that have multiple relationships with each other (which is the usual case with customers and products--many products can be sold to many customers), then the separate tables for each entity should be connected with a join table.

Just create a new Value list showing all values from Customers::ID and Customers::Name to use with the dropdown.

I ended up keeping the customer's their own separate table. However, I linked them directly to the parts list. That has seemed to work as intended. What was the advantage to having the third table joining the two?

[doughemi]

I explained that:

Quote

If you have two entities that have multiple relationships with each other (which is the usual case with customers and products--many products can be sold to many customers), then the separate tables for each entity should be connected with a join table.

Each join table record is a record of one part owned by one customer.

Without it, how do you record that customer #1 owns both part 123ZYX and part 456PQR? And then buys 2 pieces of 098WTF? 

(hint- you can't and you can't without modifying the structure, or "schema" of your database. But you could with proper structure.)