4 Replies Latest reply on Dec 26, 2013 7:47 AM by john.s

    Help with portal calculation

    john.s

      Title

      Help with portal calculation

      Post

           I have a layout with three portals each on a tab and want to show the total number of records in each portal as the tab is selected.  I've tried to use the sum and count functions and get very strange results. I am using the same logic for the count as I do for filtering the portal but yikes nothing works.

           Here is the script I'm using

           If (
                IsEmpty ( Seniors::SessionDate )
            and
               IsEmpty ( Seniors::RetakeDate )
            and 
               Year ( Get ( CurrentDate ) ) +1 = Seniors::GraduationYear;
            
           Count (Seniors::SessionDate)
            
           )
            
           The Layout is based on the Schools Table with records from the associated Seniors Table.  The portal works fine it is the count that I can't figure out.  This screen shot shows one of the Schools which correctly returns twelve records in the portal but the count is 8 from my calculation.  I have the same problem on each tab only with different weird results
            
           I'm new to FM and at a loss.
            

      Screen_Shot_2013-12-26_at_7.56.54_AM.png

        • 1. Re: Help with portal calculation
          john.s

               Ok I thought I found my mistake (I was counting an empty field) so I changed the count to count the GraduationYear field which always has a value.  I'm still getting weird results that don't match the portal. 

          • 2. Re: Help with portal calculation
            philmodjunk

                 Count counts all related records whether or not they pass the portal filter criteria. Count will show the number of related records as if there were no filter on your portal.

                 To get a count of records for display purposes only, make a copy of your filtered portal, reduce it to a single row and place a "count of" summary field defined in the portal's table inside the portal row.

            • 3. Re: Help with portal calculation
              john.s

                   Thanks Phil…I thought I was understanding this FM thing but I have a long way to go.  I've tried putting the "count of " summary field in the main table by counting the fk for seniors and I get a count of 10 on all records.  I tried putting it in the Seniors Table and count the pk for seniors and I get counts in the 500 - 600 range.  Right now the relationship table just connects the Senior primary key in the Seniors Table with a Senior foreign key in the Schools table.  The portal works fine but when I duplicate the portal and reduce it to a single row and try to use the count of summary field that I created things blow up.  Do I need to create another table occurrence for this to work?

              • 4. Re: Help with portal calculation
                john.s

                     Phil I found my mistake…when I was messing around with the summary field I let it go outside the portal and as you know that is a no no.  It now work.

                      

                     Thanks again for your help

                      

                     John