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.
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:
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.)
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.
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...
Yes, relating them by a unique photo ID should do the trick.
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...)
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.
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?
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.
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?
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.
This is now working exactly as I hoped it would! Thanks for your patience with me, Phil...