Counting filtered portal rows

[Mark Graham]

How can I count the number of rows in a filtered portal? Filtered being the operative word. I can get a count if I don't filter.

I've able to solve all my previous problems using the answers on the forum, but I just can't find the answer to this one.

[mr_vodka]

Create a summary field in your child table that counts the pkID.

Then go back to your layout with the filtered portal on it. Copy the portal and change it to a 1 line portal. Resize to your needs and then put the newly crated counting summary field in the 1 line portal.

[Mark Graham]

I am stunned at how easy that was. Thank you.

[bcooney]

Are you doing it this way bcs the portal is filtered using FM11's new portal filter feature, rather than the portal using a filtered relationship? Otherwise, simply Count ( portalTO::primaryID).

[dburnham]

Create a summary field in your child table that counts the pkID.

Then go back to your layout with the filtered portal on it. Copy the portal and change it to a 1 line portal. Resize to your needs and then put the newly crated counting summary field in the 1 line portal.

 

I can't seem to make this work.  The summary field gives me all 856 records in the child table, but the portal has only 717 rows because it is filtered.  I am trying to determine the number of rows in a filtered portal and the summary field doesn't do it.

[LaRetta]

You must place the summary field in a 1-row portal which has the SAME filter as the portal you are counting.

[bruceR]

Now if we could just figure out a list function that would work for filtered portals. To get, for instance, the list of a field such as recordID or contactID.

(For filtered portals; not filtered relationships)

[LaRetta]

Indeed! I want a new function called GetIndex ( fieldName ). I know with ExecuteSQL() we can use Distinct, Bruce, but I still think it would be useful. :-)

BTW dburnham, filtered portals weren't out until version 11. You might want to update your profile or is it possible that you are wanting a relational filter instead? Regardless, the summary field will work if it is based upon the same relationship.

corrected unique to distinct

Edited November 15, 2012 by LaRetta

[DaWood]

The count function only work for static portal, and will account for all related records regardless of the portal being filtered. Another solution is to "count" the number of portal rows in the filter portal and use that as the number of "Found Count" after the portal has been filtered. The last portal row would be your "found count." This can be accomplished using Get (ActivePortalRowNumber) function

 

Create a text field "FoundResult" in the parent table and make it global.

 

Then create this script:

 

Set Variable [$FoundResult; Value:Get (ActivePortalRowNumber)]

Go to Portal Row [select; Last]

Set Field [ParentTable::FoundResult; $FoundResult]

 

Run this script either through object modify or keystroke trigger. 

 

[LaRetta]

We are talking about difference of perspective.  From parent, you need a calculation which uses Count() or other aggregate.  But with summary field, you place it in the child table and where ever you display it up the relational line, the value will be correct from that perspective of the parent.   So child summary placed in filtered one-row portal does the job of Count() .  This saves having to add a Count() calculation in several different tables when you want to count different tables of children. 

 

A single summary field of Count LineItemID in LineItems (for example) can be used in Products, in Inventory, in Invoices, and in Clients saving four additional calculations!  Summary fields are wayyyy under used.

[DASC]

Just make a global dummy field with value=1 and make a field portalcounter=sum(dummy), that works for me with filtered portals 

[Robby]

In March Soliant Consulting published Sara Severson's excellent script for dynamically filtering portal results as users type into a global field:

 

http://www.soliantconsulting.com/blog/2013/03/dynamically-filtering-filtered-portals

 

I too had the impulse to display a count of the results, and added two lines to her script to set $$Filtered_Portal_Count as the filter is updated:

 

Between Sara's two lines:

 

Set Field by Name [$field ; Evaluate ($field)] // Refresh portal without flicker

Go to Object [Object Name: $filter] // Return to global filter field

 

I inserted:

 

Go to Portal Row [Last] // washed away by Sara's next line

Set Variable [$$Filtered_Portal_Count; Value:Get ( ActivePortalRowNumber )]

[clifford]

Just make a global dummy field with value=1 and make a field portalcounter=sum(dummy), that works for me with filtered portals 

I created a global field and put the global field in the filtered portal, however the sum still shows the total number of portal records. I'm using FMP Advanced 13. Where is the problem?

[eos]

Where is the problem?

 

The problem is that this is not a good (or, for that matter, working) method. Use the technique described in posts #2 and #10 (summary field from child table in one-row portal with same filter).

[axelvdb]

I needed the number of rows out of the context of the portal so I resolved this problem with an ExecuteSQL that gave the same result as the portal rows (including the filter) and I applied a Count in the SQL statement.

 

In my application it looked like this:  (after the 'AND' is the filter statement)

 

ExecuteSQL ( "

SELECT count (*)

FROM meterstand_pv AS mPV

WHERE mPV.ep_id = ? AND mPV.vreg_id =  mPV.vreg_id_selectie

" ; ";" ; "¶"; id_ep)

[comment]

I needed the number of rows out of the context of the portal

 

IMHO, if you need to use the results of a filtered portal at the data layer, you should move to a filtered relationship instead. Otherwise you'll be violating the DRY principle.

 

To be fair, replicating the filtering expression to another portal in order to display a summary field is also a kind of violation of DRY.  But at least it's the exact same expression, in the same language.

[Rennie Innis]

Hello All, 

Tried all the above techniques, and no joy. 

Trying to count the remaining rows in a filtered portal (not filtered relationship) when the filtered portal is on a PopOver. Maybe this acts differently?

I CAN see the correct countValue in the  summary field from child table in one-row portal with same filter as specified in previous posts.

Any hints?

Thanks

Rennie

[DanShockley]

Rather than having a Count calculation field, I'd definitely recommend having a field named something like zFoundCount (in every table). Define it as an explicitly NON-stored calculation equal to: Get ( FoundCount )

That field, as La Retta mentioned above, will know in what context it is being called. So, if you put it in a portal, filtered or not, it will show the number of records in that portal. 

It also has the advantage of not requiring the reading of the record content in the same way that a calculation field that Counts a data field in the table does. 

Read this article (from Digital Fusion in 2010) - this technique can produce drastically faster results: 

https://www.teamdf.com/blogs/a-lightning-fast-alternative-to-the-count-function/

We use this technique in many places throughout our system, and that zFoundCount field is a standard field we put in every table. It made a huge difference in performance and ease-of-development. 

Edited November 3, 2017 by DanShockley
added note about how we use this