5 Replies Latest reply on Jan 5, 2017 5:55 PM by sawhat

    Counting Related Records in a Filtered Portal

    themactech.

      Title

      Counting Related Records in a Filtered Portal

      Post

      I have a table with a related portal of records but that portal is filtered. I would like to get the count of those filtered items.

      So using Count(related table::field) I get the total number of records without the filter. Is there a calculation to only count the filtered records?

        • 1. Re: Counting Related Records in a Filtered Portal
          philmodjunk

          If you only want to display the total on your layout, you can define a "count of" summary field in the portal's table and put a one row portal with the same portal filter on your layout with the summary field located in the single row of this portal.

          But if you then need to use this count in further calculations, you'll need to either set up a relationship that produces the same set of portal records without any portal filter or use ExecuteSQL with Join and Where clauses that produce the same records set as the portal in order to count them with a count function inside the SQL query.

          • 2. Re: Counting Related Records in a Filtered Portal
            themactech.

            anyone ever tell you that you are a mother flippin' genius ;)

            THANK YOU SIR!!

             

            • 3. Re: Counting Related Records in a Filtered Portal
              sawhat

              Phil,

              I tried to implement the portal method you outlined above. I did not notice a difference in function when I just placed the summary field inside the same popup as the filtered portal. In both cases though the count would not refresh until I clicked it's text box. Why doesn't it update when the filtered content changes? Is there a work around other that using the ExecuteSQL method?

               

              Thank You

              • 4. Re: Counting Related Records in a Filtered Portal
                philmodjunk

                If you are editing data and then need to see the summary value update to reflect that editing you should first:

                Commit the Record

                if necessary, refresh the window. If using FileMaker 15, you can use Refresh Portal after giving the one row portal an object name.

                 

                As a test, you can commit records by clicking a blank area of your layout outside of the portal. If that is all you need, you can usually set up a script trigger on the field or fields that you are editing to commit records. But if necessary, the script can also refresh the window or portal.

                1 of 1 people found this helpful
                • 5. Re: Counting Related Records in a Filtered Portal
                  sawhat

                  Thanks Phil.

                  i am using FM15.

                  Adding a record commit did the trick.

                   

                  I have learned a lot from reading your posts helping others.

                  I appreciate your help.

                   

                  Doug