A conditional format combined with a self join can do this, but it requires details not in your first post.
If you link the parts table to a 2nd table occurrence of the same table in a way that matches all parts with the same ID and that are for the same Product, your conditional format can change the text and/or fill color of the field to highlight it.
Products::ProductID = ProductsSameIDProject::ProductID AND
Products::ProjectID = ProductsSameIDProject::ProjectID
conditional format expression:
Count ( ProductsSameIDProject::ProductID ) > 1
Thanks for this reply Phil.
The conditional formatting was a great tip. Thanks. Now I am struggling a bit with the self join. Maybe you can help if I explain further. My Parts table has a number field. This is the field I wan't to carry through. My Product table has 7 parts fields. In relationships, each of these parts is joined to an instance of the number field. The Job table has 20 product fields. Each of these is joined to an instance of the product table.
Given that model, how would you suggest I create the self joins?
Well, you should have mutiple fields in one record for parts. That makes everything much too complicated.
You should set up a table of selected parts where you have one part number field per part record--like you find in an invoices solution.
The Invoices starter solution demonstrates this as does this simpler demo file created by Comment:
Without that structure, it'd be darn near impossible to set up a self join that works.
I don't think I explained this very well. Here it is simplified
Parts = 1 number field per record, then a few description fields for each part
Products = 7 fields where the part numbers are keys. These are linked to seven instances of the Parts table so that the descriptions get pulled across.
Job = 10 fields where the product numbers are keys. These are linked to 10 instances of the Products table. These bring in the part numbers used in each product in rows.
The result is that I can create a new job, Type in all the products used in the job and see all the parts that need to be collected. Now just to highlight duplicate parts....
I understood you correctly.
Neither Products nor Job records should have these fields.
If several parts define a product, the part numbers should be stored in a table related to products so that any number of parts can be listed for a given product and you only need one relational link to parts.
Products::ProductID = ProductParts::ProductID
ProductParts::PartNumber = Parts::PartNumber
The same for Jobs, a related table should list the parts so that you can have 1, 2, 50 or more parts listed for a given job record and yet only have one relational link to parts instead of 10. This in turn makes the self join approach possible.
Jobs::JobID = JobParts::JobID
JobParts::PartNumber = Parts::PartNumber
I'll study what you wrote and see what I can come up with.
Hmm, missed that detail that product numbers go on the Job record. Makes sense and my basic advice to add related tables still stands, but with the following changes.
You should have this table structure:
Jobs----<JobProducts>----Products----<ProductParts>----Parts (--< means one to many)
Jobs::JobID = JobProducts::JobID
Products::ProductNumber = JobProducts::ProductNumber
Products::ProductNumber = ProductParts::ProductNumber
ProductParts::PartNumber = Parts::PartNumber
Back to this.
Using Phils advice I have a product table linking to parts table throuigh an intermediary table. The Product table uses a port to add all the parts for that product as in the invoice solution.
Now I have a job table linked to the product table through an intermediatry table. In a portal, I can add the products I created, but don't see how I can get all of the part values from the first portal to display. I only get the first record added.
Clear as mud? Hopefully not. Any help appreciated.
Yes, so far we've just started the process by rationalizing your database structure so you don't have multiple fields in the same record for products and for parts that make up a product. More needs to be done here before you can get what you want. If you just wanted to identify identical products this would be easy. The fact that you need to identify identical parts that are found in different products for the same job make this more complicated.
First some general questions and suggestions:
When you specify products for a Job, do you have a quantity field so that a Job Record might list this in its JobProducts portal?
Qty ProdNumber Desc
5 123 Machinery XYZ
1 754 Tool B
Would you like to see a report of the parts needed for this job that looks like this?
Customer: Acme Products Job No.: 45678
Qty PartNumber Desc
3 3456 Left hand widget
2 3457 Thingamajig
1 12 Left handed screwdriver
In other words, instead of color coding, the parts are listed by part number and the total number of each are listed. I'm asking this, because this type of summary report may be different effective way to get what you need and it's fairly easy to set up from the ProductParts table.
Specifically this will be used to set up a printing press. Each plate is the part, each label is the product and a job has multiple products. In the job screen I would like it to look like this....
LABELA plateid plateid plateid plateid
LABELB plateid plateid plateid plateid
LABELC plateid plateid plateid plateid
The plate ID's that are common with each other would be highlighted.
I did get the layout described using the table instance method described earlier, but not the highlighting. I agree that it is best to do these things correctly the first time though. Thanks for all your help. I've learned alot!
Hmmm, that makes sense, but is very different from what I pictured. Not only do you need to color code Plates that are used more than once. It looks like you need different groups of multi-use plates to have different colors from the other groups. Is that correct?
You can get a horizontal list of plateID's easy enough with either a calculation field in the product record, Substitute ( List ( Plates::PlateID ) ; ¶ ; " " ) or a horizontal portal trick.
I'll have to think some more on the color part of it
The horizontal portal trick sounds intriguing. Can you elaborate?
Actually, a FileMaker script can do what you describe, it'd just be nice not to need one.
Horizontal portals are actually identical one row portals set next to each other. The first is setup to display one row starting on row one, the second displays one row starting on row 2, the third displays one row starting on row 3...
I'm now going to go play with a demo file to check my ideas for a scripted solution and will then get back to you.
I tried the horizontal portal idea on the job layout. I set one to start on row 1 and one to set on row 2. I typed in a product code into each one and they still pulled the first entry in the product record portal. I'm not understanding something because, of course we would want to type in the product code only once and be able to pull all the plate/part id's in that row. How do you link the multiple portals together?
Edit: The substitute calculation worked, but I will want to display a bit more information about each part than just the number on the job record, such as status of part and where it is located.
Thanks again for all your help Phil. I know this is pro bono work but hopefully other people can gain something from it. Let me know when you want to pull the plug....