Skip to main content

Set Up for Multiple Organizations

  • May 29, 2019
  • 2 replies
  • 75 views
K
Forum|alt.badge.img

Hello,

I’m trying to figure out if Airtable will work for our needs, but I feel like I’m missing something.

We have 25+ organizations that need to add/update member data. Each organization will have two Admins who can access their own organization’s members and generate reports, but they should not be able to see members from other organizations.

Additionally, we need one or two “Super Admins” who can access everything and run aggregate reports on all data from all organizations.

What’s the best way to set this up?

I’m assuming I’d need to set up one table per organization, but then I have to share via private link - which would require the Admins from the same organization to share the password. Is there no way to share blocks (charts and pivot tables) instead?

And then how would the Super Admin be able to run aggregate reports (for example, total number of members across all organizations who have a drivers licence)?

Thanks so much for your help!
Kim

2 replies

J
Forum|alt.badge.img+18

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.

    K
    Forum|alt.badge.img

    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.


    @Jeremy_Oglesby, I can’t thank you enough for taking the time to think that through and write it all out for me. I am disappointed for sure, as I would have loved to use Airtable (it’s SO beautiful!), but in the end, having the functionality we need built into the system is the priority.

    I am definitely hoping I have the opportunity to use it at some point in the future though, as it was soooo fun to play with! It’s really well built and easy to use. I was pretty excited for a while there. :cry:

    Again, thank you so much.
    Kim

      Terms & ConditionsAccessibility statement