combining specific records

[dikkeberta69]

Hi all,

 

Maybe I'm asking something that's been explained before, but I can't find a suitable solutions anywhere.

My problem:

I got a comma seperated text file with 1.4 million records.

Simplified it goes like this:

name        location      present/absent/doubtful/no data

name1      germany     present

name1      belgium      present

name1      france        absent

name1      spain         doubtful

name1      italy            present

name1      portugal     no data

name1      hungary     absent

name2      belgium      present

name2      uk              no data

name2      ireland       doubtful

etc. 

 

As you can see I now have multiple records for each name. What I need is this:

name        present                            absent                  doubtful     no data

name1      germany,belgium,italy      france,hungary     spain         portugal

name2      belgium                                                         ireland       uk

 

So, one record for each name. This will bring back the number of records to app. 50.000 and is workable.

Is there a solution for this and can anyone explain to me (step-by-step) how to do this?

 

Thanks very much in advance!

[Kris M]

is the consumption of the csv file reoccuring or one time only?

[dikkeberta69]

Hi Kris,

 

If I understand you correctly this is the answer:

It's a one time thing. I want to import the complete file into Filemaker and hopefully I will end up with adatabase(table) as described above.

There won't be any later additions other than manual ones.

[eos]

I'd recommend leaving the import table as a join table and create parent entities; this will give you much more flexibility.

 

See here for an example (I've totally forgotten how unattractive layouts could be pre v12 …).

 

Apply the script to the included sample data. See how the newly created name records calculate their respective summary. You can easily change the way the summary works or the names of the stati..

 

When you've imported everything successfully, delete the text fields in the import table; the attributes are now stored as keys, referring to the parent tables.

ImportAndCreateParentsForJoin_eos.fmp7.zip

[dikkeberta69]

Wow eos!

You've done nearly all the work for me! Spitze!

I think this solves my problem totally.

I guess I'm less "Intermediate" than you are.

You rock.

Recht herzlichen Dank.