AnsweredAssumed Answered

Script to auto populate a join-table from an imported file with a twist

Question asked by eriso3 on Dec 20, 2018
Latest reply on Dec 20, 2018 by philmodjunk

Hi,

 

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

Screen Shot 2018-12-20 at 17.37.35.png

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.

 

Step 1

Create a different design with each mutation given it´s separate field, but with each timepoint still on separate records.

Example 1

Screen Shot 2018-12-20 at 17.37.07.png

 

Step 2

Create a concentrate from the data collected in step 1 with a singe record for each ID

Example 2

Screen Shot 2018-12-20 at 17.36.50.png

 

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.

 

Happy holidays!

 

Erik

Outcomes