I´m a PhD student and self taught Filemaker pro-noob trying to wrestle a national HIV database to better suit my needs. I´ve been very impressed by Filemaker´s data organising capacities (which by the way is the only part I´ll be using - no layouts needed).
The database consist of about 7000 individuals and my current struggle is to manage resistance mutation data which is imported from an excel-file with a cumbersome design.
Current data design
This file consists of about 250,000 records with one mutation with ID and date noted for each row. Each ID will typically have hundreds of records consisting of both unique and repetitive mutations. All together there are about 450 different possible mutations.
My goal is to transform this data in a two-step manner using some clever scripting.
Create a different design with each mutation given it´s separate field, but with each timepoint still on separate records.
Create a concentrate from the data collected in step 1 with a singe record for each ID
For step 1 there is a many-to-many relation between IDs and mutations which most likely is adressed using a join table.
My main trouble is to design a script populating the fields in step 1 without having to use some kind of if/case-function where every possible mutation are noted (since there are >400 possibilities). What I instead would like to do is to get Filemaker to understand that for each record in the imported file, "1" should be plotted in the field with the same name as the mutation in a join table. Is this possible? Is there a better solution?
The concentrated data will eventually be used to check if a sub population are more prone to developing mutations.