why do you use a portal instead of a layout with the table you want to address ( I am trying to understand the mechanics)?
The portal is only there as a data entry tool for the user to construct their search terms. When they click Search the script goes to the source table of the portal and loops through the records.
In case there is a misunderstanding - I'm not looping through the portal rows, I'm going to a layout with the table I want to address and constructing an SQL query. I then perform that query, get a list of IDs back and use that to compare against an existing list of IDs by performing a logical AND and using the resulting ID list as a global key to display the results in another portal.
It is not very clear what your PSoS script does exactly, you mention that it does do portal looping but in your original post it sounded like it did. Can you expand a bit on what exactly is in the PSoS script and what parameters you pass to it?
I'd didn't mention portal looping - I said that I go to a utility layout to do the looping.
The main script is triggered by the user clicking a button. One of the subscripts which, when called via PSOS, will cause the portal records to be deleted is shown here -
If [ #Assign ( Get ( ScriptParameter ) ) ]
Set Variable [ $searchResults ; Value: ExecuteSQL ( $sqlQuery ; "" ; "¶" ) ]
Exit Script [ Result: $searchResults ]
(Sidenote: The #Assign statement is from www.filemakerstandards.org. It takes a parameter passed as a name/value pair and assigns the value to a local variable. The variable name is taken from the script title (in this case 'Execute SQL ( sqlQuery ) yields the local variable $sqlQuery)
An example SQL statement that gets passed is -
SELECT Customers.id FROM Customers WHERE Customers.isDeleted = 0 AND Customers.isActive = 1
It is assigned to a variable $query and is passed as a parameter in the format '# ( "sqlQuery" ; $query )' which is the expected format of the #Assign statement in the PSOS script.
As you can see, there is very little code here and nothing at all to do with deleting records.
I'm convinced it's a bug as I can use PSOS on any subscript and the same thing happens.
Thanks for your help.
Can you confirm that the records that disappear from the portal are actually deleted? You can not find them anymore in the child table?
I doubt that this is a bug, it is more likely that something else in your script (not the PSoS part) is either killing the relationship or deleting the records. But I could be wrong; if it is a bug then we should be able to replicate it in a simple file that does nothing but the PSoS part, have you tried to confirm it that way?
Yes, they are definitely deleted. They don't exist anywhere in the child table - the record count is 0 (zero).
When I use Script Debugger the records disappear from the portal immediately after ANY subscript that is called by PSOS which makes me think it's a bug. If it was bad coding it would occur in the same place everytime but it doesn't. If I set one subscript to 'Perform Script' and a later subscript to 'PSOS' the error occurs later in the main script. If I switch so that the first subscript is 'PSOS' then the error occurs after that call.
I haven't tried to replicate it yet as I'm not sure exactly how! I'll take a look and see what I can do.
Keep in mind that PSoS will first run the OnFirstWindowOpen script if there is one configured. So that sequence could be responsible, especially if it contains any script steps that are not server-compatible.
Oh man, are you kidding? I had no idea that PSoS called OnFirstWindowOpen as there's no interface! I clear the query table on launch so without even checking I know that's going to be the issue.
You're my hero!!!!!!!! If you're ever in the UK there'll be a nice cold beer waiting for you
Just to confirm that a quick If statement in the OnFirstWindowOpen script has fixed the issue.
Thanks so much!
You're very welcome. Glad it go solved!
Wim, I've never heard of PSOS running the on first window open script before and although I don't use it yet I plan too. I've read about it don't remember seeing this. Is it documented? Are you saying it will run the opening script of a file that's already open or on files opened by sub scripts?
Yes it is documented. Although perhaps a little obliquely.
Note where it states that a PSoS script behaves as a server-side schedule script, and those do run the OnFirstWindow script.
Are you saying it will run the opening script of a file that's already open or on files opened by sub scripts?
And that is where the most common logical flaw is: by calling PSoS you are in effect creating a completely new user session on the server. That user session basically logs in as you but starts completely anew; it does not know what layout you are on or what found set you are on, it just logs into the database, runs the OnFirstwindowOpen and then your script.
It does not know what files you have open in your local session, it does not know what layout you are on or what records you have active. You have to re-establish those in the PSoS session by passing variables to your PSoS script.
In addition to all of the above: the fact that you spawn a completely new user session but one that runs on the server in effect makes FMS an "application server" instead of a pure "database server". So you have to be careful that your server can actually handle that extra load. I've seen many posts by developers that say "PSoS is x times faster than running it locally". And it is true, but it may not remain true if you have 10/20/50/100 users doing that all of the same time and your server does not have the processing power to cope with all of that.
I am not discouraging the use of PSoS, I'm just saying: consider all aspects; especially the current performance baseline and what adding the load will do to the server and every user's experience.
The PSoS script step starts a new user session on the FMS. Its as if a new computer had connected to the DB, which runs the OnFirstWindowOpen script. Then this new session runs whichever script is called in the PSoS step.
You must be sure to establish the context and found set within the script and DO NOT rely on anything in the current user's session. This included any variables and changed global Fields.
Also, all script steps must be server compatible.