Brian Dunning's FileMaker Custom Functions

FilteredPortalList ( fieldObjectName ; startingPortalRow )

Returns a field from each row of a FILTERED portal, up to the first blank row

  Average rating: 4.5 (30 votes) Log in to vote

Howard Schlossberg   Howard Schlossberg
FM Professional Solutions
http://fmprosolutions.com

Share on Facebook Share on Twitter

  Sample input:
FilteredPortalList ( personID ; 1 )

(where there are 100 related records but the portal is filtered to only show 3 records)
  Sample output:
667
864
1037

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

This shouldn't have alluded me for as long as it has, but I was happy to find this on FM Forums, written by Ron Cates in a thread of self-help.

Other functions that I've seen here display all VISIBLE rows in a portal, but this one displays ALL ROWS in a portal that has filters applied...although only up until the first blank row.

The reason it shows only up to the first blank row is that there is currently no way to determine how many total filtered portal rows there actually are; stopping at the first blank is the best assumption we can make. This works fine for the intended purpose, however, which assumes the field being gathered is the primary ID of the related record (from which you can use ExecuteSQL() or perhaps other functions to determine the content of other related fields).

 

Comments

Bruce Robertson   Bruce Robertson
May 26, 2013
Will this handle skips? Say - as in your example - the relation points to 100 records; and 5 pass the filter; but this field displays content in the first and last of the 5.
 
Howard Schlossberg   Howard Schlossberg, FM Professional Solutions
May 26, 2013
Good point, Bruce. No it does not. I just made an edit so that it doesn't stop at the first empty row (which it did in the initial version). But because I am using the List() function to compile the list, it skips blank iterations.

There is probably an easy(?) way around this, but not one I can think of very quickly without always having a blank row at the bottom.

This function works for me as is because I am only using it to gather primary IDs from the related records, which will never be blank.
 
Howard Schlossberg   Howard Schlossberg, FM Professional Solutions
May 26, 2013
Okay, I just added a third parameter to either show or not show blank rows.
 
Bruce Robertson   Bruce Robertson
May 27, 2013
Unfortunately that doesn't work. By using the "left" attribute, you only retrieve info for the visible rows of the portal.

Also, I was not so much concerned about getting a compacted list as with knowing when the function should stop walking the portal rows.

We really need getLayoutObjectAttribute to be fixed so that it can know when it has run out of portal rows.

So you probably need to go back to your original function but advise users that your function stops on the first empty value, either because the object is actually empty or because it is referring to a row that doesn't exist.
 
Howard Schlossberg   Howard Schlossberg, FM Professional Solutions
May 27, 2013
Ugh, right. I should have tested more than just a dozen rows in a large sample portal. I've now reverted it back to its original, which is to display up until the first blank row. Thanks, Bruce!
 
Stephen   Stephen, Wyckoff, NJ
Sep 12, 2013
Does anyone know if this can also get field data that is not being displayed in the portal? Does the field have to be in the portal to get pulled in?
 
Howard   Howard, Los Angeles
Sep 12, 2013
No, it only shows fields that are in the portal and that are given an object name. But that field can be sized at 1x1 pixel and prohibit entry, etc.
 
Nick Lightbody   Nick Lightbody, Deskspace Systems Limited
Oct 11, 2014
Here is a means of getting data from all the rows of a filtered portal - thanks to Andrew Duncan and Daniel Woods

http://www.databuzz.com.au/getting-the-ids-for-a-filtered-portal

Close to your method Howard but using the context of the filtered portal to inform the GetLayoutObjectAttribute with the correct values - which also provides an easy method of getting the filtered portal rec found count using wordcount(recIDs).

Does this compute with you? It seems to work for me.

Cheers Nick
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.