3 Replies Latest reply on May 18, 2014 7:07 AM by philmodjunk

    drug drug interaction



      drug drug interaction


           I want to implement drug drug interaction feature in filemaker. I have 2tables , table drugs and table interaction. Table drugs contain a  single field named drugList that contain drugs in a single field of a record




           drugC ,


           (generated from List function from other table)

           Another table interaction contain field -->id ,drug1, drug2 ,message . kindly help me how to implement the drug interaction feature by relationship and script steps. thanks in advance.

        • 1. Re: drug drug interaction

               It would seem you need these relationships:

               Drugs----<Interaction>-----Drugs 2

               Drugs::__pkDrugID = Interaction::_fkDrugID1
               Drugs 2::__pkDrugID = Interaction::_fkDrugID2

               Drugs and Drugs 2 would be Tutorial: What are Table Occurrences? with the same data source table. Fields in interaction would document the interaction between those two drug records.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: drug drug interaction
            thank you philmod. would you kindly attach a sample file for me by implementing drug interaction feature. thanks in advance.
            • 3. Re: drug drug interaction

                   no thank you. There's a limit to the amount of "free consulting" that I can give out here. wink

                   I do have a demo file of many to many relationships that you can look at, but it's not identical to what I've described here as it uses two different tables instead of two occurrences of the same table. But it does illustrate some of the same layout and scripting details you might need for your solution.

                   And I will share a few more design details that came to mind after I posted my first comments:

                   The link in a join table of a many to many relaitonship is "one way". When you create a record in the join table to link Drug A to Drug B, this does not automatically link Drug B to Drug A. (After linking B to A from the record for Drug A, pulling up the record for B will not automatically show that it is linked to A.) This can be managed by setting up a script that creates a second join table record with the match field values swapped. Example: If linking A to B requires setting up a new Record in Interaction with _fkDrugID 1 = 248 and _fkDrugID 2 =831, then your script would create a second record with _fkDrugID 1 = 831 and _fkDrugID 2 = 248. That way, when you pull up the record for Drug B, the second record will enable data about Drug A to appear in the portal to Interaction.

                   But this creates a new problem. You now have two records in Interaction for every linked pair of reords. You won't want to enter and maintain the same interaction information in two separate Interaction Records. This would be both redundant and also risks having interaction information for Drug A to B that is not consistent with the info for Drug B to A. To manage that, you'd define a 4th table, InteractionDetails and Link it to Interaction like this:

                   Interaction::_fkInteractionDetailID = InteractionDetails::__pkInteractionDetailID

                   Fields from InteractionDetails can also be included in the portal to Interaction that I described in my first post and both linking records in Interaction would need to have the same value in _fkINteractionDetailID so that they show the needed details from the same InteractionDetails record.

                   Here's the demo file for what it is worth: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   It's in the older file format so if you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open this file produce a copy of the file that is converted to the newer file format.