Filter data in portal. Paging results.

[Mark-IT]

Hello Community

I'm wondering is there a way to combine two technics: Filtering portal and Pagination the results.

There is a nice sample here (thanks for sharing @comment), but I'm stuck trying to combine it with filter. I tried to make a list of ID (as summary field) to use this when result is true, but with no luck.  

My goal is to use multiple global fields as checkboxes or search string, and then display splitted result. This part is clear, but I cant connect it so the sample from mentioned post.
Thanks in advance.

[comment]

A simple method would be to filter the relationship instead of the portal.

If that's not possible, you could place the summary field inside a one-row filtered portal and use the GetLayoutObjectAttribute() function to get the list from there.

Another option is to use the ExecuteSQL() function - but I would be careful to use it only when all records are committed.

 

[Mark-IT]

Unfortunately:

• ExecuteSQL() is too slow, more than 100K records in related table. 
• Via portal - I need to check how the data will behave, as is using empty checkboxes.

If that's not possible, you could place the summary field inside a one-row filtered portal and use the GetLayoutObjectAttribute() function to get the list from there.

I did this, but I stuck to integrate it with your solution.
Not sure how to combine those two "filters" together.

 

Let ( [
portalSize = 5 ;
setIDs = If ( IsEmpty ( Parent::gfilter ) ; List ( Child::ChildID ); GetLayoutObjectAttribute ( "sListChild"; "content" )) ; //taking a lot of time to evaluate
pageIDs = MiddleValues ( setIDs ; portalSize * ( Parent::gPortalPageNum - 1 ) + 1 ; portalSize ) ] ;

not IsEmpty ( FilterValues (  Child::ChildID ; pageIDs ) )

 )

it just dont gave me correct results.

 

[comment]

2 hours ago, Mark-IT said:

more than 100K records in related table.

Let's clarify this point before moving any further. How many related records do you have before applying your filters? Filtering a portal is done "on the fly" without using the index. If you have more than a few hundred related records, then a filtered portal will  be slow - and this also applies to my method of paging a portal, even without adding your extra filtering.

 

[Mark-IT]

Before applying the filter, only via relationship is around 5500 records. Im trying to make WD workable, but having summary field as a list of IDs make everything very slow, Pro better. I noticed if I hide the field in browse mode, the screen does not freezes, but still pagination of portal take a lot of time. I really think the only way will be to use Data Tables or something to paginate, as I can't make if monthly working with FM. 

[comment]

3 hours ago, Mark-IT said:

only via relationship is around 5500 records

Then portal filtering is not a good choice for you, at least not for the initial reduction of the related set.

Either define a relationship that is filtered by your global fields, or use ExecuteSQL() to get the IDs of the reduced related set. I don't think ~100k records should make ExecuteSQL() slow, but I don't see your data or how you plan to use the global fields.

Once you have the list of IDs, you can use a calculation field to extract a page-length chunk out of it and use it as the match field for the final relationship. Or, if the filtered relationship yields a reasonable amount of records, use it as the basis for the portal and apply portal filtering only for the pagination.

 

[Mark-IT]

Ok, thanks for suggestions, I will try that.