Boatloads of data, portal filtering, and how to get 1 record...
I am having some issues and confusion about how a portal operates, in relation to the total amount of data downloaded to the client. Two questions really: how do you get Filemaker to send you one whole entire record (I want to measure the size of a record)? And, the real reason behind that question, how come my portal is downloading tons of data?
FMS12, FMP12, file hosted on server
I made a layout (dashboard kind of thing: buttons to common layouts for the group, a list of upcoming things) with a portal on it to display some records. I used a portal filter, because I have heard a number of sources say that portal filtering is now done on the server and it only transfers the resulting records. It is not sorted (in the portal, nor actively by design anywhere else that I know of).
This layout is based on TableA. TO_B is based on TableA; there is a cartesian relation between TO_B and TO_A (id x id), since I am filtering at the portal and I need all possible records to be in the starting pool. The portal filter is based on a two week period ("Date_CurrWk_Monday" is a custom function that returns the date of the Monday for the current week):
TO_B::_Date ≥ Date_CurrWk_Monday and
TO_B::_Date ≤ Date_CurrWk_Monday + 14 "
Essentially, I am just trying to show them a list of items that are coming due in the next two weeks. But this layout is taking 45+ seconds to load. So I wanted to investigate how much data was being sent...
I have whittled the layout down to just this one visible portal. But when I measure the total amount of data moved (using Wireshark) I get a result of 18MiB downloaded. And I get this 18MiB result regardless of how many records end up in the filtered portal. I have narrowed the filter (altering the date range) to show only 20, 12, 7, or 1 record and the total transfer size is about the same.
I will admit, Table A is very wide; about 700 fields. And there are about 3100+ records there. So I have been trying to measure a single record's worth, and had some mixed success. (Any suggestions on definitive ways of doing this would be appreciated. I have run into the various 'chunking' FM does: 25 records at a time in form view. I have been trying form/list/table view layouts instead of portals and doing a find to get specific record counts.)
Doing some math on record counts, though, yields something in the neighborhood of 3-5Kib per record. There are a bunch of calculation fields, too, and depending on whether or not I scroll those into view the record size can increase to 100+Kib. But on the portal (or anywhere) if the calc fields aren't shown, they shouldn't fire off, so that possible extra data shouldn't be an issue. I think.
I was thinking about doing a relationship (new TO) based on a global field, and populating that global field with a single record ID. (This is actually part of a related discussion about how to filter the portal using this global field, a script, and some ExecuteSQL to get your record IDs. A simple field would work for testing.)
Anyone have any ideas on narrowing things down to a specific record easily? Or why is my portal taking so long, moving so much data?