Looping over related records

Discussion created by gobbles on Dec 11, 2012
Latest reply on Dec 11, 2012 by comment

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.







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


go to related record - show in new window

go to first record


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.