13 Replies Latest reply on Jan 16, 2012 1:25 PM by BradWieland

    Find and Collect matching records



      Find and Collect matching records


      How would I set this up:

      1. I have multiple records representing similar but different product numbers: 1234/2, 1234/3, 1234/4, etc.

      2. Each of these records shares a common photo: 1234.jpg

      3. When the record for any one of these product numbers is being viewed (1234/2 for example), I want the other records (1234/3, 1234/4, etc...) to be "gathered" into my layout. In other words, into a field that says something like: These product numbers all share photo 1234.jpg



        • 1. Re: Find and Collect matching records

          Define a calculation field that just returns the characters to the left of the /. This then allows you to set up a self join relationship that matches records on this common value.

          Left ( ProductNumber ; Position ( ProductNumber ; "/" ; 1 ;1 ) - 1 )

          Will return "1234" in the above case.

          THen this self join relationship, where cProductBaseNumber is defined with the above calculation will match to all records with this common value:

          ProductTable::cProductBaseNumber = ProductTable 2::cProductBaseNumber

          Create ProductTable 2 by selecting it and then clicking the duplicate button (two green plus signs) in Manage | Database | Relationships. This does not create a new table, jsut a new occurrence of the existing table.

          With that in place, a portal to ProductTable 2, placed on a ProductTable layout can list all the records with the same base number.

          • 2. Re: Find and Collect matching records

            OK, I think I've simplified my challenge a little. I've created a field called "Photo Sharing ID". Each related record would contain the same information within this field (let's say 1234.jpg).

            How would I make all records sharing this common info within "Photo Sharing ID" (1234.jpg) show up in my layout? I've attached an image as an example of what I'm after:

            • 3. Re: Find and Collect matching records

              My previous post is intended to produce that exact result. The vertical list of products is exactly what you would produce with the portal. What part of that post does not work for you? (I can then work from there to get you where you need to go.)

              • 4. Re: Find and Collect matching records

                I do appreciate your help with this, Phil...

                Unfortunately, the numbers are not as consistent as I thought when I first posted; they don't all share that nice, convenient naming convention. Sometimes, records with completely different Product Numbers are sharing the same photo.

                So I thought it would work well to simply have them share a Photo ID and tie them together in that way.


                • 5. Re: Find and Collect matching records

                  To clarify, Product Number 1234/2 could share a photo with Product Number 279D-4AZ/6.5...no logic or predictability to the name matches at all...

                  • 6. Re: Find and Collect matching records

                    Yes, relating them by a unique photo ID should do the trick.

                    • 7. Re: Find and Collect matching records

                      So, would a portion of your explanation above allow this to happen? I'm sorry, I just don't understand the technology well enough to know which part. (I'm referring to pulling in the related records so that they are viewed in a list...)

                      • 8. Re: Find and Collect matching records

                        Do you have a field called PhotoID that identifies the image associated with that record?

                        If so, then use that field in place of the calculation field like this in the relationship:

                        ProductTable::PhotoID = ProductTable 2::PhotoID

                        and now add a portal to ProductTable 2 to list all products with the same value in PhotoID.

                        Do that first to confirm that you can get this working. You'll spot one issue in the portal, the current record will be listed in the portal with the other records with the same photoID. Using your example data, you'll see 1234/1 listed with the others in the portal.

                        If this is a problem for you, you an modify the relationship to eliminate the current product record from the list:

                        ProductTable::PhotoID = ProductTable 2::PhotoID And
                        ProductTable::ProductID ≠ ProductTable 2::ProductID

                        You can double click the relationship line to open a dialog where you can add more pairs of fields and also to use different operators than the default = operator.

                        • 9. Re: Find and Collect matching records

                          I guess this will need to be more specific for me to get it.

                          So far in this database, all fields are in just one table. It's called "Mains FileMaker Database v1". I've created a second table called "Associated Products". The field containing the "common" image file name is called "Photo Sharing ID".

                          So, would the calculation be?:

                          Mains FileMaker Database v1::Photo Sharing ID = Associated Products::Photo Sharing ID

                          Where would this calculation go? Which field?

                          I've also added a portal to table "Associated Products" with just one field, which I've called "Associated Products Shared Photo ID"

                          I've attached a screenshot showing what the relationship graph looks like.

                          It's not working yet...where am I going wrong?

                          • 10. Re: Find and Collect matching records

                            Delete Asociated Products. This is not what I had in mind.

                            In Manage | Database | Relationships, click Mains FileMaker Database... to select it, then click the button with two green plus signs to make a duplicate table occurrence. You have not duplicated any table or data, just created a new "label" that refers to the same table in order to define a relationship between your table and itself.

                            • 11. Re: Find and Collect matching records

                              OK, I've done that. I still need to create a portal in "label" Mains Importing Database 2, right? Where do I "find" this label to create the portal?

                              • 12. Re: Find and Collect matching records

                                And then you need to link the two in a relationship. Have you done that?

                                The "label" is the name shown in the new "box" you just created. It should be the same name as the original with a "2" appended to it. You can double click this box to rename it if you wish.

                                WHen you use the portal tool to add a portal to the layout, select the name of this new "box" in Show Related Records From.

                                For more on table occurrences, see this thread: Tutorial: What are Table Occurrences?

                                It's an older thread and posts to it will not pop it back up into recent items so please post any comments or questions about it here instead or I may very well miss the post you make in the older thread.

                                • 13. Re: Find and Collect matching records

                                  This is now working exactly as I hoped it would! Thanks for your patience with me, Phil...