Generate a Self Join Table from a Value List

[Princebaskar]

Hi:

I'm a Novice user of Filemaker 11 getting trouble finding a solution to link 2 tables from a set of values located in a Value List.

The first table Products:Product_ID holds the list of products

The second one Products_to_Products is used to show in a portal the products that have the same specs and that can be considered as "similar" or "compatibles".

I need to create the link between this two tables using a Value List.

Example: 4 products has been selected and the value list contains the Product_ID for each one separated by a CR:

myValueList

1

2

3

4

Now, based on this list, I need to create in the table Products_to_Products the records that will hold the pk's for each combination of the members of the Value List.

The only constrain is to avoid the association between same products.

The needed result is this:

Product_ID1,Produc_ID2

1,2

1,3

1,4

2,1

2,3

2,4

3,1

3,2

3,4

4,1

4,2

4,3

There is any Function in Filemaker 11 that will make easy to solve this?

Any help will be greatly appreciated.

[efen]

I am having trouble understanding how you have structured your file. What is the difference between the products in the Products table and those in the Products_to _Products table?

Should they not all be in the same table?

Do you really have version 11 certification?

[LaRetta]

I'm not sure I follow either but if all you are looking for is to omit the same Product from appearing against itself then change your relationship to:

Products::IDsList = Products 2::ProductID

AND

Products::ProductID <> Products 2::ProductID

[Princebaskar]

I am having trouble understanding how you have structured your file. What is the difference between the products in the Products table and those in the Products_to _Products table?

Should they not all be in the same table?

Do you really have version 11 certification?

Hi Efen:

Thanks for the answer.

Let me clarify the situation:

The field Products:Product_ID and the fields Products_to_Products:Products1 and Products_to_Products2 contains essentially the same: Product ID's.

I'm using the table Products_to_Products to show compatible products of the Products table in a portal row.

In order to show this information, I need to generate the compatibilities using a value list located in a global container that hold several Product_ID (set of similar or compatible Products_ID's extracted from the Products table).

Then, I need to populate the Products_to_Products table and I need to know how I can create this association between Product_ID1 and Product_ID2 using the Value List

The Script or Custom Function needed should take this list of n qty of products and should create the records in the Products_to_Products table.

Then, if I have 3 compatible products in the Value List, and the Product_ID of these are 1, 2 and 3 the resultant Products_to_Products link table will be:

Product_ID1 Product_ID2

1 3

2 1

3 2

3 1

2 3

1 2

As a result, the portal row will show that for Product_ID 1 there are 2 compatibles products (2 and 3). If I go to record 2 I will see also 2 compatibles (1 and 3) and If i go to product 3 I will see also 2 compatible products, in this case 1 and 2.

I'm surely not certified yet (hope to do it soon).

I confused the certification with the version of the product that I'm currently using (Filemaker Advance Pro 11).

I already corrected the mistake in my profile.

Thanks again for the assistance.

[efen]

I'm still a bit uncertain about how you have structured this ...Is there two tables plus a join table or is the Products_to _Products table the linking table? Is the value list generated from all the products in the Products table?

See if any of the attached files on this thread are any help

http://fmforums.com/forum/topic/74828-you-might-also-be-interested-in-list/#370815