Database design problem of success
I've made a solution for a client that is hosted by a FileMaker server company and accessed via Go on iPads. After getting it working for location 1 the client opened a second facility at short notice that had the same functional requirement but little if any shared data. The easiest way forward at the time was to clone the solution and run it in parallel for the two locations. The maintenance when required is tediously repetitive, but it works.
Now a third facility is planned, and it's getting silly, not least because we'd be paying to host three solutions where it should only be one. But what is the best way to combine three data sets into one? There is no need for the three locations to share data, indeed it's undesirable that one should be able to see the data from the other two. But I'm reluctant to rely on using security measures because I have had serious problems using elaborate security in FileMaker Go.
I need a technique to filter the data as a user logs in and keep it filtered. My best thought is to use the Account Name to set a value for the location and make relationships that depend on that value for all the data tables. The downside of this is that all the layouts would have to be rewritten, with the layout pointing to the table that holds the location value and all the data displayed in a portal on the layout. But some layouts already use portals and I assume you can't have a portal in a portal (or can you? I've never tried...).
Is there an easier method that I've overlooked?
Thanks in advance.