7 Replies Latest reply on Jan 15, 2010 5:13 AM by mwtse

# How to create combinations?

### Title

How to create combinations?

### Post

Say I have a table containing five records: John, Kitty, Paul, Peter, Mary (one for each person).

How could I create all the combination between them?

i.e.

John - John (will have to ignore but this should be easy)

John - Kitty

John - Paul

John - Peter

John - Mary

Kitty - John

Kitty - Kitty (ignore)

.

.

.

The final result I want to have is to pick out two person and compare their data. I remembered that in SQL, there is a way (self-join ?) to produce a two-dimentional table I needed (i.e. Field1 contain one person, Field2 contain another, if there is 5 person, the table will have 5 x 5 = 25 records). However, I could not figure out where and what to search for with FMP 10.

• ###### 1. Re: How to create combinations?

The easy way to do this would be to construct a self-join relationship based on:

People:: PersonID ≠ People 2:: PersonID

Then you can show the data of all OTHER people in a portal to People 2 - right in the person's record.

Filemaker does not produce tables "on-the-fly", so to do exactly what you described you'd have to loop and create the combinations as records in another table. The loop logic is basically:

OUTER LOOP: \$i from 1 to n

INNER LOOP: \$j from \$i+1 to n

BTW, using your example there would be only 10 unique combinations, not 25.

• ###### 2. Re: How to create combinations?

OUTER LOOP: \$i from 1 to n

INNER LOOP: \$j from \$i+1 to n

That was what I want to do in fact, however, I could not figure out how to implement the inner loop. After creating the self-join, I do not know how to loop through all the records of "People 2".

• ###### 3. Re: How to create combinations?

I've managed to do it by using Go to Record but do not think it should be done like that. (I do not use self-join)

Anyway how could I copy/export the script so that you may give comment on?

• ###### 4. Re: How to create combinations?
Perhaps I wasn't entirely clear: if you do the self-join, you won't need the script. It will all be done through the relationship.
• ###### 5. Re: How to create combinations?
I have read SQL books cover to cover several years ago, but have not done any serious SQL programming. So I know that SQL can produce a N x N table by using self-join, but the detail is forgotten. I believed that FMP should behave similar. However, after reading and searching about "self-join", I could not find any explanation that is relevant. May be it is very easy but I could not figure out how to do it. I must be because of my poor understanding of FMP. Would you mind telling me more?
• ###### 6. Re: How to create combinations?

Filemaker is not SQL. Some things are very similar (it IS a relational database), others VERY different. Approaching Filemaker with an SQL state-of-mind can prove very unproductive and frustrating.

A self-join is a relationship of a table to itself, using two occurrences of the same table on the relationship graph. You need to be familiar with the basics of creating a relationship and a portal before going on.

http://www.filemaker.com/help/html/relational.11.11.html#1028192

• ###### 7. Re: How to create combinations?

Thanks for your info. I must confess that I'm not an SQL programmer and was really very poor at SQL. I could do only very basic query. On the other hand, I have a very strong procedure programming background, that is, however, equally bad. I have big trouble in understanding both SQL and FMP because I think in terms of procedures.

I will have to read the material you pointed to, but I think may be what I was missing is the portal.