11 Replies Latest reply on Jun 17, 2012 1:29 PM by brsamuel

    Count number of records in a portal

    m.bodily.consulting

      I am trying to get a count of how many records are in a portal. I've tried the Count function and I've tried setting the field as a Summary (count of) type but in both cases it is not counting the portal records correctly. I have a layout with 2 tabs (1. Current Loans, 2. Past Loans), there is a portal on each tab with it's own count of records. How do I set this up?

        • 1. Re: Count number of records in a portal

          Hi,

           

          Summary field is perfect here.  In the table that the portal is based upon, create a summary field which is:  Count of 'any field which is never empty' preferably the unique auto-enter ID.

           

          Then if your portal is filtered via the relationship, just place this summary field directly onto your parent layout.  If the portal is 'portal' filtered then you will need to create a one-row portal with same filter applied and place the summary field inside it.  Make the portal 2 px wider and taller than the field and center them then set the portal to transparent (or for less flash particularly on Windows, set the portal to background color).

           

          If I have failed to assist, please speak up and I (or others here) will be happy to help further. 

          • 2. Re: Count number of records in a portal
            m.bodily.consulting

            This works just great. What I failed to do was put my summary field in the portal. I didn't think to make a duplicate portal with just the summary field in it. Thanks for your help LaRetta.

            • 3. Re: Count number of records in a portal
              datastride

              m.bodily (sorry, don't know your name),

               

              Here's the approach I use:

               

              In any table that might be accessed via a portal (e.g. a child table), I create a field named "z_Record_Number", defined as an unstored calculation field of type "number" using this formula "Get( RecordNumber )".

               

              Because the field is unstored, it will be recalculated in whatever context (e.g. a portal) the record is being displayed.

               

              So then if you are showing a layout based on some parent table, with a portal based on the child table, use this fformula to give you the number of records in the portal "Last( Child_Table::z_Record_Number )".

               

              Well, actually you don't even have to be on a layout that includes records from the child table to use this calculation, nor do you need a portal. You just need a relationship from the parent table to the child table, and then you can add an unstored calculation field to the parent table that uses this same formula, and you can display that without using a portal.

               

              Oh, and if you like, you can always display the "z_Record_Number" field in each row of the portal to give you nice and neat sequential "line numbers". Very versatile ...

               

              One good aspect of this approach is the very, very low overhead. Depending on the number of related child records, this approach can be dramatically faster than using "Count( Child_Table::_ID)", as the "Last()" function is practically instantaneous, while the "Count()" function has to examine each related record.

              • 4. Re: Count number of records in a portal
                PeterWindle

                Is it not just as easy to create a calc field in the parent table that is count(childtable::field) where childtable is the relationship that the portal is using?

                 

                Similarily, if the childtable has a field that is a calculation set to 1, (call it Constant1 for example) the parent table could also be set to sum(childtable::contant1)

                 

                I've done this before without problem, however, I doubt it would take into consideration filtered portals...

                 

                word of warning, any summary or calculation that counts related records is pretty slow when dealing with a large qty of related records.

                • 5. Re: Count number of records in a portal
                  TimAnderson

                  Peter,

                   

                  Yes, just as easy but slower and no more flexible than Morgans approach. I changed to this method a couple of years ago and have noticed the difference on large data sets.

                   

                  Tim

                  • 6. Re: Count number of records in a portal
                    PeterWindle

                    Interesting, thanks for the 'heads up', I'd be interested to see how much different it would be to set a field in the parent table by a script that would get the summary result, rather that display the result on screen. I doubt that would make any difference to performance, but it would then give a user the option to show the number only when the script is activated. (ie: only when needed to view and not everytime the record is viewed.

                    • 7. Re: Count number of records in a portal
                      PeterWindle

                      ACTUALLY, I just remembered... I recal once doing a google and finding a really neat tecnique for this, I can't recall details, but it went a little something like this:

                       

                      in the child table, you have a get(recordnumber)

                      in the parent, you can perform a calc to get the last of this field via the relationship...

                      from what I understand, the value is returned very quickly and this can be used to show the number of related records.

                       

                      I have not tried it, but apparently this works quicker than anything else.

                      • 8. Re: Count number of records in a portal
                        TimAnderson

                        Yup, that's the method Morgan described!

                        • 9. Re: Count number of records in a portal
                          ntsogt

                          Hi,

                          I have tried your approach to count my portal records, and it seem to me works.

                          However, it always displays 1 instead of real value, but when I click on that "1" it shows real number.

                          Could you help me how to fix this?

                           

                          Narantsogt

                          • 10. Re: Count number of records in a portal

                            Hi Narantsogt,

                             

                            If it shows a 1 when not in the field but shows the real number when you enter the field then I suspect it is layout-level formatting attached to the field.  Select the field and from Inspector, go to Data tab.  At the bottom is Data Formatting and the default is usually general on numbers.  See if it is set to anything else such as Boolean. 

                            • 11. Re: Count number of records in a portal
                              brsamuel

                              Perhaps this is the article you remember.  It is by Daniel Woods from his FileMaker Wheetbicks blog:

                               

                              A Lightning Fast Alternative to the Count() Function