10 Replies Latest reply on Dec 28, 2011 10:24 AM by datastride

    Dynamics sorting of value list

    Bsabowitz

      Hi all,

       

      I've been working no this for a few days and don't seem to be getting anywhere. I am developing a solution to use as an electronic medical record for my personal medical practice. I have a table (ICD9 table) that contains over 1400 diagnoses with associated diagnostic codes (called icd - 9 codes, thus the name of the table). This table is related to a table that contains individual patient records (PATIENTS) through a join table that contains diagnoses (DIAGNOSES) for a given patient. I have a layout that is based on the join table in which I add a diagnosis for a given patient. I want to have a pull down menu based on a value list that lets me select a diagnosis from those in the ICD9 table. That part is easy and works great. What I want to do, however, is track how often I choose a given diagnosis (for example 'high blood pressure') and have the value list sorted with the most common diagnosis at the top, followed by the next most common diagnosis, etc. Over time, I want to have this list continue to self sort based on the frequency of the given diagnoses.

       

      Any ideas would be greatly appreciated.

       

      Thanks,

       

      Brian Sabowitz

        • 1. Re: Dynamics sorting of value list
          RubenVanDenBoogaard

          Hi Brian,

           

          If you make a number field 'Frequency" in your ICD9 table and fill that with the count(Diagnoses::DiagnoseID)  (use a looping script or a replace to update this value every once in a while)

          Alternatively you could use a scripttrigger to add 1 to that field every time you add it to a diagnose.

           

          Now the sorting bit; You could add a frequency count number to the name of the diagnose and sort on the name where you want the highest number to be the lowest in the sort order, so you

          could use something like  Right("0000" & (9999-Frequency) ; 4)& "  "& Diagnose name

           

          Hope this helps.

           

          Best regards,

           

          Ruben van den Boogaard

          Infomatics Software

          ruben@infomatics.nl

           

           

           

          created by Bsabowitz in General - View the full discussion

          Hi all,

           

           

            I've been working no this for a few days and don't seem to be getting anywhere.  I am developing a solution to use as an electronic medical record for my personal medical practice.  I have a table (ICD9 table) that contains over 1400 diagnoses with associated diagnostic codes (called icd - 9 codes, thus the name of the table).  This table is related to a table that contains individual patient records (PATIENTS) through a join table that contains diagnoses (DIAGNOSES) for a given patient.  I have a layout that is based on the join table in which I add a diagnosis for a given patient.  I want to have a pull down menu based on a value list that lets me select a diagnosis from those in the ICD9 table.  That part is easy and works great.  What I want to do, however, is track how often I choose a given diagnosis (for example 'high blood pressure') and have the value list sorted with the most common diagnosis at the top, followed by the next most common diagnosis, etc.  Over time, I want to have this list continue to self sort based on the frequency of the given diagnoses.

           

           

             Any ideas would be greatly appreciated.

           

           

          Thanks,

           

           

          Brian Sabowitz

           

          Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

          Start a new discussion in General by email or at FileMaker Technical Network

          Manage your email preferences.

           

          FileMaker Developer Conference 2012 • Miami, Florida • July 16-18 • www.filemaker.com/devcon

           

          • 2. Re: Dynamics sorting of value list
            usbc

            Brian,

            If you have a "Calc_1" field (results in the number 1) in the DIAGNOSES table you can then use a field Sum(DIAGNOSES::Calc_1) in your ICD9 table.

            Sort on that.

            You don't expressly sat that each diagnoses has a unique ID but I'm assuming that you do.

            Chuck

            1 of 1 people found this helpful
            • 3. Re: Dynamics sorting of value list
              datastride

              Brian,

               

               

               

              The suggestions offered so far are great, but for greater flexibility and sophistication I would recommend a plug-in like CNS Menu.

               

               

               

              This plug-in lets you control the look and sequencing of drop-down menus from a script. With this approach, you would define your entry field as a button that calls a script that displays the menu. In this script, you can do several things that could make data entry quicker and easier. For example, you could:

               

               

               

              1.       Sort your list of menu items before displaying the menu (based on a frequency of use count, for example)

               

              2.       You could append extra items to the menu that invoke additional “helper” functionality in your script

               

              3.       You might designate (from your own experience) 10 of the most-often used items to put permanently at the top of the list (and separated from the remaining items with a line) and put these in some unchanging order that makes it easy to know where these oft-used codes will appear. (And you can then sort the remainder of the list based on frequency of usage, of course.)

               

              4.       Decide how to sort based on the patient’s age range and gender, so, for example, “hysterectomy” doesn’t even appear for a child under 12 or a man.

               

              5.       You could even look at any codes used previously for the current patient and decide to put some of these, or a list of some related codes, at the top of the menu.

               

              6.       Once a particular code was chosen, your script could increment the use count for that item (and, if there had been a different item in the field, decrement the use count for that earlier-entered item).

               

              7.       To make things really cool, you define some group name for each of those 1400 items, and display a hierarchical menu where only the 25 group name are displayed … until you select a group, and once a group is selected, then all the items in that group are presented. And you could have multiple (and varying) sub-groups under any or all groups. This can really make it easy to sift thru a huge list of possibilities.

               

               

               

              If a user chose from the menu, for example, a helper function like “Search for item by name” from the menu, your script could (a) position the cursor in the entry field, (b) let you type in some part of the wording of a particular diagnosis, (c) perform a find to locate any and all items that match the word or words you typed in, and (d) then display a second, much shorter menu of just the matches found.

               

               

               

              All of this may sound complicated, but it would only  take a few dozen lines of script to get started, and you can always add more functionality as you think of it by just adding code to your script. And once you discover the magic of fully customizable and CONTEXT SENSITIVE menus. It’s amazing how much more user-friendly you can make things when you can add intelligence to your drop-down menus.

               

               

               

              The bottom line is using a menu plug-in like CNS Menu is a real game-changer. You can provide much more sophisticated user interfaces because you can incorporate both your own wisdom AND any context or statistical data to decide how the menu are presented to users. And it really is easy to learn and use.

               

               

               

              Peace, love & brown rice,

               

              Morgan Jones

               

               

               

              FileMaker + Web:  Design, Develop & Deploy

               

              Certifications: FileMaker 9, 10 & 11

               

              <http://www.onepartharmony.com/> One Part Harmony 

               

              Austin, Texas • USA

               

              512-422-0611

              • 4. Re: Dynamics sorting of value list
                Bsabowitz

                Thanks Ruben,

                 

                  That works.  I should of made it clear that I had no trouble with the "Frequency" field idea.  I just couldn't get it to sort with the highest number at the top.  I should of thought to subtract it from a large number. 

                 

                Thx,

                 

                 

                • 5. Re: Dynamics sorting of value list
                  Bsabowitz

                  So, for each diagnosis record in the DIAGNOSIS table I set the field Calc_1 = 1, and then add up the number of Calc_1's for a specific ICD-9 code?  I can see how that would work but can I sort on that field in highes to lowest order?  Let me try it and I'll let you know how it comes out.  Rueben gave me the idea of subtracting the counter from a high number to get it to sort in reverse order  (see thread) but the drop down then shows a large number in front of the diagnosis which is not pretty. 

                   

                  I'll keep you all posted. 

                   

                  Thx,

                   

                  b

                  • 6. Re: Dynamics sorting of value list
                    Bsabowitz

                    Hi Morgan,

                     

                      I purchased CNS_Menu and I am excited by what it appears to be able to do.  However, the documentation is pretty sparse and the few examples they give don't easily translate into my database.  I'm sure this is largely due to my being a novice.  I was wondering if you have any additional examples that show a more complete use of the plugin to help get me started?  Any additional input would be greatly appreciated.

                     

                    Thanks,

                     

                    Brian

                    • 7. Re: Dynamics sorting of value list
                      Bsabowitz

                      Hi Morgan,

                       

                        I purchased CNS_Menu and I am excited by what it appears to be able to do.  However, the documentation is pretty sparse and the few examples they give don't easily translate into my database.  I'm sure this is largely due to my being a novice.  I was wondering if you have any additional examples that show a more complete use of the plugin to help get me started?  Any additional input would be greatly appreciated.

                       

                      Thanks,

                       

                      Brian

                      • 8. Re: Dynamics sorting of value list
                        datastride

                        Brian,

                         

                        I have a bunch … but they are all in production databases. That said, I recommend you write a simple script to get started. The script would consist of these steps:

                         

                        1. Set a local variable to a value list that represents the choices for your menu

                        2. Call the “Quick Define” function of CNS Menu to "store" the menu

                        3. Call the “Show Menu” function of CNS Menu to display the menu and accept the user's selection

                        4. Test to see if the user dismissed the menu without selecting anything

                        5. If the user selects an item, display a dialog to confirm the selection

                         

                        Here’s a link to download a little example database that has two sample scripts that perform the steps outlined above.

                         

                        Note: This example assumes you have registered the CNS Menu plug-in.

                         

                             http://dl.dropbox.com/u/28923384/CNS%20Menu%20Example.fp7

                         

                        Peace, love & brown rice,

                        Morgan Jones

                         

                        FileMaker + Web:  Design, Develop & Deploy

                        Certifications: FileMaker 9, 10 & 11

                        One Part Harmony 

                        Austin, Texas • USA

                        512-422-0611

                        • 9. Re: Dynamics sorting of value list
                          Bsabowitz

                          Thanks Morgan,

                           

                            I'll play with this a bit. 

                           

                           

                          • 10. Re: Dynamics sorting of value list
                            datastride

                            Brian,

                             

                             

                             

                            I added a third example in my little database that shows two additional cool features:

                             

                             

                             

                            ·         How to sort the menu items using a pair of custom functions authored by Mogens Brun (from the Brian Dunning repository). Of course you’ll need a copy of FileMaker Pro Advanced to be able to copy these custom functions into your database.

                             

                            ·         How to add an extra item (“None of the above”) to a previously sorted set of menu items.

                             

                             

                             

                            Here’s the new link:

                             

                             

                             

                                 http://dl.dropbox.com/u/28923384/CNS%20Menu%20Example.fp7

                             

                             

                             

                            The cool thing is that you can just keep adding menu items by appending things to the local variable that holds these, sort them any way you want, group them as you like, add sub-menus as needed, highlight menu items … You can experiment with the example database to customize the menus.

                             

                             

                             

                            Let me know if you have other questions  …

                             

                             

                             

                            Peace, love & brown rice,

                             

                            Morgan Jones

                             

                             

                             

                            FileMaker + Web:  Design, Develop & Deploy

                             

                            Certifications: FileMaker 9, 10 & 11

                             

                            <http://www.onepartharmony.com/> One Part Harmony 

                             

                            Austin, Texas • USA

                             

                            512-422-0611