FileMaker scripts you should run as Perform Script On Server (PSOS) for better performance

Perform Script On Server (PSOS) is a powerful feature in FileMaker that can significantly enhance the performance of your database solutions. By executing scripts on the server rather than the client, PSOS can dramatically reduce processing time, especially for data-intensive operations. This article explores the types of scripts that benefit most from PSOS and provides guidance on implementing this feature effectively.

Understanding PSOS

PSOS allows FileMaker clients to offload script execution to the server, which typically has more processing power and faster access to the data. This can lead to substantial performance improvements, particularly for users connecting over slower network connections or when dealing with large datasets.

Key benefits of PSOS:

1. Reduced network traffic
2. Faster script execution
3. Improved client responsiveness
4. Better utilisation of server resources

Scripts ideal for PSOS

Certain types of scripts are particularly well-suited for execution via Perform Script On Server (PSOS). These scripts typically involve operations that are computationally intensive or require processing large amounts of data. By running these scripts on the server, you can significantly improve performance and reduce the load on client machines.

Data processing operations

Data processing operations are among the most common and beneficial scripts to run using PSOS. These operations often involve working with large datasets or performing complex calculations that can be time-consuming when executed on a client machine.

Importing data from external sources

PSOS can dramatically speed up data import processes, especially when dealing with large datasets. For example:

CSV or Excel imports: When importing data from CSV files or Excel spreadsheets, PSOS can process the data much faster than a client machine, particularly for files containing thousands or millions of records.

API integrations: Scripts that fetch data from external APIs and then process and import that data into FileMaker can benefit greatly from PSOS, as both the API communication and data processing occur on the server.

Exporting large datasets

Similar to imports, exporting large amounts of data can be significantly faster when performed on the server:

Report generation: Creating complex reports that involve aggregating data from multiple tables and exporting to formats such as Excel or PDF.

Data extracts: Generating data extracts for external systems or data warehouses.

Batch updates or deletions

Scripts that need to update or delete multiple records can see substantial performance improvements when run via PSOS, using Replace Field Contents or in a record-by-record loop. For example:

Mass record updates: For instance, updating pricing information across thousands of product records.

Data cleansing operations: Scripts that standardise data formats or correct inconsistencies across large numbers of records.

Archiving old data: Moving or deleting old records based on certain criteria.

Complex calculations on multiple records

PSOS is ideal for scripts that need to perform intensive calculations across many records:

Financial calculations: Such as computing compound interest or depreciation for a large number of assets.

Statistical analysis: Performing statistical calculations on large datasets.

Data transformation: Scripts that need to restructure data or perform complex transformations across multiple tables.

Text processing and analysis

Scripts that involve processing large amounts of text can benefit from PSOS:

Full-text indexing: Creating or updating full-text search indexes for large text fields.

Natural language processing: Performing text analysis tasks such as sentiment analysis or keyword extraction on large volumes of text data.

Data validation and integrity checks

Scripts that ensure data quality and integrity across large datasets are well-suited for PSOS:

Consistency checks: Verifying that related data across multiple tables is consistent.

Duplicate detection: Identifying and potentially merging duplicate records in large datasets.

By leveraging PSOS for these types of data processing operations, FileMaker developers can create more efficient and responsive solutions, particularly when dealing with large datasets or complex calculations. This not only improves performance but also enhances the user experience by reducing wait times and freeing up client resources for other tasks.

Report generation

Generating reports often involves aggregating data from multiple tables and performing calculations. Running these scripts on the server can dramatically reduce the time required, especially for complex reports involving large datasets.

Scheduled tasks

FileMaker Server allows you to schedule scripts to run at specific times. These scheduled scripts are ideal candidates for PSOS as they can run in the background without impacting client performance.

Common scheduled tasks:

1. Daily backups
2. Data archiving
3. System maintenance routines
4. Automated email reports

Implementing PSOS effectively

Context considerations

When using PSOS, it's crucial to remember that the server session doesn't inherit the context of the client session. You must explicitly set the correct context (layout and found set) at the beginning of your server-side script.

Error handling

Robust error handling is essential in PSOS scripts. Since these scripts run on the server, any errors or unexpected behaviour won't be immediately visible to the user. Implement comprehensive error checking and logging to facilitate troubleshooting.

Avoiding UI elements

PSOS scripts should not include any user interface elements or steps that require user interaction. These will fail when run on the server and can cause your script to halt unexpectedly.

Performance optimisation techniques

Minimise field references

Each field reference in a PSOS script requires communication between the server and the client. Minimise these references by using variables to store intermediate results whenever possible.

Use Set Field by Calculation

When updating multiple fields, use the "Set Field by Calculation" script step instead of multiple individual "Set Field" steps. This reduces the number of commit operations and can significantly improve performance.

Leverage server-side processing

Take advantage of the server's processing power by moving complex calculations and data manipulations to the server side. This can include using custom functions and complex FileMaker calculations within your PSOS scripts.

Monitoring and debugging PSOS

Server-side logging

Implement comprehensive logging in your PSOS scripts to track their execution and any potential issues. This can be invaluable for debugging and performance tuning.

Performance metrics

Use FileMaker Server's built-in statistics and logging features to monitor the performance of your PSOS scripts. This can help identify bottlenecks and opportunities for optimisation.

Considerations and limitations

While PSOS can significantly improve performance, it's not suitable for all scenarios. Be aware of these limitations:

Not all script steps are compatible with PSOS. If you go to the Scripts menu in FileMaker then look at the top right hand corner of the screen, you’ll see the shadowed black box icon whose tooltip says: "Show compatible script steps by product". If you choose "Server” from the list, you’ll see that FileMaker greys out all the steps that are not compatible with PSOS. These include, for example, some of the Window script steps, all the Spelling steps and many of the Miscellaneous steps.

PSOS scripts cannot interact directly with the user interface.

There's a limit to the number of concurrent PSOS sessions.

Conclusion

Perform Script On Server is a powerful tool for optimising FileMaker solutions, particularly for data-intensive operations and background tasks. By carefully selecting which scripts to run via PSOS and implementing them with best practices in mind, developers can significantly enhance the performance and responsiveness of their FileMaker applications. The complexity of effectively implementing PSOS underscores the value of ongoing professional development and hands-on experience in FileMaker development, so make sure you learn how to use PSOS properly!