1 2 Previous Next 16 Replies Latest reply on Oct 7, 2010 3:05 PM by jmau

    Script to color fields that contain identical values

    jmau

      Title

      Script to color fields that contain identical values

      Post

      Hello,

      I am building a database that has three tables, parts, products and jobs. Each part is its own record. Each product is a record and contains multiple parts and each job is a record that contains mutiple products.

      In the job record the operator keys in all the products and that gives him a list of all the parts to complete the job. Some of these parts will be used on multiple products in the job and will show up more than once.  I would like to be able to loop through all the fields in a job record and somehow highlight or color all the fields that contain identical values (identical parts) so that the operator can see which parts are common.

      Any ideas or help with this script would be appreciated greatly.

        • 1. Re: Script to color fields that contain identical values
          philmodjunk

          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.

          Possible Relationship:
          Products::ProductID = ProductsSameIDProject::ProductID AND
          Products::ProjectID = ProductsSameIDProject::ProjectID

          conditional format expression:

          Count ( ProductsSameIDProject::ProductID ) > 1

          • 2. Re: Script to color fields that contain identical values
            jmau

            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?

            • 3. Re: Script to color fields that contain identical values
              philmodjunk

              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:  

              http://fmforums.com/forum/showpost.php?post/309136/

              Without that structure, it'd be darn near impossible to set up a self join that works.

              • 4. Re: Script to color fields that contain identical values
                jmau

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

                • 5. Re: Script to color fields that contain identical values
                  philmodjunk

                  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

                  • 6. Re: Script to color fields that contain identical values
                    jmau

                    Thanks Phil,

                    I'll study what you wrote and see what I can come up with.

                    • 7. Re: Script to color fields that contain identical values
                      philmodjunk

                      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

                      • 8. Re: Script to color fields that contain identical values
                        jmau

                        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.

                        • 9. Re: Script to color fields that contain identical values
                          philmodjunk

                          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
                          etc

                          Would you like to see a report of the parts needed for this job that looks like this?

                          Customer: Acme Products    Job No.: 45678

                          Parts:
                          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.

                          • 10. Re: Script to color fields that contain identical values
                            jmau

                            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!

                            • 11. Re: Script to color fields that contain identical values
                              philmodjunk

                              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

                              • 12. Re: Script to color fields that contain identical values
                                jmau

                                The horizontal portal trick sounds intriguing. Can you elaborate?

                                I would settle for one color for now. Of course multiple colors would be better. With a javascript background my initial approach would be to set up a double loop and loop through the field values and color if different, but I guess it isn't quite as straightforward with filemaker.

                                • 13. Re: Script to color fields that contain identical values
                                  philmodjunk

                                  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.

                                   

                                  • 14. Re: Script to color fields that contain identical values
                                    jmau

                                    Hmm,

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

                                    1 2 Previous Next