2 Replies Latest reply on May 23, 2014 9:56 PM by TomRome

    Medical database conceptualisation.



      Medical database conceptualisation.


           Hey everyone...

           Here's my problem.

           I'm trying to help someone out on an altruistic medical database project and this is the problem..


           A patient has either one or potentially multiple sites on (or in) their person which is of possible medical concern.  Let's say there are 10 unique sites (for example, knee, elbow, jaw, arm, finger, ovaries,  etc etc...), yet a patient statistically will only ever display issues with a max of three. 

           For each of the 10 possible sites, there are up to 20 unique (to each site of the body) descriptive items of information.  So a patient might have up to 3 sites of concern, and 20 things needing input relating to each site.

           My approach so far has been to:

           A) obviously create patient details table.. unique id, standard stuff..

           B) create a new table for each 'site' populated with fields relevant to that site (so 10 tables with the 20 or so relevant fields for each).

           C) create a 'site data' table which is linked (relevant) to the patient whereby there are dropdown lists....  

           here's where I'm stuck...

           Is it possible to say select "elbow" from a drop down list created from a value list from possible sites, and have the relevant "elbow" data (the 20 or so input fields) table pop up in a portal.. so inputs can be made specific to the elbow (and patient) yet not displaying all 10 possible sites on the original ?

           I'm sure there's a simple solution... but damn it.. just can't quite conceptualise the whole thing.

           Having said that.. I believe that's the problem..  newbie conceptualisation. 

           The idea here is to provide the user (doctors) with 3 possible 'sites' to select, then conditionally, upon selection, easily input relevant data relevant to that site without cluttering the process?

           Any advice would be greatly appreciated.  Even the general direction. :)

        • 1. Re: Medical database conceptualisation.

               One option might be to use a tab control with one tab panel for each of your 10 locations. Instead of selecting a site from a drop down, you'd click a tab label for that site.

               Another option would be to use one Details table instead of 10, with a field that identifies the location. This may require 40 fields in place of the 20, you might need 20 calculation fields that use the value in the location field to produce text that serves as the label for your 20 original data fields. Whether that will work for you or not will depend on the type of data you need to collect for each of your 10 body locations.

               Other options--such as script triggers that change layouts that appear nearly the same except for the location specific portion of the layout--creating the illusion of an "adaptive layout" is also possible.

               In theory, you could even set up a 10 panel slider (In FileMaker 13 or newer only) and a script triggered from what you select as a location can use go to object to bring up the slider panel specific to that body location.

          • 2. Re: Medical database conceptualisation.

                 Thanks a lot Phil.  Very helpful and much appreciated.