6 Replies Latest reply on Apr 13, 2012 10:17 PM by mthomas1506

    Newbie Question- Filtering a Portal based on a calculation

    mthomas1506

      Hi all,

       

      I've just begun developing a system for my employer- a school- and have been reading and learning so much here. This is my first question- hope you can help!

       

      Background

      I have a db called Students, which contains a table for personal details, including a calculated field containing the first letter of the student's last name. There is also a table called school for that info, including a text field for the students year/grade.

       

      My main db- Student Management- has a single table to record incidents relating to that student. The field relationships seem to be working well.

       

      The Plan

      We have almost 1000 students. As a way to quickly find the student you are interested in without typing anything, I have a portal down the side showing all student names, with a script set to display that students records when the name is clicked. To make this quicker, I want to be able to dynamically change the visible records in the portal based on last name and year/grade.

       

      To do this, I have two fields in Student Management called filterName and filterYear- both global fields which have the same data for all the fields in the table. The portal's filter is set to show records like this-

      (filterName = Students:Personal:SurnameInitial) and (filterYear = Students:School:Year)

       

      The Problem

      The above does not work. When I remove references to the grade, and filter with only the last name criteria, it works as expected, but if I try to filter further based on both criteria, it still works on name, but modifying the value in filterYear does nothing.

       

      Could someone confirm what the calculation in the portal filter should be? Or, give me ideas where else to look?

       

      Thanks in advance,

       

       

      Mark

        • 1. Re: Newbie Question- Filtering a Portal based on a calculation
          Nerico

          I'm not sure I understand how your tables are set up.  Why is the student's year/grade field on a separate table?

           

          Also, you do not need a separate calculated field for the first initial, you can use the left() function on your calculation.  Assuming your filed is called LastName, Left(LastName,1) will return the first initial.

          1 of 1 people found this helpful
          • 2. Re: Newbie Question- Filtering a Portal based on a calculation
            Stephen Huston

            You need to make your filter calculation a CASE statement which will perform slightly different test depending on whether OR NOT values have been selected in each filter or are empty.

             

            If there are 2 filters, there need to be 4 case results possible. Your calculation can either test the field/filter contents or their pattern counts to determine what are matching results.

            • 3. Re: Newbie Question- Filtering a Portal based on a calculation
              mthomas1506

              Hi Nerico,

               

              The Student info is in a separate db because I have future plans to reuse that data in other areas later, and don't want to deal with duplicates.

              Regarding the tables, it just made sense to me to have within the Students db a table for personal details like name, DOB, address etc, and a separate table for all the school-related data such as year group, class/homeroom, etc.

               

              Is there any performance impact from having multiple tables? I figured it wouldn't, and whether I'm referencing a field at Students::Personal::LastName or Students::School::LastName wouldn't make a difference.

               

              Thanks for the info about the "initial" field. That function is exactly how I calculate the values in that field, but it didn't occur to me to just do that calc directly in the filter- saves one field. I'm all for simplicity.

              • 4. Re: Newbie Question- Filtering a Portal based on a calculation
                mthomas1506

                Hi Stephen,

                 

                Thanks for your reply- I'm sorry for being obtuse, but could you provide an example of how I might use the CASE function in portal filtering? I'm unsure what I would want in the "result" section of it.

                 

                What I don't get is why the folowing code doesn't work-

                 

                Each portal record will be visible when:

                (Incidents::gFilterLetter  = Personal::LastNameInitial) and (Incidents::gFilterYear  = School::YearGroup)

                • 5. Re: Newbie Question- Filtering a Portal based on a calculation
                  Stephen Huston

                  Your current test requires that neither filter be empty and that their content match exactly what is in the record fields.

                  • (Incidents::gFilterLetter  = Personal::LastNameInitial) and (Incidents::gFilterYear  = School::YearGroup)

                   

                  It's the part about neither filter being empty that needs to be handled. It's not really a CASE statement that's required, but a calculation which has several possible TRUE options since filtering is a boolean test:

                   

                  ( isEmpty ( Incidents::gFilterLetter ) and isEmpty ( Incidents::gFilterYear ) ) or

                  ( isEmpty ( Incidents::gFilterLetter ) and Incidents::gFilterYear  = School::YearGroup ) or

                  ( Incidents::gFilterLetter  = Personal::LastNameInitial ) and isEmpty ( Incidents::gFilterYear ) ) or

                  ( Incidents::gFilterLetter  = Personal::LastNameInitial and Incidents::gFilterYear  = School::YearGroup )

                   

                  This should allow filtering on either field individually if the other is blank, or on both if their both filled in, or on neither if both filters are empty.

                  • 6. Re: Newbie Question- Filtering a Portal based on a calculation
                    mthomas1506

                    Along with some rectification of some field relationships, your code has made this work perfectly. More importantly, I understand what it's doing as well

                     

                    Thanks so much!

                     

                    Mark