Importing from Excel problem

[TheunE]

Hello,

I have just started working with FileMaker, so forgive me if this is a dumb question.

My problem is the following. I have got a massive excel-file with 5 worksheets. The columns are aligned as follows:

- A unique ID number

- A variable ID (such as ROI for return on investment)

- A year

- The value of the variable for that specific year and ID

Now what I want is that for every unique ID, one record will be created in FileMaker. Problem is, that in excel there are up to 90 rows for every unique ID for a different variable/year.

Can anybody help me with this problem?

[Fitch]

Import to FileMaker and then remove the duplicates. See for example this article.

[TheunE]

Thank you for your reply.

However, I don't want the other records to be deleted. Consider this example:

ID___ YEAR VARID VALUE

ID001 2009 VAR001 10

ID001 2009 VAR002 15

ID001 2010 VAR001 12

I would want all these variables to merge into one ID001 record. Could you please tell me how this is possible in FileMaker?

[comment]

I would want all these variables to merge into one ID001 record.

No, you would not want to do that. Where exactly would you put up to 90 values for each record? The proper structure is to have one unique record for each ID in a parent table, and the year-specific values in a child table.

If you define the ID field in the parent table as Unique, Validate always, you can then import the records from the child table - only one record per each ID will be imported.

[TheunE]

Excuse me for asking another dumb question, but what do you mean by FileMaker will only import one record per unique ID? Will it import all variables and put them in one record or will it only import one variable each time?

For clarity purposes I added an example of what I have now.

Under that is the structure I would like to have in FileMaker.

example.txt

[comment]

If the ID field is validated as unique, Filemaker will import the first record with a specific ID and reject other records with the same ID. I am not sure I understand your example - it seems you want one unique record for each Fund/Year combination?

[comment]

BTW, I am not sure you even need the parent table. You could import your data as is and produce a report summarized by Fund, Year and Type.