6 Replies Latest reply on Apr 12, 2016 2:38 PM by mikebeargie

    Summarizing info in a portal

    bmdaly

      @

      Hello!

      I have a fairly simple database that is being asked a complicated question. First it is looking at the number of entries in a portal and wanting to know how many entries there are. And how many records have the same number of entries. Got that done beautifully subsummaries are my friend!

       

      Now what I am needing it to day with each of those groups, for it to summarize the number of portals that have the same entries. For example in my group of records that have 3 entries in each portal, I need to be able to say how many of those portals have entries xyz and how many of those portals that have entries abc.

       

      Any thoughts or am I chasing a dream?

       

      Thanks!

       

      !

        • 1. Re: Summarizing info in a portal
          mikebeargie

          So, you need a calculated field, that counts the number of parent records, that contain "something" in their child portal?

           

          Not chasing a dream, but two fields are required in your parent table.

           

          EG create an unstored calculation field called "ChildPatternCount" with a calculation of:

          Let ([

            childValues = List(YourChildTable::TheFieldSearching);

            check =

               Case(

                  PatternCount(childValues;"x") > 0 ; 1 ;

                  PatternCount(childValues;"y") > 0 ; 1 ;

                  PatternCount(childValues;"z") > 0 ; 1 ;

                   0 )

          ];

            check

          )

          Basically you check a list of the child values for those patterns, and if they exist, return the number "1" as a result.

           

          Then you can create a summary type field in the parent table that is just a sum of the "ChildPatternCount" field. That would give you the total count of any records that contain the patterns you searched for in the portals.

           

          That can be adapted and copied across as many different pattern fields you need to create. This is a basic form. There are other ways using more complicated queries (like ExecuteSQL() ), but they may be over your head at a basic level.

          • 2. Re: Summarizing info in a portal
            bmdaly

            So, the problem is I don't know what x y and z are and there could be up to 7 entries in the portal. There are a ton of different values/combinations. I am also needing to list the results for whatever is found in the portal along with the total number.

             

            To be more specific, I need to know the number of students that are taking subgroups of courses. I have been able to determine if the students are taking 1,2 3....7 courses, but now I need a "subsummary" to state, of the 55 students that are taking 2 courses, 5 are taking CS and PSYCH and 15 are taking CS and MATH etc, but I don't know necessarily what the combos are.

            • 3. Re: Summarizing info in a portal
              mikebeargie

              It would most likely be a lot easier for you to build a report then instead of trying to do it in a portal.

               

              The calculations you want to do are possible, but without working with your specific file, almost impossible to try and demonstrate.

               

              The Virtual reporting technique could be used spit the results back out into a portal for you. See this for a basic understanding of what you'd be getting into:

              http://www.seedcode.com/virtual-list/

              • 4. Re: Summarizing info in a portal
                bmdaly

                Unfortunately due to the confidentiality of data, I can't post the file. I will look at this and see if it will create a simple enough report for my client with the data they have given me. Thanks.

                 

                For the record, please be wary of making sweeping assumptions/statements like "they may be over your head at a basic level." It is insulting.

                • 5. Re: Summarizing info in a portal
                  erolst

                  bmzister wrote:

                  To be more specific, I need to know the number of students that are taking subgroups of courses. […] I don't know necessarily what the combos are.

                   

                  So find these combos:

                   

                  In Enrolment, create a self-join by student ID.

                   

                  Add a TO of Courses to the self-join TO by id_course. Sort it (so the same group is the same textual representation).


                  Create two calc fields: one as Count ( selfJoin::id ), one as List ( CourseTOForSelfJoin::courseName ). You can use Substitute ( List ( CourseTOForSelfJoin::courseName ) ; ¶ ; ", " ) to get a horizontal list.

                   

                  Create a report layout based on Enrolment. Add a sub-summary part for the count field, another one for the List calculation field, and a third one for Students::fullName (maybe you need a calc field that reverses the names to get correct sorting). Put the correct fields into the right parts.

                   

                  Find the Enrolment records for the desired year**, and sort by the three sub-summary break fields.

                   

                  (Untested.)

                   

                  EDIT: **If you want this report for a specific school year / semester, you'd need to add that as a predicate to the self-join relationship – which may be tricky since that data wouldn't be stored with an enrolment record, so it couldn't be used in (the right-hand side of) a relationship. But I guess a script line (in the report script that you need to write anyway) and a utility field could take care of that.

                   

                  Maybe first try the concept as such; if it succeeds, fine-tune …

                   

                  PS: Your thread title is misleading: you're not really interested in portals, but in related records; and IIUC, you also don't want to display your findings in a portal (which wouldn't make for a really satisfying display). Just to explain my totally ignoring anything portal …

                  • 6. Re: Summarizing info in a portal
                    mikebeargie

                    Apologies then. So I'll encourage you to fill out your (currently blank) public profile and get your certification badges so we can get to know you.