8 Replies Latest reply on Apr 8, 2017 3:58 PM by user29536

    Double Run

    user29536

      The group I work for uses Prime Suite for their ERM. With that said there are over a half million appointments in the database.

      I build a file in FileMaker called Forms Console. with just about every form built into it. Since there are so many records in the Prime Suite

      rather than that file taking a large hit every time a medical assistant pulls a list of daily appointments we pull appointments for 10 days, (3 Backward, 7 Forward) into an external SQL file, which is usually between 10K and 13K.

       

      I have a script that I run to get a new list for FileMaker table. In this script since the FileMaker table is for work purpose only.

      The script deletes all records, goes to SQL table and using the custom function FoundList. It gets a list of ID's from the table , comes back to FileMaker table and in a loop script it creates a new record, sets the ID field (all fields do a lookup to SQL) then exits when list in CF is down.

       

      This all works great. I do it this way because the FileMaker server can't do imports. When I run this script on the client it runs and is done.

      Now the weird part. When it runs a scheduled script on the server it runs the script twice. Its's not a big deal. I just can't figure it out as to why it runs twice. There a no scrip triggers on any of the layouts it visits.  When I run the script on the server I can watch what it is doing on the client side and it defiantly runs once, then re-deletes all records and runs again. Anyone else since this ghostly behavior?

        • 1. Re: Double Run
          beverly

          You might consider creating a View in the SQL server that always returns your 10-day range. The view is basically a 'canned' query/find that already narrowing down the data set you need. You can view or query just this table as needed through import or ESS.

           

          Sent from miPhone

          • 2. Re: Double Run
            fmpdude

            user29536 wrote:

            I do it this way because the FileMaker server can't do imports. ..

            It's a simple matter to create INSERT SQL (and any other needed) SQL to directly insert data into FMS. Using JDBC and very simple Java code you could create this logic -- then schedule it to run automatically, or on demand.

             

            The FMP JDBC driver is free and is with your FMP distribution.

            • 3. Re: Double Run
              user29536

              I know about views ... My question is why is the script running twice on the server?

              • 4. Re: Double Run
                beverly

                Can you post a screenshot (or print to PDF) of your script?

                 

                Sent from miPhone

                • 5. Re: Double Run
                  user29536

                  Client_script.pngServer.png

                   

                   

                  As I said run the script on the client it runs once and done.

                   

                  Run the script as scheduled it runs twice.

                  • 6. Re: Double Run
                    beverly

                    You have no found records on the server. It has no context. You are going to a layout but relying on records (which may or may not be).

                     

                    Sent from miPhone

                    • 7. Re: Double Run
                      Jason Wood

                      Yeah, you should have a "Show All Records" in there to be safe, but that wouldn't account for the script running twice.

                       

                      How long does it take for this script to complete on the server?

                       

                      Did you check the server log and does that confirm that the script is running twice?

                      • 8. Re: Double Run
                        user29536

                        the table dbo.formsconsole is always in a show all records mode. So no need for an extra line item in the script.

                        I do have a problem with the amount of records form one table to the next. As I in the original post. The external SQL extracts10 days of appointments from the Master appointment table.. Which is usually in the 12000 range +/- 1000.

                         

                        The reason why thy are brought into a FileMaker table is speed. In SQL the table can only process records in groups of 1000.  I am using a filtered portal which consists of a Date, an office location (1 of 12) with these two the third filter is a relational value list show the providers working at the location on the date chosen. Once the provider is select it generates a list of their appointments. To do this with a SQL table would be should and was until we made it a local FM table.

                         

                        As for the speed of the Server side scheduled script It has a limit of 15 minutes or it will abort. I have it start at 5 AM in the morning and the scripts sends me an e-mail when it is done, Average time e-mail received is 5:12 AM

                        So it never times out. Probably should should be done in 6 minutes If the server side script would run only one time.

                         

                        Remember It is not quantity or time It is why does it run 2 times on the server, but if I run it on a client it runs once?

                        It is the same script that is run in both cases.