8 Replies Latest reply on Feb 11, 2016 11:24 AM by jtamayo

    A way to group records?



      I have a consultation table that has fields for

      • the date of the consultation
      • consultant's name (ID# pulled from a Consultant table)
      • the person they consulted with (ID # that pulls records from a People table)
      • the length of the session


      All of this information is included in a portal I have on a Consultant layout, that looks a little something like this:




      DateName (this is the person they consulted with)Length of Session (in min)
      1/15/2016John Smith30
      1/30/2016Sean Thompson60
      1/30/2016Jessica Patterson60
      1/30/2016Elisa Simpson60

                                                                                                                                                  TOTAL SESSION TIME: 3.25 HOURS



      Then the problem becomes when there is more than one person involved in the consultation.  Is there a way to group records of a similar consultation so that if say Sean, Jessica, and Elisa were part of the same consultation, their times would only be counted once in the Total Session Time?  How would you go about fixing this so that it recognizes that they're part of the same consultation? I'd appreciate any help on this!  Thanks!

        • 1. Re: A way to group records?

          A limitation of a reported list is that one record = 1 row, so that one shared consultation can't appear 3 times in 3 different sections.


          You could use the virtual list method to create this report, or you could create 3 separate records for a consultation involving 3 consultants. Either way, you could calculate and total a "split time" which is the session time of the consultation divided by the number of consultants.



          Chris Cain


          • 2. Re: A way to group records?

            Hmmm...I'm not familiar with the virtual list method.  I'll look that up.  Thanks!


            What if I were to create an SessionID field?  Give those who are all part of the same session, the same SessionID?  So, using the example, the three people would all have a SessionID of 1.  Then could there be a way to tell the database that if records have the same SessionID, add up the session lengths (in this case 180) and divide by the number of sessions with the same SessionIDs (3)?


            (Did any of that make sense?  My coffee's starting to wear off...)

            • 3. Re: A way to group records?

              3 * 60 + 30 = 3.5 hours, not 3.25, something with your calc is wrong, to start with.


              Another question: Person comes in the morning, is seen by 2 consultants, is ordered some exams, comes back in the afternoon and is seen by 2 consultants, one of which has already seen her in the morning. How do you handle that, at the input level ?

              • 4. Re: A way to group records?

                Oops sorry about that.  Changed the times but forgot to change the total.

                As for what you're saying, the way things are currently set up, it would be put in as 4 records on our Consultations layout which looks like:


                (automatically generated record ID)DateParticipant NameConsultant NameLength of Session
                12/11/2016John SmithConsultant 130
                22/11/2016John SmithConsultant 230
                32/11/2016John SmithConsultant 130
                42/11/2016John SmithConsultant 330


                This information is later portaled in two other layouts, one for the participant and one for the consultant.  Which look something similar to:



                John Smith


                (automatically generated record ID)DateConsultant NameLength of Session
                12/11/2016Consultant 130
                22/11/2016Consultant 230
                32/11/2016Consultant 130
                42/11/2016Consultant 330

                TOTAL SESSION TIME: 2 HOURS  (should be 1 hour)


                Consultant 1

                (automatically generated record ID)DateParticipant NameLength of Session
                12/11/2016John Smith30
                22/11/2016John Smith30

                TOTAL SESSION TIME: 1 HOUR



                The problem I have in my original post is kind of the opposite of this (1 consultant, 2 participants), but the same problem happens just in a different portal.  See how John Smith's total session time is off an hour?  I thought maybe including a SessionID number for those with more than 1 consultant/participant...

                • 5. Re: A way to group records?

                  Hi! I'm not sure why 2 hrs should be 1 hr in portal "Participant". The total of the portal rows is 120 (30+30+30+30). Are you trying to filter by Consultant 1 at the same time?



                  • 6. Re: A way to group records?

                    I'd suggest rethinking your data model. I recommend having a table called "Sessions" where you'd store the day, time, location, and purpose (and possibly outcome or recommendations) and another called "People" where you'd have people's names, contact info, etc. Then, in between the 2, you'd have "SPLinx", a join table (also called a link, merge, or bridge table) that would contain a record for every combination of session and person that actually occurred in your practice, along with a "Role" field that would identify why that particular person attended that particular session. For example, a series of "SPLinx" Records for the same session might look like this:

                         SPLSeq = 12345, SesSeq = 789, PerSeq =5678, Role = Participant

                         SPLSeq = 12346, SesSeq = 789, PerSeq =1005, Role = Therapist

                         SPLSeq = 12347, SesSeq = 789, PerSeq =2016, Role = Consultant

                    Session #789 would be for 30 minutes, and you'd only have to enter that datum once, in the "Sessions" table, but it would be visible in the "SPLinx" table because it would be linked to "Sessions" by means of the shared code number 789. Similarly, you'd have all your info on Participant John Smith under PerSeq 5678 in the "People" table and everything for your staff member Jessica Patterson under PerSeq 1005 in that same table, and they too would be visible to the "SPLinx" table by means of that shared code. And, of course, everything from "SPLinx" would be visible in the other direction as well.

                    • 7. Re: A way to group records?

                      Richard S. Russell wrote:


                      I'd suggest rethinking your data model. I recommend having a table called "Sessions" where you'd store the day, time, location, and purpose (and possibly outcome or recommendations)


                      and the Session (or Consultation, as OP calls it) total time.  Completely agree with all the rest.

                      • 8. Re: A way to group records?

                        Yes!  I see what you mean.  I'll go ahead and try that out.  Thank you all so much for the help!