The use of ExecuteSQL usually means a commit record and loss of focus on the record within the portal, so in this case we are recording the ID of the related record and looping through the portal records until we find a match.
This might be partly the same kind of routines you do for PerformScriptOnServer - PSoS:
Evaluate “Coordinates” (PrimaryKey / Table / Layout) to use as ScriptParameter in a modular script.
(Apologies if I get too much off topic)
(-:
that thing became slower and slower while working - if You entered some fieldnames ‘blind’ (eyes focused on the keyboard for example), it became that slow that You could easily write faster than the thing interpreted… resulting in messed up fieldnames
As soon as it became slower and slower, we needed to quit filemaker and restart - otherwise it would crash
We lost one pilot project because the customer destroyed his own stuff (it was a coaching project, the customer left the platform)
Coming from the aeronautics industry I can say that each time those geniuses replaced best (engineering) practise by marketing bombast, it failed miserably. No exception to that.
2 Likes
Yep, loss of focus I portal because of necessary scripted activity is a big nuisance and a UI showstopper.
Hi Torsten
It is interesting though, that the commit record is worth it. I find myself using ExecuteSQL more and more, but never with any joins and keep it as simple as possible, but the benefits are huge for me.
We have scripts named something like ‘CommitRecordSkipValidation’ that are designed to bypass ‘OnRecordCommit’ script triggers for this purpose (using global variables - please FileMaker, alongside our main new feature request of 'OnRecordExit script trigger, can we have a ‘Prevent Script Triggers From Running’ option box in ‘Commit Record’?)
Can someone fill me in on how this has changed? Is GTRR slower than it used to be? Is it faster to list all related ids in a variable, navigate to the related table, and add a find request for each id?
1 Like
We first saw the impact upon upgrading to FileMaker Server 16, despite most clients remaining on FMP15 due to the changes needed as a result of the MDI/SDI migration.
I believe others have reported it on earlier versions.
Without going unto detail about reports of customers’ systems slowing down, some to unacceptable levels, we and our customers were constantly seeing a message displayed of ‘Find in progress… Processing query’ - Google ‘FileMaker find in progress’ and you should find plenty of hits.
This has hit both our LAN and cloud hosted solutions. It doesn’t appear to be affected if the related link is simple, complex, a single related TO or a related TO with a chain of TOs connected to it.
We are in the process of replacing GTRR with find requests, but haven’t finalised how we are going to replicate landing on the source record and retaining a found set based on the source portal. Probably using List() based on the related TO, identify the key field and location of the destination record in the list, go to layout, find on the foreign key field and loop through until the record with the ID is identified.
However, that is a crazy amount of scripting, which we’d use a generic subscript for using multiple parameters, to replace a button that could achieve the same using a single script step.
This came to a head a couple of weeks ago when a client reported performance issues and we agreed they’d email a log over every day detailing what the users were doing at the time the problem occurred.
Day 1 upon receiving the first report I changed a GTRR with a go to layout and find on a single related TO link and haven’t had another log emailed to me since.
There appears to be many legacy features in FileMaker that don’t appear to function at acceptable speeds as it has been upgraded. Identifying these and avoiding them, despite the additional work, will pay dividends. The sad thing is that we have used many of these to solve complex problems in the past but now performance design compromises problem solving.
4 Likes
Interesting I’ll look that up and do some testing myself. This caught my attention because I was recently impressed by how slow a gtrr was in a script I was doing. I just chalked it up to how many related records there were and that I was matching the found set. Will do a comparison and get back. Thx.
3 Likes
Yup. I was bit by this recently also. I am thinking of trying to do a modular search, not quiet as elegant as fmSearchResults, but allows you just to pass in data and have it perform the search.
We have a search process that I used pre-16, which was faster than searching in related records. But in 16-18, a find that results in just a few hundred records takes upwards of 1-2 minutes the first time. Faster there-after.
1 Like
Hey, I actually created a find module a while back that sounds a lot like what you described. You pass in the find criteria as JSON to the Perform Find or Perform Multiple Finds script.
Check it out and send a pull request if you see anything that can be improved.
It could probably do with some more unit tests that actually check the find results for accuracy. Like maybe have a couple tests that do the "normal" find, save the result, then do the module script find and compare the two.
2 Likes
Does sound similar. I’ll take a look.
1 Like
I added some functionality tests and already caught a bug. Should be fixed now. At the risk of curtailing adoption, i’m not sure i’d use this for everyday scripted finds. But it would be really useful as a component in other modules to do dynamic finds, e.g. if you have a search bar on multiple layouts that need to search different fields.
It’s also useful as a way to do callbacks because you can pass the find criteria along and call them later as needed. E.g. if you have a dynamic picker framework and want to pre-filter the values that are shown.
1 Like
Am I understanding correctly that people are no longer using GTTR to reproduce found sets (for example to send find results from the user to the server for PSOS)?
I've never had an issue with GTTR in that regard, but then again I've never tried to loop "new request" in order to find a list of ID's. Especially when it's a list of thousands of ID's, I'd assumed that would be much slower.
Interesting approach to trapping for GTRR errors...
https://community.filemaker.com/en/s/question/0D70H000005eLBWSA2/go-to-related-record-when-rr-doesnt-exist
[begin excerpt]
I would recommend checking for the error that occurs when no related record exists (error 101). That way you don't need to check the relationship twice if there's actual related records to display. For example:
Set Error Capture [On]
Go to Related Record [From table: "RelatedRecordsTable"; Using layout: "RelatedRecordLayout" (RelatedRecordTable)]
If [Get(lastError) = 101]
Do whatever you want to do if there are no related records
End If
[end excerpt]
1 Like
Hi Mac
As the OP, I’ll try to clarify.
Since moving to later versions of FileMaker Server 15, we’ve seen many GTRR scripts take an unacceptable amount of time to complete, displaying a ‘Finding...’ message. The same scripts worked fine in v15.
We don’t take an absolute approach for anything. Therefore, where we’ve come across these problems, we’ve had to replace GTRR with finds based on variables.
However, this is not always the case and where GTRR continues to work without any problems. then we leave them in place.
As yet we can’t trace why this behaviour crept in. Some simple TO links a became unusable, whereas some complex links continued to work fine.
Regards
Andy
1 Like
This is a pretty good point. Probably trivial in practice, but I think I'll start doing that instead, though I better play a bit with it first. There are few nuances to GTRR.
Interestingly, there are times when GTRR with nothing on the other side, does not change the found set (228)...and other times when it does (234). Trapping before GTRR has its use cases.
We too have reduced GTRRs...mostly as as a side effect or reducing TOs on the Relationship Graph. If we do not need to show a portal or parent records on a layout...then...one less TO and we use Find routines instead.
So far we have not been bitten by any GTRR issues. We will keep our eyes out.
Thank you for posting what you are seeing.
3 Likes
All the more reason to be sure that the conditions are safe to use the function.
I've tested for the existence of related records using IsEmpty( ) prior to using GTRR since it was introduced back in v9. I believe that IsEmpty() is safest for all situations.
If [ not IsEmpty( table::id ) ]
Go to Related Record [ ... ]
End If
3 Likes
IsValid () also provides information about the existence of related records in a given context.
2 Likes