Hi @Kimberly_Nichols,
I thought quite a bit about your scenario, and I think the unfortunate truth is that Airtable might not be the best solution for your particular needs.
Airtable is a really great tool for small teams that need to organize projects or track items in a database, where each team member is able to see all the data and contribute. Unfortunately, provisioned user access is really not a strong point for Airtable. In order to get the aggregate reports you are wanting, it would, of course, be necessary for all data across all of your organizations to be located in a single base (Airtable speak for database). However, it’s not possible to give a user the ability to edit anything at all in a base without also exposing the entire base to them. Airtable does not have any sort of granular user permissions that hide data from users who can edit the database – it’s all or nothing.
It is possible to give users view only access to only a portion of a base (a view or a block), and restrict them from viewing anything else, including fields not included in the defined view… however, they would only be able to view it, and would not be able to edit. It sounds like you need your individual Organization Admins to be able to edit their Organization’s member data, so Airtable’s way of limiting access will not work for your situation.
I do have a suggestion that might work in your situation, however it may prove to require more upkeep than it is worth, when there may be other viable web-based RDBMS’s that could meet your needs (albeit at a greater cost).
Here’s my suggested work-around:
Have a separate database (base) for each Organization. The Org Admins would only have access to their own Organization’s base – they’d be able to edit data about their Org’s members, but they wouldn’t even see the other Org’s bases in their Airtable workspace.
Super Admins would have access to every Org’s base, in addition to a “Master” base, where all of the data from all the Org’s is conglomerated. The data from each Org base would, of course, have to be somehow moved into the “Master” base, and there are various solutions for making this happen.
The simplest, most straightforward way to do that would be manually - export each Org base as a CSV on a weekly, daily, hourly basis, whatever frequency is required, and upload those CSV’s into the Master base. These can be merged with existing data using Airtable’s CSV import tool and their De-duplication block, if care is taken to ensure that field names match up and record names are unique. It should be a mostly painless process, however it would be manual.
There are ways to sync all the databases to the master as well, using an automation tool like Zapier or Integromat, or with a custom app using the API and webhooks. If the sync is only going one direction (new data is only manually entered into Org bases, and then synced to the Master – new data is never manually entered into the Master to be synced to the Org bases), then this should be possible to do in a pretty painless manner as well. Integromat, in particular, can act on a record’s “Last Modified” field to sync modified data from an Org base to the Master.
I have built 2-way sync systems before, so what I describe above is definitely possible and should be able to be set up in stable and reliable manner. 2-way sync is a lot more tricky and a lot more error-prone.
I’m sure a clever engineer could come up with other ways to automate this process.
The tl;dr of this is, Airtable could meet your needs here, but not necessarily in the way you might be thinking. It will require a clever and careful setup to accomplish the user-provisioning you are wanting to achieve.
