Multi-tenant record filtering
My current project involves multiple businesses working out of the same file in order to allow them to easily exchange some information (like invoicing each other), while keeping most of it separate. I am trying to keep this in the same file to allow easy updating of the system on my end.
- Each user is associated with a company
- The userID and companyID are kept in a table together and use some global fields and misc. scripts to essentially get a table that has a global field showing the current companyID
- When a record is created in any of the tables, it captures the current userID and the user's companyID
- Records should be visible only when the current user's companyID matches the companyID of the user that created the record.
- Basically, users in the same company can all see each other's records but not records in the same tables from other companies.
So far, I have determined that I need to have a TO of the table containing the gCurrentCompanyID attached to/through every relationship (which there are a LOT of) to filter appropriately - this seems a little ridiculous and I can only imagine that it will have some performance limitations.
Is there some other simpler and more efficient method I can make us of in this situation - something in FileMaker Pro 12 Advanced?