1 2 Previous Next 16 Replies Latest reply on Dec 19, 2015 4:35 PM by juandent

    how to find the number of rows in a portal?

    juandent

      For any portal, how can one find how many rows it has?

       

      Thanks,

      Juan

        • 1. Re: how to find the number of rows in a portal?
          StephenWonfor

          A calculation that does this - count(YourPortalRelationship::SomeNotEmptyField).  Or a scripted variable doing same.

          • 2. Re: how to find the number of rows in a portal?
            juandent

            This does not take into account any filters that are applied to the portal, though...

            • 3. Re: how to find the number of rows in a portal?
              rgordon

              Use a script to go to the last portal row and use the Get(portalrow number) function to get the row number. If your relationship allows creating a new record, I think you might need to subtract 1 from the Get function result.

              • 4. Re: how to find the number of rows in a portal?
                juandent

                ok, sounds almost good ... two issues:

                 

                1) how can I find if the relationship allows creating a new record (so that I know if I need to subtract 1 from the Get function)?

                2) where do I place the value of the portal row count? In FM, as far as I know, we can only display fields from tables (we cannot display variables for instance). So, do I need to define a global field in the table and use that to bind it in the display ?

                 

                 

                Thanks rgordon!!

                • 5. Re: how to find the number of rows in a portal?
                  rgordon

                  1) how can I find if the relationship allows creating a new record (so that I know if I need to subtract 1 from the Get function)?

                  This option is available in the Edit Relationship box. more than likely this option is not select. If you can add new records directly in a portal, this option would be selected.

                  2) where do I place the value of the portal row count? In FM, as far as I know, we can only display fields from tables (we cannot display variables for instance). So, do I need to define a global field in the table and use that to bind it in the display ?

                  You can place it in a field or variable

                  • 6. Re: how to find the number of rows in a portal?
                    PeterWindle

                    note that the "count" function is SLOW where there are a lot of related records to count.

                    rgordon's solution will perform better, remember though... if you have multiple portals, you will need to script navigate to that portal first.

                    • 7. Re: how to find the number of rows in a portal?
                      stephensexton

                      You could determine if you need to delete 1 from the Get(ActivePortalRowNumber) function by first checking if your unique ID field for that row is empty... if it is empty, then it does not represent a record, so you can delete 1 from Get(ActivePortalRowNumber)

                      • 8. Re: how to find the number of rows in a portal?
                        twelvetens

                        Another method I've used is to create a summary field in the "child table" which just keeps a count of the records. Place this summary field in the portal (you can have it hidden if you like, by placing it behind something), and give the field an object name (e.g "portalsummaryfield"). Then, in the "Parent" table, create a calculation that uses the GetLayoutObjectAttribute function to pull the "content" from that field, and that will show you the number of rows in the portal, and will take into account any filtering set on the portal.

                         

                        So the calc would look like: GetLayoutObjectAttribute ( "portalsummaryfield" ; "content")

                         

                        Try that...

                        • 9. Re: how to find the number of rows in a portal?
                          PeterWindle

                          James - that's neat!

                          • 10. Re: how to find the number of rows in a portal?
                            nicolai

                            Another simple technique:

                             

                            Add a summary field to a portal source table and define it as "Count of", select field which always have value and don't tick any other options.

                             

                            If you add this field to the layout (it has to be from the same relationship as the portal) it will give you the total number of portal rows disregarding the filter

                             

                            If you duplicate the portal and reduce the portal rows number to one and add the same summary field to the new portal it will show you the number of rows for the filtered portal. Obviously, the filter has to be the same for both portals.

                             

                            Again, on a large data set it could be slow, but if it is the case you probably should not be using a portal filter.

                             

                            If you enable "Running total of" for the same summary field it will give you the line numbers.

                             

                            Demo attached

                            • 11. Re: how to find the number of rows in a portal?
                              Mike_Mitchell

                              rgordon wrote:

                               

                               

                              In FM, as far as I know, we can only display fields from tables (we cannot display variables for instance).

                               

                              Inserting merge variables onto a layout

                              • 12. Re: how to find the number of rows in a portal?
                                rgordon

                                The above quote was not from me. I use merged variables on layouts but sometimes they do not refresh on the screen as you would expect.

                                • 13. Re: how to find the number of rows in a portal?
                                  beverly

                                  I've read this thread and cannot determine if you need the number for DISPLAY ONLY or if you are using it elsewhere.

                                   

                                  The "Insert" menu "Other Symbol..." will allow you to use the {{FoundCount}} in a portal (single row) to show the count (no calcs/ no summaries!). This also works in a Filtered Portal. Just create a duplicate of any portal where you need this value and make it a single row (no fields). Then use the Menus or just type the value.

                                   

                                  The Other Symbol menu/dialog has a lot of values that work within records (parent) and inside portals (children).

                                  Inserting the date, page number, or other variable onto a layout

                                  beverly

                                  • 14. Re: how to find the number of rows in a portal?
                                    juandent

                                    Hi!

                                     

                                    I have created a summary field in the child table called count (Count of: id_child_table), and named it COUNTER in the layout, and a calculated field in the parent table called countConcepts defined as:

                                     

                                    GetLayoutObjectAttribute ( "COUNTER"; "content" )

                                     

                                    Sounds good but it is not taking account of the filer. See, I  have a button that toggles the filter on or off from the portal:

                                     

                                    -----------

                                    Set Variable [$$filter; Value: If( $$filter; 0; 1)]

                                    Refresh Portal [ Object Name: "CONCEPTS" ]

                                    Refresh Object[ Object Name: "OUTER_COUNTER"]

                                    ----------

                                     

                                    While the portal has the following filter:

                                     

                                    ------------

                                    If ( $$filter ; T11b_period_conperiod_Concepts||id_concept|::is_excluded = 0; 1)

                                    -----------

                                     

                                    The toggling of the filter IS WORKING (I can see one row appearing and disappearing as I  toggle it) but the count (called "OUTER_COUNTER") does not change value (even if I try to refresh it -- see above).

                                     

                                    See if this makes sense to you..

                                    thanks ,

                                    Juan

                                    1 2 Previous Next