6 Replies Latest reply on Jun 2, 2017 2:03 PM by clayhendrix

    Filter Report by Dropdown


      There is another discussion about this topic, but I cannot understand exactly what the author was trying to accomplish and therefore if the solutions offered would be helpful to me.


      I have a Student Information System solution for a school. The report I am working on is class rosters. The report works wells. It uses data to put each teachers students one page with his/her name at the top and the school term for which the roster applies.


      When users need to generate rosters (by running this report) for a term other than the one that I have coded in the script, they click find, type the school term into the field at the top of the report, perform the find, and then they have to resort to get the students in alpha order and sorted by teacher, so the pagination works. However, that simple find they can perform on this report does not give them the option to exclude dropped students (which is indicated in a field NOT printed on the report). The script that runs when the report opens, filters out dropped/inactive students, but they loose that filtering when the perform a find for a term other than the one I have in the script.


      What I would prefer NOT to do is write a script for every school term (into perpetuity) and create a separate button on the reports layout for each term.


      I think, with help, I can write a script that is triggered by changing a drop down box at the top of the roster report that will find students currently enrolled (not dropped/inactive) for that school term, sort by teacher name (for pagination of the rosters), and then sort by student last name.


      The field that is displayed at the top of the report is the actual field that is displayed elsewhere in the system for the registrar to indicated what school terms the student is enrolled, so at the top of the report, I have that field set for Find, but not browse, because I fear a user could change the values in the term field for multiple (or all) of the student records that the registrar maintains. That would be a mistake. That would lead to restoring from a backup.


      Should I create another table occurrence of the table occurrence for the table and also create a global field for the school term field and link the two tables in some way and display the global school term field at the top of the report?


      Other information:

      -school terms are controlled by a value list

      -the roster report is based upon the Enrollment table.
      -individual students have multiple records in the Enrollment table: one for each term in which they have been enrolled, are currently enrolled, and are enrolled for the future. The Enrollment table is linked to other tables that contain student demographic information, etc.


      Thanks, in advance, for your help.

        • 1. Re: Filter Report by Dropdown

          Well, you already have spelled out the script logic, so what's left is to implement that in script terms:


          New Window

          Enter Find Mode

          Go to Enrollment (Enrollment )

          Set Field [ Term::term ; ATable::gTermSelector ]

          If [ ATable::gExcludeDropOuts ]

            New Record/Request

            Omit Record

            Set Field [ Student ... whatver field and value you use to denote a drop-out ]

          End if

          Set Error Capture [ on ]

          Perform Find

          If [ not Get ( FoundCount ) ]

            Close window

            Show Custom Dialog

          End if

          Sort Records




          Student --< Enrollment >-- Term

          • 2. Re: Filter Report by Dropdown

            Set up your drop down on a different field. Make it a field with global storage specified in Manage | Database.

            Set up a script trigger on the drop down to run your script when the user selects a value from the drop down. Using a global field has several advantages:


            a) The user can't accidentally modify data thinking that they are in Find Mode when they are really in Browse.

            b) Different users can use this field to pull up a report at the same time as they won't see the value that another user enters into a global field

            c) a script can enter find mode and still use the value of a global field with a set field script step to set up find criteria and perform a find.


            See this discussion for multiple examples of scripts that use global fields in order to perform finds:


            Scripted Find Examples

            • 3. Re: Filter Report by Dropdown

              Thank you, philmodjunk. I am pretty sure you would have mentioned this if it were necessary, but does the global field have to go a different table or can it reside in the Enrollment table (the same table the layout is based upon)?

              • 4. Re: Filter Report by Dropdown


                Student --<Enrollment>--Term


                Set Field [ Term::term ; ATable::gTermSelector ] <-- Does that mean that gTermSelector MUST/SHOULD be in a table other than Term?


                Thank you.

                • 5. Re: Filter Report by Dropdown

                  clayhendrix wrote:

                  Set Field [ Term::term ; ATable::gTermSelector ] <-- Does that mean that gTermSelector MUST/SHOULD be in a table other than Term?

                  Where you store a global field is usually just a question of organization, except when you use it as a matchfield in a relationship; then it must reside in the table from where you define that relationship.


                  So in this case, define it where it makes sense to have it. I guess when I penned that code, I envisioned some sort of Dashboard/Report table.

                  • 6. Re: Filter Report by Dropdown

                    I'm going to try your script above. I'm going to modify the first few lines as I would like the script to be triggered after the user selects the desired term from a dropdown field. So, I'm going to have ATable::gTermSelector as a field displayed as a dropdown with a value list of terms based upon Term::terms.


                    I think that will work.