13 Replies Latest reply on Dec 2, 2015 10:57 AM by ch0c0halic

    Why is PSOS deleting portal rows??????

    jurgmay

      Hello!

       

      I'm experiencing a very weird issue using Perform Script On Server.

       

      I have a script which at a few points would benefit from offloading some search tasks to the server to prevent thousands of records from being transferred to the client. The server would return a list of IDs.

       

      When I start the script a portal is shown on the screen. The script goes to a utility layout and loops through each record and constructs an SQL query (I've checked and the query that is returned is sound and it runs fine if I run it in Data Viewer) Sometimes I need to perform a logical AND against two sets of record IDs to ensure I only process the records whose IDs appear in both lists; this is another task I'd prefer to perform on the server.

       

      If I use 'Perform Script' everything runs fine.

       

      If I use 'Perform Script On Server', WHEREVER I use it, the next thing that happens is the Search Criteria line items which are shown in the portal, disappear! Deleted! There are no more records in that table. I don't use 'Delete Record' ANYWHERE in any of the scripts and I only go to that layout once when constructing the SQL.

       

      I've attached two screenshots showing the layout before the search is performed (showing the search criteria) and after, then the records have been deleted.

       

      The record deleting happens after any PSOS call so it's not any particular subscript that is causing the issue because the problem goes away if I run that same subscript using 'Perform Script'. In fact running all subscripts by calling Perform Script runs just fine... but very slow!

       

      Also, the main script works correctly so I get the right result (a global field is set to the value of a list of record IDs) and results are shown on screen with the correct record count being shown.

       

      The issue I have is the Search Criteria records are being deleted when they shouldn't be.

       

      Does anyone have any idea how I can troubleshoot this? Maybe this has been an issue for other people???

       

      I appreciate any help or feedback.

       

      Thanks,

       

      Juerg

      Screen Shot 2015-12-01 at 20.02.05.pngScreen Shot 2015-12-01 at 20.15.35.png

        • 1. Re: Why is PSOS deleting portal rows??????
          TorstenBernhard

          why do  you use a portal instead of a layout with the table you want to address ( I am trying to understand the mechanics)?

          • 2. Re: Why is PSOS deleting portal rows??????
            jurgmay

            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.

             

            Thanks,

             

            Juerg

            • 3. Re: Why is PSOS deleting portal rows??????
              wimdecorte

              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?

              • 4. Re: Why is PSOS deleting portal rows??????
                jurgmay

                Hi,

                 

                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 ]

                End If

                 

                (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.

                 

                Juerg

                • 5. Re: Why is PSOS deleting portal rows??????
                  wimdecorte

                  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?

                  • 6. Re: Why is PSOS deleting portal rows??????
                    jurgmay

                    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.

                    • 7. Re: Why is PSOS deleting portal rows??????
                      wimdecorte

                      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.

                      • 8. Re: Why is PSOS deleting portal rows??????
                        jurgmay

                        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

                         

                        Cheers!

                         

                        Juerg

                        • 9. Re: Why is PSOS deleting portal rows??????
                          jurgmay

                          Just to confirm that a quick If statement in the OnFirstWindowOpen script has fixed the issue.

                           

                          Thanks so much!

                           

                          Juerg

                          • 10. Re: Why is PSOS deleting portal rows??????
                            wimdecorte

                            You're very welcome.  Glad it go solved!

                            • 11. Re: Why is PSOS deleting portal rows??????
                              RickWhitelaw

                              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?

                              • 12. Re: Why is PSOS deleting portal rows??????
                                wimdecorte

                                Yes it is documented.  Although perhaps a little obliquely.

                                Perform Script On Server

                                and

                                General information about the Perform Script On Server script step | FileMaker

                                 

                                Note where it states that a PSoS script behaves as a server-side schedule script, and those do run the OnFirstWindow script.

                                 

                                You mention:

                                 

                                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.

                                • 13. Re: Why is PSOS deleting portal rows??????
                                  ch0c0halic

                                  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.