I have several database files with a total of about 100 fields that I
need to consolidate into one file. These fields need to be mapped via
an individual ID numbers. Does anyone know of a way I can avoid having
to create the hundred new fields and manually lining them up with the
fields in the individual files?

In other words I have survey data from participants who are identified
by unique ID numbers. I need to consolidate their answers to several
different surveys into one masterfile (that I can later export to SPSS
as necessary). The way I know how to do it know is very tedious and
requires defining a field for every field I wish to import, lining them
up carefully, linking them via the unique ID number, and then doing it
again several times. I am hoping to automate this as much as possible.

I am using FileMaker Pro 5.5, but can upgrade to 7 if it would make a
difference (I am trying to avoid upgrading since I have collaborators
who have the older version and I don't know how to convert a new file
into an old one).

Thanks for your help.

..d

I assume you miss spoke when you said "linking them via the unique ID
number" since an import doesn't require a relationship.

If the columns in all your survey data are in the same order, then you
only need to line up the import field order once, and save the import
script without specifying a file name. You can then run the import
script, and it will ask you to select the file, but the order of the
fields won't change.

If your looking for an easy way to map import field order, eg. you have
100 fields in the survey data and need to map it to 100 fields in the
master file, then do the following;
- check survey data for blank columns or columns your not going to
import
- in master file create 1 global field for each non imported column eg.
"GlobalBlank1", "GlobalBlank2"...
- from the master file do an export (I know you want to do an import)
matching the column order, including blanks, in the survey file.
- save the export file as a FileMaker file
- save the export script also, you may need it again next year
- import from the exported FM file by field name order
- save the import script without specifying file name

The import order for importing survey data should be correct unless you
made a mistake in the export.

Thank you for your response. I think I have confused my use of the
terminology, and am not sure that your suggestions are going to do what
I need.

I have many records that are mostly overlapping but not completly so.
ie
Database 1:
ID number, question 1, question2
12, a, c
13, d, d

Databae 2:
ID number, question 47, question 48
7, yes, no
13, no, no

Combined file:
ID number, question 1, question2, question 47, question 48
7, , , yes, no
12, a, c, ,
13, d, d, no, no

I know how to do this by importing with field mapping, where from File
1 I define fields " question 47" and "question 48" and match fields
based upon ID number with File 2 and import.

Since I have so many questions (fields), I want to avoid having to
create so many fields and then lining them up with the old fields to
import. So my ideal solution would have one field entitled ID number
that is matched on import, and all other fields are created in the
master file.

The solution FP suggests (thanks for the effort), seems like assumes
that the fields I need map to already exist. If there is no way to make
the fields be created automatically, I could create the fields and FP's
technique may be helpful if it makes so I can avoid having to line up
all the fields during import.

Thanks,
..d