I'm currently designing an iPhone client for a database with many thousand records. I'm looking for a possibility to only load 100 portal records at first, then allow the user to load more if desired. Apple is doing the same in their iOS Mail application for IMAP connections. At the bottom of the list there's a button which allows the user to "load more messages from server" (or similar). I think this could be very useful for mobile FM development in general.

After many hours I sort of gave up... the portal in question is based on a join-table. See image attached. Any ideas and input most appreciated!!

 

In essence you want to restrict the portal to a range of records. Maybe you can add the upper and lower record id's into the relationship and use:

< upperid

> lowerid

 

Incidentally, I always thought that FM restricted the records loaded in portals in the same way it does for a list view and also now on large value lists.

HTH

In essence you want to restrict the portal to a range of records. Maybe you can add the upper and lower record id's into the relationship

I tried this: if the portal is supposed to display all records from the parent table, you can more or less predict the number (not accounting for deleted records). You might wind up with 1,4,5,6,7,9,11,14, etc. So you could approximate, I guess. If the relationship contains other criteria however, you will wind up with something like 4,25,45,77,78,79,111, etc. Suddenly your >1<300 formula only shows 40 records...

Incidentally, I always thought that FM restricted the records loaded in portals in the same way it does for a list view and also now on large value lists.

Interesting! Based on the delays we're experiencing in test-setups, I wonder about this, but I actually don't know. Even if true though, as soon as you add a portal filter field (which references all records of the relationship) this concept goes out the window.

In response to MikeKD on the old thread: 

I'm not sure of the best way to do this, but I'd create two portals with different numbers of records on them and hide/show them using a global "show more threads" field.

Not sure if there's a new fangled FM14 way to do it, or even a less clunky FM13 way though!

​Imagine a table with 50k records. Let's say your first portal shows 100 of these 50k records - you would need an almost infinite number of portals to show 100, 200, 300, 400,...

Can you populate a global multi key and use that as the parent of the portal relationship? I have found that QuickFind is the fastest approach for this, rather than ExecuteSQL, but I'd try both.

Can you populate a global multi key and use that as the parent of the portal relationship? I have found that QuickFind is the fastest approach for this, rather than ExecuteSQL, but I'd try both.

​Whew... you just kinda lost me :-o

Do you mean: perform a find (with QuickFind) in the parent table, aggregate the record-IDs with a loop, then insert them into a global field used for the portal relationship?

What would you search for? Record ID is of no use, and Record Number is "dynamic" based on the found set and sort order (you wouldn't want to sort 50k records prior to searching, that defeats the purpose).

Edited May 19, 201510 yr by artvault

What would you search for is the key question. Not sure what the records in the portal represent.

Yes, I would aggregate their IDs (but use the new List Of summary field), not a loop. The Quickfind would not be in the parent table, but in the portal's table. I'd go to a layout devoted to this function and run a QuickFind (your search criteria is in a global field).

The Quickfind would not be in the parent table, but in the portal's table. I'd go to a layout devoted to this function and run a QuickFind (your search criteria is in a global field).

​Ah, place the related field on a dedicated layout, add it to QuickFind, search "through" a relationship that has access to all records in the parent table. This way only the found set needs to be loaded for aggregation, not all 50k. Genius! 

Though the question remains: what to search for...

Let's keep it general; what the records represent doesn't matter. It's always about showing the "newest" 100, 200, 300. This could mean record IDs, creation dates, or modification dates.

"Related field" uh, no. Use a global field, I call mine SEARCH. Set that field to your search criteria and use the Perform QuickFind script step on a layout of the TO for the child records. Capture the found IDs (in another global field) which is the parent to the portal relationship.

Here's a demo Todd Geist has posted to show his SelectorConnector model. Within it, on the Order form, you'll see how he uses ExecuteSQL and virtual list to drive a portal. https://www.geistinteractive.com/2014/11/21/filemaker-selector-connector-video/

I tried this, but found it too slow for my solution. I changed it up a bit and used QuickFind to populate a global field that drives the portal relationship.

You might wish to also look into using OFFSET and FETCH:

http://help.filemaker.com/app/answers/detail/a_id/12061/~/specifying-offset-and-fetch-first-clauses-in-filemaker-pro-using-the-executesql

Thank you so much bcooney. I will examine your suggestions, build a sample file, and post it here.