3 Replies Latest reply on Apr 15, 2016 12:19 AM by erolst

    Updating & Indexing Student Status

    abbadonnergal

      My client's solution is used to manage a school (with Students enrolled in Classes). Between the Students table and the Classes table there's a ClassDetail table that enables users to "enroll" students in a class... Standard stuff.

       

      The client asked for a "Status" field in the Student table that updates whenever students are enrolled or not enrolled in classes (based on related values in the ClassDetail table). I accomplished this with an unstored calc (see below), but the users want to perform finds on that field and it's unbearably slow given the volume of Student records.

       

      I think the best solution would be to revert this to an indexed text field and update it via script triggered by key events: students' enrollment in a class, a class ending, etc. But there may be a better way.


      Does anyone have any suggestions? Thanks.

       

      zcStatus field in Students table:

       

      Case (

      //--------------------------------------------

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "CURRENT"    )  ≥ 1

      and

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "UPCOMING"    )  = 0

      ;

      "ACTIVE" ;

      //--------------------------------------------

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "CURRENT"    )  = 0

      and

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "UPCOMING"    )   ≥ 1

      ;

      "FUTURE" ;

      //--------------------------------------------

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "CURRENT"    )     ≥   1

      and

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "UPCOMING"    )    ≥  1

      ;

      "CONTINUING" ;

      //--------------------------------------------

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "CURRENT"    )  = 0

      and

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "UPCOMING"    )   = 0

      and

      PatternCount (

      List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

      "COMPLETE"    )    ≥ 1 ;

      "INACTIVE" ;

      //--------------------------------------------

      "WILL CALL"

      //--------------------------------------------

               )

        • 1. Re: Updating & Indexing Student Status
          Extensitech

          Regardless of whether you use this as an unstored calc or you're setting this result with a script trigger, you should definitely use a Let to evaluate "List ( stu_CDT__ClassDetail|StudentID::zcClassStatus )" just once, rather than 9 times, and evaluate PatternCount ( statuses ) ; "CURRENT"    ) and ..."UPCOMING" etc. only once each, too.

           

          You'll probably still need to consider setting an indexed field via script, but this change even on its own should show some definite improvement.

           

          HTH

          Chris Cain

          Extensitech

          • 2. Re: Updating & Indexing Student Status
            abbadonnergal

            So I implemented the Let function to set a Variable for the "List (stu_CDT__ClassDetail|StudentID::zcClassStatus )", which improved the speed of the query. But when you say "evaluate PatternCount (statuses) ; "CURRENT" ) and ... "UPCOMING" etc. only once each, too." I'm not exactly sure how to implement that suggestion, cause I'm testing for 2 different conditions in each instance to calculate the status. This is what I'm left with:

             

            Let ( [

             

            ~Statuses =  List ( stu_CDT__ClassDetail|StudentID::zcClassStatus )

             

            ] ;

             

            //-------CALCULATION-------------

             

            Case (

             

            //--------------------------------------------

             

            PatternCount (

             

            ~Statuses ;

             

            "CURRENT"    )  ≥ 1

             

            and

             

            PatternCount (

             

            ~Statuses ;

             

            "UPCOMING"    )  = 0

             

            ;

             

            "ACTIVE" ;

             

            //--------------------------------------------

             

            PatternCount (

             

            ~Statuses ;

             

            "CURRENT"    )  = 0

             

            and

             

            PatternCount (

             

            ~Statuses ;

             

            "UPCOMING"    )   ≥ 1

             

            ;

             

            "FUTURE" ;

             

            //--------------------------------------------

             

            PatternCount (

             

            ~Statuses ;

             

            "CURRENT"    )     ≥   1

             

            and

             

            PatternCount (

             

            ~Statuses ;

             

            "UPCOMING"    )    ≥  1

             

            ;

             

            "CONTINUING" ;

             

            //--------------------------------------------

             

             

            PatternCount (

             

            ~Statuses ;

             

            "CURRENT"    )  = 0

             

            and

             

            PatternCount (

             

            ~Statuses ;

             

            "UPCOMING"    )   = 0

             

            and

             

            PatternCount (

             

            ~Statuses ;

             

            "COMPLETE"    )    ≥ 1 ;

             

            "INACTIVE" ;

             

            //--------------------------------------------

             

            "WILL CALL"

             

            //--------------------------------------------

             

                     )

                     )

            • 3. Re: Updating & Indexing Student Status
              erolst

              Looking at your calculation formatting, I think there can be too much of a good thing (like comments and candy), up to point where it becomes almost unreadable.

               

              Also, if you use Let(), do it consequently, just like Chris suggested:

               

              Let ( [

                stati =  List ( stu_CDT__ClassDetail|StudentID::zcClassStatus ) ;

                hasCurrent = PatternCount ( stati; "CURRENT" ) ;

                hasUpcoming = PatternCount ( stati ; "UPCOMING" ) ;

                hasComplete = PatternCount ( stati ; "COMPLETE" ) ;

                ] ;

                Case (

                  hasCurrent and hasUpcoming ;

                  "CONTINUING" ;   

                  hasCurrent and not hasUpcoming ;

                  "ACTIVE" ;

                  hasUpcoming and not hasCurrent ;

                  "FUTURE" ;

                  hasComplete and not hasUpcoming and not hasCurrent ;

                  "INACTIVE" ;


                  "WILL CALL"

                )

              )


              Let() not only lets the computer work smarter, it also helps you make your logic clearer.


              This can probably be simplified even more, but I'm only on my first cup of coffee …