3 Replies Latest reply on Dec 11, 2012 11:12 PM by comment

    Looping over related records

    gobbles

      Please put me out of my missery, I am doing something hopelessly wrong.

       

      I have two related tables.

       

      Each entry in table A has a series of related entries in table B. Each of those entries in table B has a short letter code. The are related by way of a file number.

       

      eg:

      TABLE B


      12345EF
      12345B
      12345CP
      12345B

       

       

      All I want to do is loop through each of the tuples in TABLE A, then visit each of the related entries in TABLE B and then update a field in TABLE A so that all the rows in TABLE B are concatanated together - minus any dupplicates.

       

      So in the case above, the FIELD in TABLE A would be: "EF B CP"

       

      I had thought this would be a relatively straightforward task but it appears I am completely unable to do it.

       

      I have tried a script that uses a set of global fields to collate the codes:

       

      Go to Layout for Table A

      Go to First record

      Loop

      go to related record - show in new window

      go to first record

      loop

      if code = BF

      set global field BF to BF

      end if

      ... if statements for the other possible code values (about 5 or so)

      go to next record(exit after last)

      end loop

      close new window

      set field in TABLE A(concatanate contents of the various globale fields)

      delete content of globals

      go to next record(exit after last)

      end loop

       

      Not only does this not work (it seems to ignore my if statements and assign all values to the global fields) but it takes a good 5 - 10 seconds to loop through the entries of each TABLE B. There are never more than 10 or so related entries in TABLE B and only about 6 if statements. I have no idea what filemaker is doing.