MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

Server-Side Scripting Improves Performance by 99%

June 20, 2013 by Darren Burgess 7 Comments

Target

MightyData developed an order management system for Lettermen Sports several years ago.  Lettermen Sports is one of the premier sports apparel production companies in Minnesota with locations in Blaine and Plymouth and a large production facility where it completes nearly 300 orders per week for sports apparel screen printing, lettering and shipping.

The primary purpose of its FileMaker application is to manage the flow of orders through its production facility. A core facet of the solution is its interface with the SQL-based point-of-sale program, CounterPoint. The FileMaker solution was developed by MightyData to import orders and order detail from CounterPoint.

Performance of the import/update process began to lag behind the increasingly fast pace of the business as the record counts in some of the FileMaker tables grew to over 300,000. With the update process taking an average of 23 minutes, the in-house FileMaker server performance was being compromised by the load. Employee productivity was reduced by having to wait for orders to be imported, and by the performance hit to the FileMaker server.

Lettermen Sports’ management approached MightyData to re-design the order import process to accomplish the following:

  • Reduce the time required to import and update orders from CounterPoint to the FileMaker order management system
  • Automate the import process
  • Eliminate the up-to-24-hour delay for new and updated CounterPoint orders to be created/updated in the FileMaker database

Technology

MightyData’s technical team arrived at a solution to redesign the import/update process to more closely match the current needs of the business. The original import/update process involved importing data from the point-of-sale solution using FileMaker’s import script step to update matching records and create new records that were unmatched. Import subscripts were then used to import data from across the CounterPoint solution so that data required for shop floor production was available in the FileMaker database. This approach was very effective at ensuring that all data was successfully imported as the solution would import across all orders and line item detail each time the update was run.

As the business rules evolved, however, the update process became more complex. Combined with high record counts, a new approach was required.

The new design was based on only working with new and changed records in the CounterPoint system. In so doing, the number of order records that the scripts were required to process was reduced dramatically, to just 3 or 4 at a time. Conveniently enough, CounterPoint provides an order creation and update timestamp which is updated any time an order or its line-item detail is changed. This allowed for a complete overhaul of the scripting process that simultaneously accomplished all three of the customer’s goals. Furthermore, all script execution was moved to the server, allowing staff to focus on order production and not on managing the update process.

Transformation

This project is an excellent an example of how value is driven by the interface of a customer’s need with great technical planning and execution in a spirit of partnership with the customer to get the job done well. The new solution reduced the update execution from an average of 23.4 minutes to just 2.5 seconds – a 99.8% performance gain. While technically impressive, the real value comes from production staff getting new orders and updates no longer than 30 minutes after they are recorded in the point-of-sale system. If even that is too long, the update can be run manually in a few seconds, giving the team immediate access to new order data.

As an added bonus, MightyData also gets to work with some really good people at Lettermen Sports. They help us help them. And that partnership makes all the difference.

Filed Under: Scripting Tagged With: Case study, Performance, Server-side scripting

FileMaker Server-Side Scripts: File Paths

March 9, 2012 by Anders Monsen 3 Comments

Folder structure for server files

In an earlier post I wrote about FileMaker Server’s ability to combine system scripts, or shell scripts, with native FileMaker scripts. This shows one of the many ways that FileMaker integrates with external systems, one of the great benefits of working with FileMaker. Yet aside from integration, FileMaker’s server-side scripting also runs native FileMaker scripts. This can off-load many tasks currently assigned to robot boxes, computers whose sole purpose is to connect to FileMaker Server, mimic a user and run various events. With enhancements in FileMaker Server 10 and 11, a veritable movement has risen in the FileMaker community to stomp out robot usage in favor of handling such events through FileMaker Server instead.

There are advantages to running regular tasks through Server. Robot boxes take up resources. The computer running scripts might be better served elsewhere. Security considerations also play a part. Where does the robot box sit? Is it a headless box in a server room, or on someone’s desk? If the robot goes down or fails, how quickly will this be noticed? What about backups? Moving the scripts to server might remove some of this concern.

FileMaker’s Knowledge Base contains a couple of brief entries on server-side scripting, one defining the issue, another looking at differences between client and server-side scripting. Referring to these documents while setting up server-side scripting provides a nice overview of options and things to consider, but there are additional caveats.

External File Paths

Consider the server folder structure. Organizing databases into folders both gives a visual overview of separate types of files, and helps break down the backup process into more discrete parts. While relative file paths like file:filename.fp7 will still find other files, even across separate folders, they will break with server-side scripting across multiple files.

Folder structure for server files

To test this issue, I took a copy of SeedCode complete, which uses three files, and split these into two folders, one for the data, and one for the UI. This will mimic a situation where running backups on the UI might differ from the data files. I then created a script in the UI file that found records in the data file through the External Data Sources. This script worked perfectly when the files were in the same folder. When you separate the files, the server side script failed. This failure showed in the Schedule view, under status – “FileMaker Script error”.

Server-side script error

To see what happened, I swithed over to the Log Viewer and enabled Server Events. There you can get more details about any errors. In this instance it showed error 100: File not Found.

Server-side script error

The recommended solution is to address the folder in External Data Sources, in addition to the relative path.

I first tried listing just the folder name:

file:SeedCodeData/SeedCodeData

This failed, as did adding a dash in front of the folder name. The solution, recommended by Lisette Wilson, was add two periods in front of the folder name, something quite typical in PHP include files (as well as HTML). The new format looked like this:

file:SeedCodeData
file:../SeedCodeData/SeedCodeData

When I ran the schedule again – no errors! Actually, there was one small error. Since any OnOpen script also runs, I had modified the script to look for Get ( ApplicationVersion ) = “Server”. This would bypass the regular process and skip any incompatible steps. Yet, I still got an error indicating the opening script was still running. The actual calculation result was “Server 11.0v4”. Once I adjusted for this, there were no errors in the Event Log.

Server-side script OK message

Conclusion

This process illustrated some of the frustrations with server-side scripting – there is no Script Debugger when running a server-side script. Even if the Event log shows no error, there may still be unknowns in the process that need to be debugged. To deal with this I often build an event log table, and populate this with information from the script during testing. Also, the exact format of the folder path in External Data Sources took a while to track down. Still, with caution and planning, the possibilities opened by server-side scripting are well worth investigating.

Filed Under: Scripting Tagged With: Server-side scripting

FileMaker Server-Side Script Sequences

February 22, 2012 by Anders Monsen 1 Comment

FileMaker Server script types

FileMaker solutions often employ what’s know as robots–computers that run FileMaker scripts so that users do not need to execute scripts manually. The release of FileMaker Server 9 debuted server-side scripts, which can reduce the need for robots and take advantage of Server schedules. In this post I’m going to take a brief look at this scripting option.

FileMaker Server script types

In FileMaker Server, when you select “Run Script” there are three kinds of scripts available:

  • FileMaker script
  • System-level script
  • Script sequence

As with any instance of limited resources, decisions must be weighed before proceeding. FileMaker Server has a limit of 50 schedules. System-level scripts can be run as cron events (Mac OS X) and system events (Windows), so these scripts might not be the best use of FileMaker Server’s schedule resources.

Running FileMaker scripts can off-load client processes onto the server, thus eliminating the use of robot machines. Script sequences allow a mix of both worlds, since you can script both FileMaker and the OS to run repetitive tasks.

Getting Started

Setting up schedules takes place in the Admin Console, which has a series of easy steps to create a schedule. Once you have selected “script sequence” for the type, you’ll see a list of databases along with a place to enter an account to access the database.

Select script dialog

Since a database is usually accessed by FileMaker Pro, special consideration is necessary to run scripts server-side. A file with an OnOpen script needs to take Server into consideration. One option is to use Get ( ApplicationVersion ) and exit the opening script if the result is “Server”.

After the database and account are entered, you select the script that the server will run. (Adding an option to filter for scripts like the Manage Scripts dialog in FileMaker Pro might be a nice future enhancement for FMS.) As with FileMaker Pro, you also can specify an optional parameter.

Setting Sequence Permissions

The next step is to select the sequence options, which is where it gets interesting. Here you can select system-level scripts to run before and/or after your FileMaker script, along with any system-level parameters (see below). Importantly, you also select the account to run the system-level scripts. On Mac OS X this is important because it must match the permissions on the system-level script inside the Scripts folder for FileMaker Server.

Script sequence dialog

Since the default account is fmserver, you should set the system-level script username to fmserver and the group name to fmsadmin. The drop-down will let you select any script in the Scripts folder, but permission issues can cause errors in the execution of the schedule. An enhancement in FMS 11 allows you to set a time limit on the schedule, which can be helpful when a script sequence may run too long and collide with subsequent schedules. In the example below the FileMaker script runs after the shell script. Other examples might have a shell script run first, followed by a FileMaker script, then another shell script. This could involve some method to load and transform data, then export and move the data via the OS. This opens many additional possibilities for FileMaker integration.

Conclusion

Unfortunately, large aspects of server-side scripts remain in a black box environment. Error messages aren’t always clear. The script may fail and the Schedule status shows a FileMaker error. Checking the Log Viewer usually provides more clues, such as Error 100 for file not found, or another FileMaker error. Sometimes the status may be OK but the shell script portion fails, which requires troubleshooting outside FileMaker. Agnes Riley of Zero Blue wrote a post in 2010 covering some of the caveats. Despite these cautions, server-side scripting has tremendous benefits and power.

A subsequent post will look more closely at running FileMaker-only server-side scripts. Does your solution use sever-side scripts? What are some of the best practices and problems you encountered?

Filed Under: Scripting Tagged With: Admin console, Best practices, Server-side scripting

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in