1 2 3 4 Previous Next 53 Replies Latest reply on Aug 30, 2012 4:12 PM by philmodjunk

    Counting Occurences of Values in Dropdown List

    neilgalang

      Title

      Counting Occurences of Values in Dropdown List

      Post

      Hi,

       

      I dont mean to be a baby, but this forum has been very helpful with my FM project-- I encountered another task, which is:

      Counting Occurences of Values in Dropdown List

      So basically I have a value list defined from a set of records named modalities (modalities are patient treatments)...these modalites are

       

      1 HEAT

      2 COLD

      3 TENS

      4 GROUP

       

      Then I have ten fields on my PATIENT MODALITIES layout which contain these values in the each of the ten fields, per patient modality record....

      I need a report or a summary that summarizes the

       

      1) number of modalities each patient has gone through ie

       

      Patient Rex Smith

       

      HEAT - 4

      COLD - 4

      TENS - 0

       

      and

       

      2) Number of instances of this modality per found set ie

       

      HEAT

      235 sessions

      COLD

      125 sessions

      GROUP

      50 sessions

       

       

      I think both processes are the same, its just a matter of sorting? I am having trouble and getting dizzy doing subsummary layouts....please help...thanks in advance gurus!

        • 1. Re: Counting Occurences of Values in Dropdown List
          philmodjunk

          You have a significant problem with your design:

          Then I have ten fields on my PATIENT MODALITIES layout which contain these values in the each of the ten fields, per patient modality record....

          With 10 different fields in every patient record with any one of the 10 storaing any one of the possible values from your value list, any attempts to count values becomes very cumbersome and you have imposed an artificial limit of 10 such values for any given patient--a limit you need not have.

          Replace these 10 fields with one related table, call it modalities and relate it to your patient table by PatientID.

          You can now place a portal to Modalities on your layout in place of those 10 fields and format the single field in this portal row with your value list of modalities. This portal can have 10 portal rows, but with a scroll bar, you are not limited to 10 nor need use up space for 10 rows if you don't want to. You also can use multiple portals to modalities to arrange these in columns or a grid if you need to.

          Now you can set up a report layout based on the Modalities table and Sorting with SubSummary layout parts and summary fields can work to produce a count either for one patient or a group of patients.

          • 2. Re: Counting Occurences of Values in Dropdown List
            neilgalang

            Hi! Being a new Filemaker developer I am very thankful for all the help I have received here. Even though I wasn’t able to meet my deadline I successfully remodeled the database according to your suggestion, wherein each treatment item (Rx Item) is now called from a set of related records via a Parent-child relationship.

             

            Default Rx_PK :: Default Rx_FK

             

            It also works perfectly but I have a few concerns regarding the new model.

             

            1)   How will be able to show all RX items in table view in the NEW MODEL as illustrated in the diagrams below?

             

            2)   As my main concern is to do reports in the end, it’s probably good to use your suggested model- but what is the fastest way to generate reports via IWP?  Is there an easy step to summarize reports in this way?

             

            Sample Report

             

            Name of Resident: Saul Hudson

            Treatments Attended:          MDS Minutes Total

             

            AROM                                                      45

            HEAT Therapy                                          55

            Walking Therapy                                      66

            Group Therapy                                         55

             

             

            Name of Therapist: Kevin Galang

            Treatments Given:                MDS Minutes Total

             

            AROM                                                      65

            HEAT Therapy                                          85

            Walking Therapy                                      76

            Group Therapy                                         85

             

             

            Or even present these in charts? I appreciate all the help in advance as I have been constructing this medical system for almost 8 months now. I hope I can come up with a solid solution soon.

             

             

            • 3. Re: Counting Occurences of Values in Dropdown List
              neilgalang

              heres image one original size

              • 8. Re: Counting Occurences of Values in Dropdown List
                philmodjunk

                My first advice is not to use table view. Table view is useful for you as the developer to get a quick and flexible over view of the data, but casual users can get into trouble with some details of the layout and it also limits you in key ways that don't exist in list view.

                First, your Sample report when NOT using IWP. (Reports are very limited in IWP, you may not be able to get all that you want when using a web browser to access yourdatabase.)

                Set up a list view layout based on the Modalities table. Add a Sub Summary layout part "when sorted by" PatientID. Put fields, such as the patient name in the sub summary layout. Remove the Body layout part and replace it with a second sub summary part when sorted by Modality.

                Put the modality field in this layout part. You can define a summary field that computes the "count of" the modality field and include it in this same sub summary part to show the sub total. If you put this same field in the first sub summary layout part, you get a count of total modality records for that patient. If you put it in the header, footer or a grand summary layout part, the same field gives you the total records in your report.

                Now perform a find for the records that you want in your report, it can be for one patient, a group of patients or only for specified modalities and a range of treatment dates can also be specified.

                Once you perform the find, sort your records, first by Patient, then by Modality.

                The catch for IWP is that sub summary layout parts are not supported in that interface. There are ways to approximate it starting from a table where you have one record for each row in the above report, but it is not a simple thing to set up nor nearly as flexible as the sub summary report.

                Back to the table view issue in your last screen shot. Your layout is not limited to fields from the RX table. You can also include fields from the Patients table. If you use an option in layout setup to make the header visible, you can include fields from Patients in the header and then perform a find to limit the modality records to only those related to a specified patient.

                Even better, make this a list view layout based on modalities and you can include fields from the patients table in the header, or if you want to see data on multiple patients, in a sub summary part when sorted by patientID. (But not in IWP.)

                • 9. Re: Counting Occurences of Values in Dropdown List
                  neilgalang

                  Thank you for your reply-- the thing is we are deploying this EMR on IWP, we already purchased a server and Filemaker Server Advanced, so NOT going IWP is NOT an option :(

                  should I just go back to the old layout, as shown in photos one and two? According to you it is possible to count the occurences of each treatment and compile them in a report, but it will be cumbersome. If it possible, I might as well experiment on that route due to time constaints and IWP limitations that you have mentioned. What are the steps if I wanted to count occurences in dropdown lists as shown on the first diagaram?

                   

                  Given all these, what are my options? Once again THANK YOU VERY MUCH!!

                  • 10. Re: Counting Occurences of Values in Dropdown List
                    philmodjunk

                    No do not go back to the original structure. That one's a nightmare for any kind of reporting of modality statisticts.

                    You'll need to carefull think through your options. If these reports can be prepared in advance and then downloaded, a script might be able to generate them as PDF's that can then inserted into container fields or viewed in web browsers.

                    If you must be able to set up a dynamic report where the user specifies criteria and then they get the report in their web browser, you are in for quite a bit of scripting to get the same totals and subtotals to appear in the browser.

                    It would require adding one new record to a report table each time a patient receives a treatment modality for the first time. The records in this related table (and this is in addition to the modalities table), would have these two data fields:

                    PatientID, Modality

                    From that table, I'll call it Patient_Modality, you can set up a relationship to Modalities like this:

                    Patient_Modality::PatientID = Modalities::PatientID AND
                    Patient_Modality::Modality = Modalities::Modality.

                    With a layout based on this table, you can place the summary field from Modality in the body of the layout and you will see the needed sub totals for each modality. And it is possible to filter the results by a date or range of dates by including global date fields defined in Patient_Modality in the above relationship.

                    You'll need to set up scripting that updates the Patient_Modality table as needed each time you submit new treatment data on a given patient.

                    • 11. Re: Counting Occurences of Values in Dropdown List
                      neilgalang
                      hi.. can i use other simpler routes for iwp? maybe count themusing summary and count fileds and totals instead?
                      • 12. Re: Counting Occurences of Values in Dropdown List
                        philmodjunk

                        How is that really simpler?

                        You will still end up creating one record for each patient-modality combination. It eliminates the need for linking it in a relationship to Modalities--so that part is simpler, but you pay for it with more complex scripts and keep in mind that these scripts have to correctly update such counts when you: A) add a new modality entry. B) Remove a modality entry entered/selected by mistake and C) Change a modality entry from one Modality to another. And you can't use a script trigger to perform the script to update your counts, you have to use a button to perform it.

                        • 13. Re: Counting Occurences of Values in Dropdown List
                          neilgalang
                          wll this script be good in creating the rx records? i am using a similar one in another area of the database- i just need to find out if it will copy the contents of a portal field and set it to a new report... Rx_Items::Rx_Items_PK = DailyCompleted_Rx::Rx_Items_FK .  Go to layout (Rx items) #add script steps here to perform a find to pull up the desired set of Rx items.  (sort by PK number add script button to Default Rx Plan of patient and sort for modalities of that patient) Go to Record/request/page [first] Loop         Set variable [$Rx_Items ; value: Rx_Items::Rx_Items_PK]         Go to Layout [DailyCompleted_Rx]         New Record/Request         Set field [DailyCompleted_Rx::Rx_Items_FK; $Rx_Items]         Go to Layout [Rx_Items]         Go to Record/request/page [next; exit after last] End Loop
                          • 14. Re: Counting Occurences of Values in Dropdown List
                            philmodjunk

                            Posting from an iPad by anychance? Seems like posts from one of those always mash the text together.

                            pasting and reformatting so that I can read it:

                            Go to layout (Rx items)
                            #add script steps here to perform a find to pull up the desired set of Rx items. 
                            (sort by PK number add script button to Default Rx Plan of patient and sort for modalities of that patient)
                            Go to Record/request/page [first]
                            Loop
                                    Set variable [$Rx_Items ; value: Rx_Items::Rx_Items_PK]
                                    Go to Layout [DailyCompleted_Rx]
                                    New Record/Request
                                    Set field [DailyCompleted_Rx::Rx_Items_FK; $Rx_Items]
                                    Go to Layout [Rx_Items]
                                    Go to Record/request/page [next; exit after last]
                            End Loop

                            This script can create records for the same patient with the same modality as previosly created records. It essentially copies the records already created during data entry. The script needs to create a new record for a specific comtination of PatientID and Modality only if that pairing of values does not already exist in the table. This is so you can produce the one row for each modality format you get with the sub summary layout parts in the summary report we are trying to reproduce in IWP.

                            I'd set up this relationship:

                            Rx_Items::PatientID_FK = DailyCompleted_Rx::PatientID_FK AND
                            Rx_Items::Modality = DailyCompleted_Rx::Modality

                            Enable "Allow creation of records via this relationship" for DailyCompleted_RX

                            Then use this loop for creating records in DailyCompleted_Rx, but only if one with the same two values does not already exist:

                            Loop
                                    Set field [DailyCompleted_Rx::PatientID_FK; Rx_Items::PatientID_fk]
                                    Go to Record/request/page [next; exit after last]
                            End Loop

                            Note that you stay on the layout for Rx_Items.

                            BTW, I realized part way through that I am using names from your sample script set to work with other tables and mixing in details from the Modalities table. You'll need to subsitute some names here to fit your actual tables.

                            1 2 3 4 Previous Next