I would consider the transactional approach as a viable solution for this problem.
While you can postpone the commit, you're just postponing the problem. It's pay me now, or pay me later. Jittery scrolling in a portal is an indicator of poor network performance or a design that has too many fields in the related table. When FileMaker scrolls a portal, it will periodically (every 25 records or so) download the related records from the server. I would look to solution design:
- See if you can eliminate some fields in the related table to lighten the network load
- If you can, avoid sorting the relationship
- If you can, avoid portal filters (you didn't say you were using one, but just in case)
- Be sure the solution is using themes and styles; eliminate extra layout objects where you can
Optimization for WAN is pretty important, since bandwidth and (especially) latency are performance killers. If you can give us a little more information on your solution, it might help us narrow down the source of the issue. Any unstored calculations in the related table? Any images or other container data being displayed in the portal? What's the sorting like? How many fields are in the parent table and the related table?
Wow, thanks for the great input!
I'm not using any portal filtering or sorting. Filtering is done through relationships. There are no container objects or design elements, all regular stuff from the theme.
Do fields in the related table that are not shown in the portal still slow down the whole process? In the portal I got nine fields in total, stuff like product name, product category, amount, price, amount in storage, comment. Some of them like the price and the amount in storage have to be unstored calculations, since they depend on the amount entered. Category and name are to be selected by the user as a popup list, which go back to an pk_value and display the name or category of the item. The parent table has around 15 fields.
Scrolling is really jittery for me even with less than 25 records in the portal. I don't think it's a reload issue. It's even slow when run on the server machine directly. It's not a powerhouse, but we're just talking scrolling here. While I love everything else, I'm overall quite disappointed with the performance of FileMaker.
I'm seriously thinking about trying the Modal Window Revert method. I'd rather have my users wait a little bit on the final commit than have huge lags when selecting the items. This would be quite a bit of work, though, so if there is an easier fix, I'd much prefer that. I started with zero knowledge about FileMaker, so I didn't have performance on my mind.
Do fields in the related table that are not shown in the portal still slow down the whole process?
Yes, they do. All fields on the record come down when the record is fetched.
In the portal I got nine fields in total, stuff like product name, product category, amount, price, amount in storage, comment. Some of them like the price and the amount in storage have to be unstored calculations, since they depend on the amount entered.
I would dispute that. Nothing "has to" be an unstored calculation (at least in a table). The unstored calcs are the source of your problem. FileMaker is evaluating them as they come onscreen, which is what's causing the jittery scrolling. (It's not "just scrolling"; it's evaluating those calculations as the fields appear.)
Unstored calcs can (and, in many cases, should) be replaced by a scripted process that inserts the correct value. If the fields can be indexed, then you don't have the constant re-evaluation that slows things down.
Just for testing, I made a copy of my database and converted all unstored calculations to text fields. Unfortunately, scrolling speed was only minimally improved - and the file was run from my hard drive, not the server.
Then, I converted the three popup fields in the portal to edit boxes and scrolling was perfectly smooth, so they are the culprits. The thing is, I don't how to do without them. They let my customers pick the product name or product number while storing the pk value of the product in an fk field so that my warehouse system accounts for the order, i.e. subtracts the amount ordered from the warehouse. For one, my customers need to see the products and should only be able to pick what we have, and for another, I don't know how else to store the pk value in such a subtle and unambiguous way. Dropdown menus seem to work much faster, but they always show the pk value from the background at some point, even when I do that little trick with the edit field as an overlay to display the name instead of the pk as demonstrated here Filemaker Dropdown without ID trick - YouTube
Interesting. I'll have to check into that later; haven't ever heard about the popup issue.
There are a couple of ways to have a "pick list" without the pop-up menus:
1) You can use a popover and a Virtual List technique through a portal. Everything is scripted, so that when the user clicks on a row, the appropriate value is inserted. All you have to do is show him the "plain text" value; the ID is inserted in the appropriate field via Set Field (which means you don't have to have it on the layout).
2) You can use a popover (again, but differently) to show "more detail" on a given row. Take the pop-up menu completely out of the portal, and put it into the popover instead. That way, it only has to refresh when spawned.
How is the value list for the pop-up being generated? There may be issues there, too. A value list generated via relationship can mean slow performance under the right circumstances.
Yes, the pop-up is being created via a relationship. By now I changed everything to a dropdown list and applied the dropdown without ID trick. I can still see the current pk value instead of the name while picking another value in the dropdown, but this is a really minor nuisance in comparison to the boost in performance. However, this performance boost is exactly what I don't understand. In my limited perception, the dropdown should have to pull just as much information as the pop-up, since they're based on the same value list.
What happens if you show only the second field in the value list (the "user friendly" name)?
That's what I'm doing anyways. The only time I get to see the pk value is when I modify my selection, i.e. select one item from the dropdown (displayed correctly) and then click the dropdown again to make a different selection. Then, all selectable values are displayed correctly as their name, but my current selection in the field is displayed as the pk value for the time that I have the dropdown open. As I said, that's a minor nuisance.
A screenshot to make it clearer.
Whenever you use pop-up in this manner, the field on the layout is the key field, but what's being displayed is the related record data. I expect that has something to do with it. FileMaker has to resolve the relationship in order to display the data.
What isn't clear is why the same issue doesn't exist for drop-down lists. (Assuming the related field is on the layout.)
Anyway, I suggest just using a popover and bypassing the issue.
Can't thank you enough for all your helpful input!
I might as well get banned from the forum for this answer but I have to share this alternative.
In order to fight the portal sending all the record data problem, I use a "thin table" on a specific database. Imagine having a "Clients" table with lots of fields. There is a twin table, called ClientsSmall, containing only FK_Client and 3 other fields, among which ClientName, all lookup.
Whenever the data in Clients is changed, a script receives as parameter the clientID, goes to the correct ClientSmall record and sets FK_Client to FK_Client, provoking the lookup. Of course there's a simple way to create the SmallClient record upon creation of a new Client record, too.
The portal on clients shows data from ClientsSmall, not from Clients. It's quite turbospeed and I get what I wanted, the PK.
Why would you get banned? Jon Thatcher (FMI) suggested exactly that model at DevCon about 4 years ago.