8 Replies Latest reply on Sep 24, 2009 1:02 PM by comment_1

    Want to replace copy / paste script - is inner join a solution?

    afubeca

      Title

      Want to replace copy / paste script - is inner join a solution?

      Post

      I am currently running a copy paste script to move data from one table to another.  Since I am dealing with millions or records the process is becoming cumbersome.  I believe there is a better way of doing this. I described the issue to a friend who is an SQL guy and he suggested an "inner join", unfortunately I have no idea how do an inner join in FileMaker.

       

      My "main table" has a "unique id" for each record.  

       

      I am performing the find an a "second table" with 5 columns.

       

      I have created a "new field" in the "main table".  My script runs a find in the "second table" where: 

       

      F1=The "unique id" from the "main table" 

      F2=A 

      F3=B

      F4=C 

       

      After the find is only one record so I take the F5 value and copy back into a field on the main table.

       

      I can't simply do a lookup because the "unique key" is repeated multiple times on the "second table"

       

      Is there an easier way?  Anyone know the answer?

       

      Thanks.