I have a database of about 1000 products. When looking at each record / product in layout view I want to see a list of 6 similar items to the current product in which customers might also be interested. A bit like the way Amazon does it. But I can't get it to work properly.... HELP!!!

Here's what I have done:

Each record has a product name and a unique 4-digit reference number. So I created 6 additional fields called "alternative_interest_1" etc all the way up to 6, and these fields have a drop down list attached of all the products so I can select the appropriate one. This then places the unique 4-digit number in the "alternative_interest" field.

The drop down works by linking to the 4-digit unique number but also shows the product name. This way, if the product name ever changes the 4-digit number will keep the link active.

HOWEVER, when I click away from the field and thus the drop down list, I can still see the 4-digit number but obviously no longer the product name, so I don't actually know what product it is by merely looking at the field.

I need another field underneath that continues to display the product name, but cannot get this to work?

I hope all that makes sense and someone can help me.

You will need to link the field that is holding the 4 digit ID in a relationship to another Table Occurance of your Products Table. Then place the product name field on your layout from that TO.

You can actually place the name field right over the top of the ID field. Set it to not be enterable in browse mode and when you click it it will drop down the drop down list from the ID field as if the name field weren't even there.

Edited November 16, 201015 yr by Guest

Hi Ron

Thanks for your suggestion, however I have tried that and it doesn't work!

I created a second table occurrence ('products 2') and the relationship between that and 'products' is the 4-digit unique reference field.

However, when I place the related field 'product name' from 'products 2' T/O on the 'products' layout it just displays the product name for that record. I need it to show the product name for the related 4-digit reference in the field 'alt interest 1'.

Doing what you suggest doesn't unfortunately create a link or relationship between the field 'product name' on T/O 'products 2' and the 'alt interest' fields 1-6 on the products layout.

Do you see what I mean?

I created 6 additional fields called "alternative_interest_1" etc all the way up to 6

This is not a good starting point. To make this work, you would also need 6 self-join relationships, one for each field. It would be better to use a single checkbox field, or a repeating field, to make the selections - then show the related products in a portal.

Is the attached any help?

interested.zip

Thank you 'comment' and 'efen' for your suggestions, and efen your example is great, I am sure I can work this into my file.

I would never have come up with that workaround myself! Cheers, I'll let you know if it works out!

I don't understand your file. It looks like "Product X" cannot be a "see also" item for two or more products at the same time?

Yes, I agree - it is a poor example. It was cobbled together in five minutes before I had to earn my living.

I have attached a revised file that adds the foreign key to any already present rather than substituting the one there. I also appreciate that this would probably be better using a join table.

interested.zip

Edited November 17, 201015 yr by Guest
forgot to attach the file

If I can take a complete liberty of your time... how would I do this with a join table? As an 'intermediate' Filemaker user, the simplest way is always best for me!

Also, presumably any relationship will be such that if the name of a product changed it wouldn't affect anything and the name would be updated in the list?

Like the attached - and the answer to your question is yes.

interested2.zip