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

    How to create combinations?

    mwtse

      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?
          comment_1
            

          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?
            mwtse
              

            Thanks for your reply,   

             

            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?
              mwtse
                

              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?
                comment_1
                   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?
                  mwtse
                     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?
                    comment_1
                      

                    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?
                      mwtse
                        

                      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.