3 Replies Latest reply on May 12, 2013 10:08 PM by philmodjunk

    Comparing Summary Fields from Filtered Portals After Using One Line Portal Trick

    Tusquittee

      Title

      Comparing Summary Fields from Filtered Portals After Using One Line Portal Trick

      Post

            

      Hi...

            

      I’ve been trying to compare summary data from a filtered portal and I’m not sure how to go about this.

            

      On my class detail layout from tbl Classes I have a portal that displays information from my Reg Data table.  The portal displays student information including a status which indicates if the student is enrolled, withdrawn, or cancelled.  

            

      I used the trick where you create a one line portal with a summary field to determine a lineitem count.  So far so good, (or so I thought!)  I have four separate one line filtered portals with summary fields which indicate how many students total, how many have cancelled, how many have withdrawn, and how many remain enrolled. This works well.  I can change the status of each student and the summary fields populate as expected.

            

      The next thing I would like to do is to have a field, Classes::Available Seats, which is a calculated field that takes a maximum number and subtracts the actual enrolled number to determine how many seats are left in the class.

            

      So... something like Classes::Maximum- Reg Data::Enrolled Count

            

      When I realized this wasn’t working I created separate table occurrences named Reg Data for Enrolled, Reg Data for Cancelled, Reg Data for Withdrawn... and changed my one line portals to evaluate the summary fields from that perspective.  This too works in terms of my summary fields..... Yet, I still cannot determine how to make the subtraction piece of the puzzle work.

            

      I changed the calculation for Available Seats to

      Classes::Maximum-Reg Data for Enrolled::Enrolled Count.......but still no luck....should I be scripting this instead?

            

      Any suggestions or thoughts on what I’m doing wrong? Thanks in advance,

      Untitled_2.jpg

        • 1. Re: Comparing Summary Fields from Filtered Portals After Using One Line Portal Trick
          philmodjunk

               Summary field values shown in filtered portals are "display only". I know of no way to access the value for use in a calculation or for evaluation in a script. You'll need to figure out a relationship that matches to the correct set of records without using a portal filter to do so. This sometimes requires defining a calcualtion field that returns a constant value for use as a match field in such a relationship.

          • 2. Re: Comparing Summary Fields from Filtered Portals After Using One Line Portal Trick
            Tusquittee

                 So, now I've ventured into the world of Executesql in hopes I might be able to set up the correct calculations.  I saw a previous post that reccomended SQLExplorer and have tried my hand but still no luck... 

                 the above picture is from the Class Detail Layout.. Has a filtered portal on it from the Reg Data table.  I need a count of the Status from Reg Data where Status is = "Cancelled"  AND.. the Reg Data::CLASS ID MATCH FIELD should also be = to Classes::CLASS ID MATCH FIELD

                 I worked out the following using SqlExplorer and pasted it into a field on the pictured layout.  The calculation works EXCEPT I need just a count for that particular class.. Instead it gives me a count of everytime that Status is set to "Cancelled"... Which means that the AND statement I set up isn't working? It seems odd to me to have a join statement that says basically the same thing as the AND statement but I don't think I can get rid of the join? 

                 Might you have any suggestions or could someone point me to other helpful posts? Thanks, 

                  

                  
                 "SELECT COUNT ( a.\"Status\")
                 FROM \"Reg Data\" a
                 INNER JOIN \"Classes\" b ON a.\"CLASS ID MATCH FIELD\" = b.\"CLASS ID MATCH FIELD\"
                 WHERE a.\"Status\" = ? OR a.\"CIDMF\" = ?" ; 
                 "    " ; "|*|" ; 
                  
                 "Cancelled" ; "CIDMF (b)"
                  
            • 3. Re: Comparing Summary Fields from Filtered Portals After Using One Line Portal Trick
              philmodjunk

                   What do you get if you count on a field in b instead of a?

                   And what you describe here could be set up with standard FileMaker relationships to get your portals, but without a filter expression. Thus, that's an additional avenue you can persue.