[Note: several hours after posting this article I realized the “Hide Object” calc could be streamlined. Screen shot and demo have been updated to reflect this.]
Demo file: virtual-portal-v2b.zip (requires FM 18 or later)
This is a quick follow up to the Virtual Portal article I posted the other day. As you may recall, the objective was to use virtual list to display disparate entities in a portal…
…via an array like this…
…which was auto-generated via a Hide Object calculation attached to this field:
Alex Zueiv demonstrated in the comments section that the SQL used to generate the array could be made more robust (thank you Alex). Another way to make the array more robust would, of course, be to generate it via JSONSetElement rather than ExecuteSQL… and today’s demo is almost identical to the one I posted the other day except:
a) the Hide Object calculation attached to zz_g_downstream_ownership has been revised to use While and JSONSetElement…
…and b) an ascending “name” sort has been added to the relationships from Company to the four child table occurrences.
For more information on While, see…
Nice one Kevin. Any thoughts on the relative performance of While vs ExecuteSQL – especially with larger related found sets?
Hi Paul, I’m guessing that ExecuteSQL pointed at indexed data will be the winner. But that’s just a guess. Also the array generation method used in today’s article could be optimized using Russell Watson’s Insert Calculated Result trick (as mentioned in Virtual List Simplified and elsewhere on this blog), but that seemed like overkill for this simple use case.
I asked because in my experience, the first time ExecuteSQL is called on a table can be rather slow especially with a lot of records.
I wonder if the culprit is actually ExecuteSQL? For example, I have a system connected to an external file on the same server called Transactions that contains 740K records. When I open my file, and put this in the data viewer, the response is instantaneous.
Note: in this scenario a) all referenced fields are indexed, and b) I’m not using GetFieldName either explicitly or within a custom function to make my field or table references robust… but instead am intentionally writing brittle, raw (i.e., unprotected) SQL.
(Speaking of which, a reason to avoid using GetFieldName under certain circumstances will be the topic of my next FileMakerHacks article.)
Depending on the size of the related record set, the Count() function can be slow, as every record must be evaluated to determine if it meets the criteria – regardless of how simple the criteria might be. For this reason, I have added a field to my standard set of fields-that-belong-in-every-table, which is an unstorred calculation of: Get ( FoundCount ).
It is an instant way to know the number of related records. Credit to a Weetbicks blog post from several years ago.