Jump to content
NewBoard

Handling Identical Part Numbers With Different Revisions

Recommended Posts

NewBoard    0

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!

Share this post


Link to post
Share on other sites
Cable    3

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

Share this post


Link to post
Share on other sites
NewBoard    0
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?

Share this post


Link to post
Share on other sites
doughemi    83

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.

Share this post


Link to post
Share on other sites
NewBoard    0
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.

Share this post


Link to post
Share on other sites
Cable    3

doughemi esplained what I was suggesting

Share this post


Link to post
Share on other sites
doughemi    83

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.

snap2.jpg

Share this post


Link to post
Share on other sites
NewBoard    0
Posted (edited)
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.

snap2.jpg

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. 

CPJ.PNG
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.

Rev.PNG

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 by NewBoard
Needed further clarification

Share this post


Link to post
Share on other sites
doughemi    83

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.

Share this post


Link to post
Share on other sites
NewBoard    0
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?

Share this post


Link to post
Share on other sites
doughemi    83

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.

Share this post


Link to post
Share on other sites
NewBoard    0
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?

Share this post


Link to post
Share on other sites
doughemi    83

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.)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Similar Content

    • By Ben Moore
      Often we have to revise estimates for a customer, and they may come back and order against an estimate that is not the most recent. I am trying to find the best way to tie all the estimates together with the same Estimate or Job #, and increment a revision #. So far I've created a Job# field, and a Revision number field, but having trouble getting the revision number and job number to increment correctly when I have a new estimate vs a revision.  Any suggestions?
    • By Matthew R White
      I have a number field that I would like to auto enter either (2, 3, or 9) at random every time a new record is created.
      Please help!
      Thanks
      Matt
       
      I tried to do a sequenced auto enter but filemaker simply counts up from 1 and I can't specify the 3 numbers.
       
      Thanks
       
×

Important Information

By using this site, you agree to our Terms of Use.