Case Study: Multi-Office Solutions

Multiple Offices Or Locations In A Solution

Over the years, Portage Bay has developed a number of solutions of a type generally called ‘multi-tenant’. As I was discussing a possible  multi-tenant project with a prospective client recently, it occurred to me that it would make an interesting blog post.

A multi-tenant solution can offer a number of possible advantages over maintaining separate solutions for separate entities, but there are some significant risks.


  • Eliminates need for data synchronization/aggregation

  • More efficient use of server resources

  • Simpler management


  • More complex programming

  • Higher cost of development

  • Risk of data being visible to inappropriate user

We currently have three Claris FileMaker solutions that we support, and one in 4th Dimension, that can be classified as multi-tenant in one way or another. Two are fully multi-tenant where completely separate organizations are logging in and utilizing the application. Two are solutions where different offices of the same organization are using the same app, but the data is separated by office and so users logging in from a given office only see their office’s data. The largest of these FileMaker multi-tenant solutions supports about 95 concurrent users.

Broadly speaking, I think that while a multi-tenant solution in FileMaker or 4th Dimension works well, they are quite challenging to develop, requiring much more than the average amount of testing to assure that a given group of users only sees the data they are supposed to see. The design phase of your project is particularly important since you must design a framework that consistently only displays data to the users based on the office, company, or division they belong to. This likely requires an internal account management system, separate from FileMaker’s accounts and privilege sets. We have a modular account management system we plug into any database where it’s needed.

Extensive And Ongoing Testing Necessary

I think at the outset of a multi-tenant project, it should be acknowledged by all involved that without extensive and ongoing testing, it is almost inevitable that a bug will eventually occur in the system that allows a user to see data that they aren’t intended to see. The degree to which this would be a catastrophic error, or merely inconvenient, can help drive the decisions regarding testing and validation.

Controlling Record Level Access

The ability to control record level access (RLA) via the FileMaker security model is the only completely secure way to restrict access to data in FileMaker by privilege set. Unfortunately, RLA comes with a performance penalty and it may or may not perform well enough in your solution. This will depend greatly on the solution’s design and how carefully RLA performance considerations are built into the solution.

A second issue with RLA is that when it is enabled, records are not hidden from the users. They are still visible, but with the text <No Access> showing instead of the actual data. This makes for an unattractive user interface and so we have always chosen to script the navigational interface rigidly so that the found set is always filtered to show only the set of records the user is allowed to see.

Implement Both RLA And Query Based System?

A difficult design decision is whether to implement both RLA and a query based system for controlling data access (as described in the paragraph above), or to implement only one or the other. Only using RLA will give you the <no access> text all over the place, but I suppose there could be solutions where that is considered ok. Solely relying on queries to display only the correct records will result in a cleaner, faster, system, but one that is inherently more likely to allow a bug to show a user data they aren’t supposed to see. Using both may lead to performance problems that are difficult to resolve. If your calculation that controls the record level access is simple, the performance impact may be minor, but a more complex calculation will have a bigger impact.

  • Are you trying to control access at the table level, or field level?

  • Are you doing complex reporting, or just simple data entry?

Careful consideration during the design phase will help make the performance impacts of RLA more manageable.

Further Considerations

Our most complex multi-tenant solution is used by school districts to manage special ed student data (individualized education plans and special ed evaluations). In this app, we are not only restricting access to records based on school district, but in some cases by staff members and in other cases by school. This multi-layered access control has really made for an application with higher maintenance and support costs.

In order to offset the risks that the complex code introduces, we have more complete testing procedures for this app than for many others. We have written regression testing documentation for this app to ensure we test each access scenario before a new deployment. User accounts are pre-defined in our dev/test environment to make it simpler to test appropriately as each user group. Thorough data change logging and script logging makes a huge difference in diagnosing issues when they do occur.

4th Dimension does not have a conditional record level security capability like RLA, so in the one multi-tenant solution we have created in 4D, we rely entirely on the query based approach. All user interfaces and reports that display data to the end users are filtered by a sub-query by a firm id that limits the record selection to just the one firm. Fortunately, 4D supports pointers and indirection more completely than FileMaker, making this relatively straightforward. I’d like to say that thorough testing has always allowed us to prevent one firm from seeing data from another firm, but in 15+ years of this app being in use by multiple firms, there have been a small number of cases where a bug has indeed allowed this to happen. Fortunately this application is in an industry where such an event doesn’t matter a great deal and quickly fixing the issue has kept everyone happy.

Leave a Reply

Your email address will not be published. Required fields are marked *