11 Replies Latest reply on Oct 3, 2012 9:42 AM by NaturSalus

    Portal Filtering in a Dashboard

    NaturSalus

      Title

      Portal Filtering in a Dashboard

      Post

            

           In the Dashboard layout, based on the Dashboard TO, I have a Portal based on the SchedulePatient TO with the following fields:

             
      •           Schedule:ScheduleDate
      •      
      •           

        Schedule:ScheduleYear

             
      •      
      •           

        Schedule:ScheduleMonth

             
      •      
      •           

        Schedule:ScheduleDay

             
      •      
      •           

        Schedule:ScheduleDayOfWeek

             
      •      
      •           

        Schedule:ScheduleTime

             
      •      
      •           

        Patient:NameFirstLast

             

           The established relationships are shown in the attached image.


           I know that some Portal fields look redundant (Schedule:ScheduleYear; Schedule:ScheduleMonth; Schedule:ScheduleDay; Schedule:ScheduleDayOfWeek) but I am still figuring out what is the best way to give the user all the possible filtering options.

            

           No issues with the Portal since it shows the right data.

            

      What I want to achieve, and I don't know what is the best way, is to set up multiple filters for the Portal, so that the user has absolute control on what is shown on the Portal.

           The Filter criteria that I want to use are:

             
      •           

                       show data per year

             
      •      
      •           

                       show data per year & month

             
      •      
      •           

                       show data per year & month & day of the week

             
      •      
      •           

                       show data per per date

             
      •      
      •           

                       show data per per date & time

             
      •      
      •           

                       show data per per date & day of the week & time & patient name

             

           To achieve that I thought of setting up a few global fields that would be the ones used by the user to select the: “year, month, date, day of the week, time and patient name” of the records shown on the Portal.

           The following global fields would be created in the Globals table:

             
      •           

        gYear

             
      •      
      •           

        gMonth

             
      •      
      •           

        gDate

             
      •      
      •           

        gDayOfWeek

             
      •      
      •           

        gTime

             
      •      
      •           

        gPatientName

             


            

           Then I though of setting up the following portal filter calculation:

      Globals::gYear = Schedule:ScheduleYear or

      Globals::gMonth = Schedule:ScheduleMonth or

      Globals::gDate = Schedule:ScheduleDate or

      Globals::gYear = Schedule:ScheduleYear or

      Globals::gDayOfWeek = Schedule:DayOfWeek or

      Globals::gTime= Schedule:Time or

      Globals::gNameFirstLast= Patient:NameFirstLast


            

           Concerning the Portal Refresh issue, I thought establishing the following cartesian relationships:

             
      •           

                       Schedule::Refresh x SchedulePatient::Refresh

             
      •      
      •           

                       SchedulePatient::Refresh x Patient::Refresh

             


            

           and OnLayoutEnter the Dashboard the following Refresh Cartesian Join script is triggered:

      Set Field [Get (Active Field Contents)]

      Set Field [Schedule::Refresh;1]

      Set Field [SchedulePatient::Refresh;1]


            

           The big question is what is the right context for the filter calculation given that:

             
      1.           

                       the Portal is on the Dashboard layout

             
      2.      
      3.           

                       the Portal is based on the SchedulePatient TO

             


            

           Does it make any sense?


            

           Thanks,

           natursalus

      relationship.jpg

        • 1. Re: Portal Filtering in a Dashboard
          philmodjunk

               You need a relationship linking Dashboard to either SchedulePatient or Schedule. I think it should link to Schedule, but let's check the details first.

               Am I correct that one record in Schedule represents one specific appointment date and time?

               That would then make sense for SchedulePatient to serve as the link between a patient record and an appointment date and time, but is it possible to schedule multiple patients for the same time slot? If not, you don't need the join table.

               You won't need the dedicated Year, month, day etc. fields in order to make the portal work as you can use functions to extract those values in your portal filter expression. (Unless we dispense with the portal filter altogether and just set up a filtered relationship instead of a filtered portal.)

               And with the right relationship--something I can't quite specify for you just yet, you won't need any script to refresh your portal, selecting the values in the global fields will update things automatically.

          • 2. Re: Portal Filtering in a Dashboard
            NaturSalus

                 Hello Phil,

                 Thanks for looking into my question.

            You need a relationship linking Dashboard to either SchedulePatient or Schedule.

            My mistake I attached the wrong image. I am attaching the right image.

            Will do the following relationship: SchedulePatient::__kp_SchedulePatient X Dashborad::zz-Constant ?

                  

            Am I correct that one record in Schedule represents one specific appointment date and time?

            Yes

            That would then make sense for SchedulePatient to serve as the link between a patient record and an appointment date and time, but is it possible to schedule multiple patients for the same time slot? If not, you don't need the join table.

                 Don't get you quite here.

            is it possible to schedule multiple patients for the same time slot?

                 Since the db is just for one Therapist, the answer is NO. Each time slot is for attending just one patient.

            If not, you don't need the join table.

                 I guess I got you now. So the right relationship is Schedule --< Patient?

            You won't need the dedicated Year, month, day etc. fields in order to make the portal work as you can use functions to extract those values in your portal filter expression. (Unless we dispense with the portal filter altogether and just set up a filtered relationship instead of a filtered portal.)

                 So the choice is between using the portal filtering option of FM 11 with "extracting "functions or set up a filtered relationshp.

                 I guess, a filtered relationship looks easier to me since I have no clue about the "extractiing" functions. Would it do just one filtered relationship that takes into consideration all the possible options or do I need to set up a different relationship for each filtered option?

                 Thanks.

                 natursalus

                  

                  

                  

                  

                  

                  

                  

                  

            • 3. Re: Portal Filtering in a Dashboard
              philmodjunk

                   As discussed, you need this relationship:

                   Dashboard-------<Schedule>------Patient

                   Sometimes I think better of a suggestion when I've taken the time to think it through more carefully. While it IS possible to use a filtered relationship, the need to flexibly specify a wide reange of time periods that may or may not also include the ID for a specific patient argues for a portal filter expression. And those functions I mentioned should look pretty familiar to you once you see the example filter expression I'm about to post:

              Dashboard::gYear = Year ( Schedule:ScheduleDate ) or

              Dashboard::gMonth = Month ( Schedule:ScheduleDate ) or

              Dashboard::gDate = Schedule:ScheduleDate or

              Dashboard::gDayOfWeek = DayOfweek ( Schedule:Date ) or

              Dashboard::gTime= Schedule:Time AND

              If ( IsEmpty ( Dashboard::gNameFirstLast ) ; True ; Dashboard::gNameFirstLast= Patient:NameFirstLast )

                   This probably isn't the final form of your filter expression as it won't allow you to specify a date AND a time, but it does filter a given time period for a specified patient. I'd try this expression first and then explore variations that would use And with either If or Case to produce a filter expression that allows you to specify both date and Time.

                   Also note that you can use inequalities in this filter expression to match to a range of dates or range of times such as:

                   Dashboard::gDate1 < Schedule::Date AND Dashboard::gDate2 > Schedule::Date

                   Assuming the above filter expression, make your relationship this:

              Dashboard::gYear X Schedule::anyfield AND

              Dashboard::gMonth X Schedule::anyfield  AND

              Dashboard::gDate X Schedule::anyfield AND

              Dashboard::gDayOfWeek X Schedule::anyfield  AND

              Dashboard::gTime X Schedule::anyfield AND

              Dashboard::gNameFirstLast X Schedule::anyfield

                   By including the filter fields with the X operator as part of the relationship, changing the value in one of these fields should automatically update your portal without the need for any scripting.

              • 4. Re: Portal Filtering in a Dashboard
                NaturSalus

                     Hello Phil,

                     Many thanks for your detailed directions, I'll try them tomorrow and I'll let you now how it went.

                     natursalus

                • 5. Re: Portal Filtering in a Dashboard
                  philmodjunk

                       Note:

                       If Dashboard::Month stores the name of the month, use the MonthName function instead of Month--which returns the month number.

                  • 6. Re: Portal Filtering in a Dashboard
                    NaturSalus

                         Phil,

                    If Dashboard::Month stores the name of the month, use the MonthName function instead of Month--which returns the month number.

                         Yes, Dashboard::Month stores the name of the month

                         I take good note of your note

                         thanks,

                         natursalus

                    • 7. Re: Portal Filtering in a Dashboard
                      NaturSalus

                            

                           Hello Phil,

                           Everything is working as expected with the exception of portal filtering by gNameLast which doesn't show any record.

                           The only change that I have made is that I am using gNameLast field instead of the gNameFirstLast field


                           so I have modified the Dashboard X Schedule relationship

                           from

                      Dashboard::gNameFirstLast X Schedule::anyfield

                           to

                      Dashboard::gNameLast X Schedule::anyfield


                            

                           I have created the following field in the Schedule table:

                      Schedule::PatientNameLast (calculation, text) = Patient::NameLast


                            

                           In the portal filter calculation I have added:

                      Dashboard::gNameLast = Schedule::PatientNameLast

                           so, the whole portal filter calculation is as follows:


                            

                      Dashboard::gYear = Year ( Schedule::ScheduleDate ) or

                      Dashboard::gMonth = MonthName ( Schedule::ScheduleDate ) or

                      Dashboard::gDate = Schedule::ScheduleDate or

                      Dashboard::gDayOfWeek = DayName ( Schedule::ScheduleDate ) or

                      Dashboard::gTime = Schedule::ScheduleTime or

                      Dashboard::gNameLast = Schedule::PatientNameLast and


                            

                      If (IsEmpty ( Dashboard::gNameLastFirst ) ; True ; Dashboard::gNameLastFirst = Patient::NameLastFirst )


                            

                           The filters for year, month, date, day of the week, and time work as expected; but the filter for gNameLast doesn't show any records.

                            

                           Any ideas what's wrong?

                            

                           thanks,

                           natursalus

                      • 8. Re: Portal Filtering in a Dashboard
                        philmodjunk

                             Is gNameLast a text field or a number field?

                        • 9. Re: Portal Filtering in a Dashboard
                          NaturSalus

                                

                          Is gNameLast a text field or a number field?

                               It is a text field

                          • 10. Re: Portal Filtering in a Dashboard
                            philmodjunk

                                 Then this should work as long as the text in both fields is exactly the same. (Won't work as written if you only enter part of a last name into the global field or if the patient's last name field contains invisible characters such as a space or return character.)

                            • 11. Re: Portal Filtering in a Dashboard
                              NaturSalus

                                   Got it!

                                   in the portal filter I had:

                              If (IsEmpty ( Dashboard::gNameLastFirst ) ; True ; Dashboard::gNameLastFirst = Patient::NameLastFirst )

                                   instead of:

                                   If (IsEmpty ( Dashboard::gNameLast) ; True ;Dashboard::gNameLast  = Patient::NameLast )

                                    

                                   Now I have a working filtered portal in a dashboard!yes

                                    

                                   thanks,

                                    

                                   natursalus