Performance problems with record access based on calculation
Hi everyone, I have a huge issue with poor performance when using custom record level access based on a calculation in a multi-tennant type solution.
We have a database with about 100K records which two offices use over WAN. Each office should only see its own records, so we created a field ENTITY_OWNER which has either "office1" or "office2" text string in it, depending on who owns the record.
We then set record level access to custom calculation so that a user is granted access when ENTITY_OWNER = his office.
When accessing a list layout using the admin account which has no access rules, the performance is very good over WAN. When logging in with a user that has these customs access rights, it takes about 20 minutes to load up the list. I have tried to use both a global field in the calculation, set to the correct office name and a separate security profile for each office, which has a calculation using the office name as a fixed string. I can't get decent performance.
I am not too surprised, I guess the database has to a calculation of each and every 100K record for each access to determined if the user has access rights.
Is multi tenant, record level access impossible in Filemaker due to these issues. Is there a smart way to achieve the same thing, that a user only sees his office records? In this case, both are the same company, so security does not have to be super tight, it is more about the use case, not to see records that are not relevant.
One solution could be to have full access to all records for each user but create filter buttons in the user interface which filter the data correctly, or filter with a script on layout entry, but users will be confused if they use the "show all records" button. Another idea I am toying with is to change all views to portals and trough a relationship filter out the right records. But I don't like to overuse portals and also they have their own performance issues with subsequent filtering etc.
Any other ideas out there? I also I am not a fan of the "no access" message and if we could find a solution to now showing it, would be great to.
Greatful for help!