4 Replies Latest reply on Dec 19, 2012 5:53 AM by bvondeylen

    Narrowing a Portal List



      Narrowing a Portal List


           I have a database of students from a number of years. I would like a list view that I can gradually "narrow". I would like a row of popup menus to perform this "narrowing".

           I would first like to have a popup menu for school year, and have the list only display students from that year.

           Then a popup menu for School so only those students in a specific school (from that previously selected school year) would display.

           Then a popup menu for Grade Level, so only those students in a School Year / School / Grade Level would display.

           Then finally a popup menu with teachers, so only those students in a School Year / School / Grade Level / Teacher would display.

           I am currently stumped how to do this.

        • 1. Re: Narrowing a Portal List

               What version of FileMaker are you using?

               Does this really need to be a portal or could a list view listing students also work for you?

               Will you be editing the student data on this layout or is it only for display purposes?

               It's possible to set up a filtered portal (if using FileMaker 11 or newer) where each of your popup menus is set up on a field that is referenced by the portal filter expression. But the filter expression needed will be vary complex and the filter won't automatically update when a value in one of these fields is changed unless we either: Include that field in the portal relationship with the X operator or use a script with the undesirable Refresh Window [flush cached join results] step.

               A list view approach can handle thes issues a bit more simply by performing finds or constraining the found set.

               And if a read only display of this data is all that you need and you have FileMaker 12, you can set up a calculation field with ExecuteSQL to produce this list of student information modified via pop ups.

          • 2. Re: Narrowing a Portal List

                 I do have FileMaker Pro 12, and the database resides on a FileMaker Pro Server 12 Advanced

                 I would prefer a List (vs a Portal), so that sound promising.

                 The ExecuteSQL sounds good, but I know little of SQL. How would something like this look?

                 The listing would be for display only purposes. The data is coming from an ESS table on a Microsoft SQL Server (our student information system).


            • 3. Re: Narrowing a Portal List

                   Since a list view works, that would appear simpler and more flexible than using ExecuteSQL for this.

                   Make your pop up fields fields that have global storage specified. This way a script that performs a find or constrains a found set can access the value of this field while in find mode to set up the needed find requests.

                   Here's one possible approach you can experiment with. I'm going to just do this for two pop up fields, School Year and School, but if that works for you, it can easily be extended to incorporate values selected in the other pop ups.

                   The first script performs a find for all records with a given year:

                   Enter Find Mode  []--> clear the pause check box
                   Set Field [Students::Year ; GlobalFields::Year ] ---> global fields::year is your pop up field and must have global storage specified
                   Set Error Capture [on]
                   Perform Find[]
                   Sort [Restore ; no dialog ] ---> optional, but an alphabetic sort on student names makes sense to me.

                   The second script narrows the result of the first script to only list students from a particular school:

                   Enter Find Mode  []--> clear the pause check box
                   Set Field [Students::School ; GlobalFields::School ] ---> GlobalFields::School is your pop up field and must have global storage specified
                   Set Error Capture [on]
                   Constrain Found Set[]
                   Sort [Restore ; no dialog ]

                   If you use pop up menus, you can use the OnObjectModify script trigger on your pop ups to perform these scripts.

                   If you use drop down lists, use OnOBjectSave if you are permitting users to type data directly into these fields.

              • 4. Re: Narrowing a Portal List

                     Still getting use to all the new features. Once I discovered the ScriptTriggers, that opened up a whole new ballgame.

                     I created 1 Script, which did a find using all 3 global fields (Yr, School, Grade). Since it doesn't matter in a Find if the fields are empty, I didn't need to worry about "constraining". Just do a find whenever one of the Global Popup menus was modified.

                     The only thing I needed to 'catch' is when modifying the School global field, I needed to add a 'wipe' of the Grade global field, since doing a find on all 3 fields would give unexpected results (eg school A has grades K-5, and if Grade A and Grade 2 were selected, then changing the School to School B which is middle school, the result would be no records unless you wipe out the global field for Grade).

                     Anyway, it works now, and it works slick as snot (appropriate for this time of year). laugh