i need to be able to merge related data from a varible number of records into one related field

the data i have looks like this

record # --- Field=ID #---Field=data

1 001 AA

2 001 BA

3 001 FD

4 002 GK

5 002 VX

etc for 67,000 records total

what i need in the end is

record # --- Field=ID #---Field=data

1 001 AA BA FD

2 002 GK VX

i thought i could use two tables and a SUM calculation but this did not work.

anyone have any ideas on how to "merge" related data?

thanks

stimpy

You could try. Define a relationship between this table and a copy of itself joining by Field ID. Define a Value list of Field Data = values of Field data (show only related by Field ID Join) and then define a new field FieldData = ValueListItems(of the value list). If you want these separated by spaces as shown in your post then you will have to do a Substitute on the ValueListItems replacing carriage returns by spaces. You still have to delete duplicates problem. I believe there have been posts on this in the past. One method which might work is to create a clone of the file, validate the Field ID as unique and then import the records from the original. It "should" import just one record for each Field ID but I haven't tested it so don't try it without a backup.

Edited August 5, 200520 yr by Guest
Deleted one stupid idea!

thanks the get valuelist items worked great!

eliminating duplicates is another matter .. i will have to dig deeper on that one.

I hope fileamaker adds this ability as a feature...its too common a need to have to do a calculation for

There is a discussion going on right now dealing with deleting duplicate records:

http://fmforums.com/forum/showtopic.php?tid/164109/post/170615/#170615

I have also tried the method I mentioned of validating the Field as unique. By itself that doesn't seem to be enough. However, by validating it as Non-empty, Unique, Validate always, do not allow override it does work and it works whether or nor you put auto-enter options on during the import.