9 Replies Latest reply on Jan 3, 2016 6:51 PM by SteveFransen

    ICD10 Code Picker And Nested Portals

    SteveFransen

      Greetings,


      I'm working on a medical record solution:


      Encounter ---< Problem ---< DiagnosisStatus >--- DiagnosisCode (ICD10 codes)

       

      Encounters are patient visits.

      Problems are a list of clinical problems associated with each Encounter.

      DiagnosisCodes are the ICD10 codes that describe each Problem (many to many).

      DiagnosisStatus' describe whether the diagnosis described by each DiagnosisCode is resolved, resolving, worsening, etc. for this Problem.

      DiagnosisStatus also serves as as join table to support the many to many relationship between Problem and DiagnosisCode.

       

      Conceptually I'd have an Encounter layout with a Problem portal, with a DiagnosisStatus portal on each portal row of the Problem portal, with a popover on each portal row of the DiagnosisStatus portal, with a DiagnosisCode portal on the popover from which to pick a DiagnosisCode.

       

      However, that's not an option since portals can't contain portals.

       

      I've looked through a number of questions about pickers but can't find exactly what I need and would appreciate some advice.

       

      Performance is a another major issue since there are almost 100,000 ICD10 code records in the DiagnosisCode table.

       

      Thanks,

      Steve

        • 1. Re: ICD10 Code Picker And Nested Portals
          lkeyes
          Hi....  couple questions:   1. wondering why the diagnostic code is a many to many; can you apply multiple codes to a single diagnosis?   2. With the 100K of codes table of course, you want this to be indexed; if it is indexed (with unique keys) and running on reasonable hardware then I'd think this would be OK.  (Is it a native Filemaker table?... if not, it might be pretty slow...) 
          • 2. Re: ICD10 Code Picker And Nested Portals
            jbrown

            HI.

            My wonder is why Diagnosis Status table is needed at all? It seems to me the status of a problem (Resolved, worsening, etc) is simply an attribute. I get that it serves as a join table, but in this case, why do you need a join table at all? It seems reasonable to have a many to many relationship in this case since there are codes from which you pick for a problem, and you want to see the Code's meaning. Many different problems could be the same code (which is the many-to-many), but that seems an ok thing to do. The Codes table is simply a lookup type table. What does this code mean?

             

            I'd get rid of the Status table, make that an attribute of the problem, and connect the problem to the codes table.

             

            As far as picking, there are many methods. If you're in the portal recording a problem for an encounter, you can bring up another layout that lists the codes. On that layout, you can do some type-to-filter mechanism, and then pick a code to be placed in the field in the portal. I think that's how I'd do it. with 100,000 codes from which to choose, a filter picker would have to be used.

            • 3. Re: ICD10 Code Picker And Nested Portals
              SteveFransen

              Hi Jeremy,

               

              You are correct that the status could be an attribute of a problem. I agree that makes more sense.

               

              However, I need a many-to-many relationship between Problem and DiagnosisCode and I thought a join table was the only way to create that relationship. If that's not correct I could simplify a number of things as I continue to refine the design of my database!

               

              Can many-to-many relationships be created without join tables?

               

              Thanks,

              Steve

              • 4. Re: ICD10 Code Picker And Nested Portals
                SteveFransen

                Jeremy,

                 

                Sorry, I didn't answer your question: "What does this code mean?"

                 

                 

                The codes are ICD10CM codes which are the international system for coding medical diagnoses. In my solution, a problem might be type 2 diabetes which could be related to all of these ICD10CM codes:

                 

                E11          Type 2 diabetes mellitus

                E1121      Type 2 diabetes mellitus with diabetic nephropathy

                E11331    Type 2 diabetes mellitus with moderate nonproliferative diabetic retinopathy with macular edema   

                E114       Type 2 diabetes mellitus with neurological complications   

                E1161     Type 2 diabetes mellitus with diabetic arthropathy

                 

                Thanks-again,

                Steve

                • 5. Re: ICD10 Code Picker And Nested Portals
                  davidhead

                  I think the suggested data structure is reasonable if a Problem can have multiple ICD10 codes attributed to it.

                  Then status can be recorded both for the overall problem and for each specific diagnosis.

                   

                  The question for the picker is what would be the ideal interface in your opinion? How would you want it to work? I would see it best to be able to either dig into each area hierarchically OR to be able to enter a keyword such as dementia to filter the list.

                  • 6. Re: ICD10 Code Picker And Nested Portals
                    lkeyes

                    I guess I'm still pushing back a little about the many to many relationships.... I don't quite see where the MtoM comes in.

                     

                    If I understand this,  you have:

                     

                    An encounter. 

                    One or more problems (for each encounter)

                         One or more diagnoses (for each problem)

                              One ore more codes  (for each diagnosis)

                     

                    Why wouldn't this be a 1 to M between encounter and problem,  1to M from prob to diagnoses,  and 1 to M  between diagnoses and code?

                     

                     

                    The status of each diagnoses could be an attribute (field) of the diagnoses table. 

                     

                    So, essentially, you'd have a "pick list" of diagnoses to choose from when populating the diagnosis portal.

                    and you'd have a "pick list" of codes to choose from for each diagnoses.  I should think the data entry screen would show a single "encounter" record as the main record, and cascading portals for "problem", "diagnosis" and "code", such that as you scrolled through the problem records, the diagnosis and code portals would scroll appropriately, depending on which problem was the active record.

                     

                    The above assumes that you have multiple diagnoses per problem.  Otherwise, if there is only a single diagnosis for a problem, you can eliminate the diagnosis portal.

                     

                    Check out the "virtual list" technique for populating the downstream pick lists.

                     

                    ---- Larry 

                    • 7. Re: ICD10 Code Picker And Nested Portals
                      davidhead

                      lkeyes wrote:

                       

                      I guess I'm still pushing back a little about the many to many relationships.... I don't quite see where the MtoM comes in.

                       

                      If I understand this,  you have:

                       

                      An encounter.

                      One or more problems (for each encounter)

                           One or more diagnoses (for each problem)

                                One ore more codes  (for each diagnosis)

                       

                      Why wouldn't this be a 1 to M between encounter and problem,  1to M from prob to diagnoses,  and 1 to M  between diagnoses and code?

                       

                      The status of each diagnoses could be an attribute (field) of the diagnoses table.

                       

                      The original suggestion is good:

                           Encounter ---< Problem ---< Diagnosis >--- Code

                       

                      It was correctly noted that Problem–Code is a M:M therefore needing a join table which is the Diagnosis.

                       

                      Larry, where you have gone wrong is that each diagnosis has (only) one ICD10 code. The diagnosis is effectively defined by the code.

                       

                      But each ICD10 code can be used multiple times (for different diagnoses for different patients).

                      • 8. Re: ICD10 Code Picker And Nested Portals
                        SteveFransen

                        Hi Larry,

                         

                        Here's how the patient is involved for context:

                        Patient ---< Encounter ---< Problem ---< DiagnosisStatus >--- DiagnosisCode (ICD10 codes)

                         

                        This part is correct:

                        An encounter. 

                        One or more problems (for each encounter)

                             One or more diagnoses (for each problem)

                        We don't need this part since the attributes of a diagnosis include a code and a description:

                               One ore more codes  (for each diagnosis)

                        But a diagnosis record can also relate to many problem records. Take these two diagnosis records:

                         

                        Code        Description

                        E11          Type 2 diabetes mellitus

                        E1121      Type 2 diabetes mellitus with diabetic nephropathy

                         

                        Each of these diagnoses may relate to one problem for one encounter record, and a different problem for a different encounter record.

                         

                        Problem records may relate to many diagnosis records and diagnosis records may relate to many problem records. Doesn't that require a many-to-many relationship, and therefore a join table?

                         

                        Thanks,

                        Steve

                        • 9. Re: ICD10 Code Picker And Nested Portals
                          SteveFransen

                          Hi David,


                          So, if this is the right design:

                          Patient ---< Encounter ---< Problem ---< DiagnosisStatus >--- DiagnosisCode (ICD10 codes)

                           

                          What are my layout options other than an Encounter layout with a Problem portal, with a DiagnosisStatus portal on each portal row of the Problem portal, with a popover on each portal row of the DiagnosisStatus portal, with a DiagnosisCode portal on the popover from which to pick a DiagnosisCode... since those nested portals aren't supported.


                          I believe I could put this together with scripts and several layouts but I'm wondering if there's a more elegant solution using the fundamental relationships.


                          I think the picker is actually a second problem to be solved after I get a layout structure in place that takes advantages of the relationships.


                          Thanks,

                          Steve