1 2 Previous Next 23 Replies Latest reply on May 20, 2016 3:13 AM by ianmanning

    Alternative to Export Field Contents that works on server?

    ianmanning

      I have a script that needs to run hourly, daily , weekly etc so I would like to run it on the server.

      The current script uses Export Field Contents in a loop to step through hundreds of records and save the contents of a single field in to separate files for each record (1 file for every record)

      Export Field Contents will not run on the server, does anyone have a simple solution to this?

       

      I believe I can solve it by:

      opening a new window

      finding the current record in the new window  (one record at a time)

      Export Records (only 1 record found so it will only export the 1 record)

      switch back to the first window

      loop to next record

       

      I think this will take longer than the Export Field Contents and sometimes there will be thousands or hundreds of thousands of records to export.

       

      Thanks for any help

      Best regards

      Ian

        • 1. Re: Alternative to Export Field Contents that works on server?
          Extensitech

          A small improvement to the process might be to have a self-relationship (where pk = pk) and instead of new window and find, do gtrr in new window. I would expect that to go pretty quickly on the server, where the windows don't actually get "drawn".

           

          Chris Cain

          Extensitech

          1 of 1 people found this helpful
          • 2. Re: Alternative to Export Field Contents that works on server?
            rgordon

            If you use the self relationship I don't think you need the gtrr.  You can export the related field.  It should always be just one record.

            1 of 1 people found this helpful
            • 3. Re: Alternative to Export Field Contents that works on server?
              siplus

              There's always the alternative of installing Filemaker Pro on the server and starting a database that does all you want, with no restrictions that FMS has, via a scheduler like Task Till Down.

              • 4. Re: Alternative to Export Field Contents that works on server?
                ianmanning

                Thanks Chris Cain, Extensitech, for the great idea I will try it out. it will also help with a previous problem I had.

                Best regards

                Ian

                • 5. Re: Alternative to Export Field Contents that works on server?
                  ianmanning

                  Thanks rgordon, I will try your idea and let you know if it works.

                  Best regards

                  Ian

                  • 6. Re: Alternative to Export Field Contents that works on server?
                    ianmanning

                    Thanks siplus,

                    Unfortunately the server is windows not MAC so Task Till Dawn wont work.

                    I had thought of installing Filemaker pro on the server and using scheduled scripts on the server to change a field on the installed pro  with a script trigger on the field that will then run the script on the pro. My only concern is that if server shuts down (power cut etc) that although Filemaker server will restart the Pro database will not or is there a way of getting the Pro database to start automatically?

                    Best regards

                    Ian

                    • 7. Re: Alternative to Export Field Contents that works on server?
                      RubenVanDenBoogaard

                      You can use the free BaseElements plugin which has an Export Field Content version which works on server.

                       

                      BaseElements Plugin | Goya Pty Ltd

                       

                      hth

                       

                      Ruben

                      • 8. Re: Alternative to Export Field Contents that works on server?
                        CamelCase_data

                        This all sounds intriguing.

                         

                        I'd be curious to get some more context on exactly what you're trying to achieve here - maybe there's a different approach that can work better. If I understand you right, and you are actually going to export hundreds of thousands of records, each into it's own file, that's never going to be close to fast no matter which technique you use.

                        Do you have a maximum time within which this routine must complete? Have you done any actual benchmarking of the method you currently use?

                         

                        I'm not sure Export Field Contents is necessarily the best approach to your problem, but if you do go that way, I really like the technique described at https://blog.beezwax.net/2014/04/21/100x-faster-flight-testing-filemaker-13-perform-script-on-server-part-ii/. There are also a number of plugins you can use, e.g. the free BaseElements plugin: https://www.goya.com.au/baseelements/plugin.

                        • 10. Re: Alternative to Export Field Contents that works on server?
                          ianmanning

                          For David Wikström and anyone else interested in the whole project.

                          Locally (inthe UK) we have our ERP system (Program for sales, stock, invoices etc) this runs on top of a Microsoft SQL database.

                          We have a Quote Database that I wrote running on Filemaker server 12.

                          We now have a new CRM (customer relationship Management) database that is provided by our parent
                          company in Germany. It is also hosted in Germany and is Microsoft Dynamics based.

                          For a number of reasons the interface between our local systems and the CRM is being done by sending and receiving XMl files (they are not really XML but text files with a field structure that looks like XML, but no headers etc. This also means I can’t use Filemaker XML export)

                          Each record has to be in a separate file

                          Each record type (ie Contact, Company, Order, Invoice, Quote) has to be sent to a different directory/folder.

                          It would be much better if we could simply let the databases talk to each other but that
                          isn’t an option.

                          I have a Filemaker database that sits between our local systems and the German system.

                          This database retrieves the:

                          Quotation information from the “Quote Database”

                          Order Info from the ERP

                          Invoice info from the ERP

                          Stock info from the ERP

                          It also combines the Contacts, Companies, product info and prices from both the Quote database and the ERP as both systems have this information but not necessarily the same information.

                          New Contacts, Companies etc are also sent from the CRM (via Text files) to be added to our local systems.

                          I create the XML data in a text field then export it to a file which then gets sent via ftp to Germany

                          Obviously some information changes very frequently and some only occasionally.

                          Invoices are only generated once a day so I only send that info once a day but orders are constantly being generated so I have to send them every few minutes.

                          If we have a price increase I will have to send the whole product list to the CRM with the new prices and there are 90,000 products.

                          To start with I have to send about 1,000,000 historical records, orders invoices etc so that we have some history in the CRM.

                          As you can see the volume of data files to send varies greatly, I am trying to organise it so that I send fewer files but more frequently so that the processing time isn’t that important.

                          If you want to know more please ask.

                           

                          As far as time to process each task I will schedule the Bulk uploads ( price increase etc) over a weekend

                          Tasks with over 100 files I will schedule for each evening but less then 100 files I will do constantly throughout the day.

                          Also I have to sequence tasks as contacts cannot be sent before their company has been added. Order header has to be added before order details etc. New products have to be added before a quote or an order.

                          Thanks for everyone's advise

                          Best regards

                          Ian

                          • 11. Re: Alternative to Export Field Contents that works on server?
                            beverly

                            Ian, export as XML can use an XSLT to also provide text (just looks like XML with whatever you want in it, such as no 'headers').

                            What are you using to FTP?

                            you may find that plug-ins (there are several) may help with some of the export and transmission - look for ones that work on the server

                            and/or consider a "feed" that is web-based that the MS SQL can consume

                            beverly

                            1 of 1 people found this helpful
                            • 12. Re: Alternative to Export Field Contents that works on server?
                              CamelCase_data

                              Certainly lots of challenges here, ianmanning - not the easiest or most logical requirements, but certainly the kind of stuff one runs into now and then.

                               

                              If we accept the premise that you do need to transfer the data as text files via FTP, I would perhaps start by looking into plugins that allow you to transfer the data via FTP referencing a variable or a field, without actually creating a file on the FileMaker side. That should save at least some time. You can probably use a plugin and/or custom functions to compile the XMI data for each record.

                              I have seen pretty significant performance differences between different FTP plugins (in particular with larger files), and you may want to do try a few different ones.

                               

                              Another idea, at least for the huge 1M-upload, would be to actually do generate the files, but then zip the files before upload (hoping that the unzip can be done on the receiving end). Sometimes uploading one huge file is a lot faster than transferring a very high number of small files of the same total size.

                               

                              As I'm sure you know ,you're also going to want to make sure you have a very powerful server computer... It might also be good to see if simply upgrading to FileMaker Server 14 can improve performance (if that is an option at all).

                              1 of 1 people found this helpful
                              • 13. Re: Alternative to Export Field Contents that works on server?
                                beverly

                                +1 on the zip then FTP, David!

                                +1 on the powerful server, too.

                                beverly

                                • 14. Re: Alternative to Export Field Contents that works on server?

                                  Are you up for a little programming?

                                   

                                  This would be a snap with an external Java program using JDBC to connect to your live FM database on the server.

                                   

                                  Just issue a SQL to get a ResultSet holding your fields, then walk through the ResultSet and save each field to a file.

                                   

                                  You could also schedule that file to run using either a MS Scheduled Task or a Mac Cron job. Also a snap. And, you could run that Java code from anywhere.

                                   

                                  Totally easy.

                                   

                                  (And, unlike the typical FMP "plug-in" approach ($$$), the software do do this would be completely FREE to buy (Hurray! Nothing to Buy!) and to host.)

                                   

                                  HOPE THIS HELPS.

                                   

                                  - m

                                  1 2 Previous Next