7 tips for intermediate FileMaker Pro users

As an intermediate FileMaker Pro user, you have probably mastered the basics and are now looking to enhance your skills further. This article presents seven advanced tips designed to help you optimise your FileMaker Pro experience, streamline workflows and create more powerful database solutions.

1. Harness the power of custom functions

Create a library of utility functions: Custom functions are a powerful feature in FileMaker Pro that allow you to create reusable code snippets. These functions can significantly reduce repetition in your scripts and calculations, making your solutions more efficient and easier to maintain.

Developing a set of custom functions for common tasks such as date manipulation, string formatting and data validation can save you considerable time reinventing the wheel. For instance, if you often need to format phone numbers or dates, having dedicated custom functions will ensure consistency across your solutions. There are libraries of free custom functions available online that the FileMaker community have put up to help developers in their work, for example, at www.briandunning.com.

A custom function will very likely use the LET () function as its primary starting point, for its helpfulness in building a complex formula.

Example: a custom function called CF_ListToCommas, with the parameters ( ListOfItems ; And_Or_Parameter ; MaxNumber ), whose purpose is to convert a carriage-return delimited list to one separated by commas, with the words “and” added at the end if the number of list members is large.

/*

Converts any return-delimited list to items separated by commas, with the word "and" before the last item. No full stop (period) at the end in case it's not needed.

Specify the list, whether to use AND or OR as the last separator and the maximum number of items to name before stating 'and N others'. Result examples:

Red, Blue, Green and Yellow

Bill, Sue, Jo, Peter and 6 others

*/

Let(

[

List = ListOfItems ;

Max = If ( IsEmpty ( MaxNumber ) ; 99999 ; MaxNumber ) ; // Use a large number if no specific maximum have been set

CountValues = PatternCount ( List ; "¶" ) + 1 ;

Extras = If ( CountValues > Max ; CountValues - Max ; 0 ) ;

LastValue = RightValues ( List ; 1 ) ;

FirstValues = If ( Max ≥ CountValues ; LeftValues ( List ; CountValues - 1 ) ; LeftValues ( List ; Max ) ) ;

FirstValues = Substitute ( FirstValues ; "¶" ; ", " ) ;

/* The last 2 chars are a comma and a space so remove these */

FirstValues = Replace ( FirstValues ; Length ( FirstValues ) - 1; 2 ; "" ) ;

Result = Case (

   not IsEmpty ( FirstValues ) and Extras = 0 ; FirstValues & " " & And_Or_Parameter & " " & LastValue ;

   not IsEmpty ( FirstValues ) and Extras = 1 ; FirstValues & " " & And_Or_Parameter & " one other" ;

   not IsEmpty ( FirstValues ) and Extras > 1 ; FirstValues & " " & And_Or_Parameter & " " & Extras & " others" ;

LastValue ) ;

FinalResult = Substitute ( Result ; "¶" ; "" )

] ;

FinalResult

)

Use recursive functions: Recursive custom functions can be incredibly useful for complex calculations, such as working with hierarchical data. They allow you to perform operations that would be difficult or impossible with standard calculation fields. For example, calculating factorials or traversing tree structures can be elegantly handled with recursion.

2. Master advanced scripting techniques

Scripting is at the heart of FileMaker Pro's automation capabilities. By mastering advanced scripting techniques, you can create more sophisticated and efficient solutions.

Use script parameters: A script parameter is simple a piece of information that is passed to a script at the time it runs, whether this is through a button being clicked, a sub-script called by another script or a script trigger. Rather than relying on global variables, use script parameters to pass data between scripts to improve script portability and make your solutions easier to debug and maintain. Script parameters also help avoid conflicts that may arise from using global variables across different scripts.

For example, suppose you have a portal of sales figures on multiple layouts in your solution. One on the Sales Team layout to show all the sales made by a sales person, another on a Client layout to show all the sales made to a particular client, another on a Monthly Periods layout to show all the sales by month, and so on. Your goal is to form a found set of records from the portal using Go To Related record, sort them and print them out (or create a PDF from the found set). Without script parameters, you would have to write a script for every situation. But if you run the script with a script parameter, you can use the same script. For for example, on the Sales Team layout, the script parameter would be the phrase “Sales Person”. On the Sales Team layout, the script parameter would be the word “Client” and on the Months layout, the parameter would be “Month”. Then you just need to add logic to your script along the lines of:

If

   Get (ScriptParameter) = “Sales Person”

   Go To Related Record (SalesPerson_SALES_SalesPersonID)

Else If

   Get (ScriptParameter) = “Client”

   Go To Related Record (Client_SALES_ClientID)

Else If

   Get (ScriptParameter) = “Month”

   Go To Related Record (Months_SALES_MonthNumber)

End If

Implement error handling: Robust error handling is crucial for creating reliable solutions. For example, in a search script, failing to find a record after a Perform Find script step returns a notoriously unhelpful message asking whether the user would like to cancel, continue or modify the find. Use the 'Set Error Capture' step to suppress this unhelpful message and instead, present custom error messages using Show Custom Dialog to more gracefully handle unexpected situations. This practice not only enhances user experience but also aids in troubleshooting.

3. Optimise performance with indexing and found sets

Understanding how FileMaker Pro handles data retrieval can help you create faster, more responsive solutions.

Leverage indexing: Properly indexed fields can dramatically improve search and sort performance. Pay attention to which fields are indexed and consider adding indexes to fields frequently used in searches or relationships. This practice ensures that queries run efficiently, especially as your dataset grows.

Use found sets efficiently: Instead of repeatedly performing finds, manipulate found sets using script steps such as 'Constrain Found Set' and 'Extend Found Set'. This approach reduces processing time significantly when working with large datasets, allowing for quicker access to relevant records without redundant searches. In some circumstances, for example, in unusually large record sets, constraining a found set without indexes may speed up the search too. (This option was added to the Constrain Found Set script step in FileMaker 21.1).

4. Implement advanced security measures

As your solutions become more complex, implementing robust security measures becomes increasingly important.

Use privilege sets effectively: Create granular privilege sets that align with user roles in your organisation. This practice ensures that users have access only to the data and functionality they need while protecting sensitive information from unauthorised access.

Implement external authentication: For enterprise-level solutions, consider implementing external authentication methods such as OAuth or Active Directory. This approach centralises user management and enhances security by allowing integration with existing authentication systems.

5. Leverage the power of ExecuteSQL

While FileMaker Pro's native find and sort capabilities are powerful, ExecuteSQL offers even more flexibility for complex data retrieval tasks.

Use ExecuteSQL for reporting: ExecuteSQL can be particularly useful for creating complex reports that involve data from multiple related tables. It allows you to retrieve exactly the data you need without creating additional relationships, enabling more efficient reporting processes.

Optimise SQL queries: Learn to write efficient SQL queries by using appropriate indexes and avoiding unnecessary joins. Well-optimised queries can significantly improve the performance of your solutions and reduce server load during data retrieval operations.

Use Virtual Lists: Use ExecuteSQL for reporting in conjunction with Virtual Lists. These are record sets based on a generic table and fields that builds records on the fly from lists of values stored in global variables, extracting the columns from the lines that form the members of the list with calculations. Virtual Lists are a good way for users to make their own data selection from record sets, for example with the help of drop down menus, and then run a script to build the report in a reusable table of records, not tied to any one original source table.

6. Master portal filtering and sorting

Portals are a fundamental feature of FileMaker Pro but many users do not take full advantage of their capabilities.

Use portal filtering: Instead of showing all related records in a portal, use portal filtering to display only relevant information based on user input or specific criteria. This technique enhances user experience by reducing clutter in layouts and focusing attention on pertinent records.

Implement dynamic sorting: Allow users to sort portal data by clicking on column headers. This feature enhances usability and gives users more control over how they view related data, making it easier for them to find what they need quickly. The method is to use a sort field for the portal that is a calculation based on the GetField (fieldname) function. By varying the referenced field name with GetField(), the field name whose values determine the sort order of the portal can be changed by the user, as they click on column headings running a script that includes a script parameter that indicates the column the user clicks on.

7. Embrace the FileMaker Data API

The FileMaker Data API opens up new possibilities for integrating FileMaker Pro with web services and other applications.

Use the Data API for web integration: Leverage the Data API to create web applications that interact with your FileMaker Pro solutions. This approach allows you to extend the reach of your databases beyond the traditional FileMaker Pro client, enabling more versatile application development.

Implement OAuth for secure access: When using the Data API, implement OAuth authentication to ensure secure access to your data. This method is becoming the industry standard for API access due to its enhanced security features compared to basic authentication methods.

Conclusion

Mastering these seven advanced techniques can significantly enhance your FileMaker Pro skills and allow you to create more powerful, efficient and user-friendly solutions. Effectively implementing these tips requires a reasonably deep understanding of FileMaker Pro's architecture and best practices, so you might want to consider attending a training course, joining FileMaker user groups or attending workshops focused on advanced features and best practices. Pursuing advanced certifications can also deepen your expertise and help keep you up-to-date with the latest features and techniques.

Related Training Courses

Useful Resources