Keeping Roles and Responsibilities Up-to-Date

[NYCEDU_Admin]

Here’s my situation.

I have ORGANIZATION CIRCLES (aka teams) like “board” and “online forum” that are responsible for certain domains of work.

Each circle has columns labeled with circle roles “Leader”, “Facilitator”, “Secretary”, and “Delegate”. Anyone who doesn’t have a role can still be a general “Member” of that circle.

Each of those roles can be inhabited by a CONTACT (aka person) like “Deborah” and “Rachel”. Each CONTACT can have multiple roles.

Where I get stuck is how I can use forms to update circle roles. I imagine that I’ll need another table for people to fill out with the following:

1. CONTACT
2. ORGANIZATION CIRCLE
3. Role (“leader”, “secretary”, “facilitator”, “delegate”, or leave blank if general member)
4. Election Date (to fill out if you’re updating who’s been elected to a role)
5. Resignation/Removal Date (to fill out if you’re updating who’s been removed from a role)

After this form is submitted, I’d love for some formula magic to automatically update the ORGANIZATION CIRCLE table with the correct CONTACT in each role.

1. If “Deborah” was the original “#NYCEDU Board Facilitator” but a new form comes in with “Deborah” being removed from the “#NYCEDU Board Facilitator” role, I’d like “Deborah” to be removed from the Facilitator column of ORGANIZATION CIRCLE. However, I do NOT want “Deborah” to be removed as a general member since she could still be a member without having the Facilitator role.

2. If a new form comes in with “Michael” elected as “#NYCEDU Board Facilitator”, I want “Michael” to replace “Deborah” in the Facilitator column. Again, I do NOT want “Deborah” to be removed as a general member.

3. If a new form comes in with “Deborah” removed as a member of a particular ORGANIZATION CIRCLE, I want “Deborah” to be removed from all columns associated with the ORGANIZATION CIRCLE.

Your help is greatly appreciated!

[Justin_Barrett]

This can’t be done with native Airtable forms. While the form that you proposed is possible to build from a technical standpoint, I can’t think of any “formula magic” (at least not right away) that would take the record made by the form and do the personnel shuffling you describe. If you want to keep the setup shown in your example screenshot, where each role is linked to a contact record in another table, that’s definitely not possible. A formula field can only drive its own contents. It cannot modify the contents of other fields. In that case, the fields for Leader, Secretary, etc. would need to be formulas (or possibly lookups to other tables containing formulas), so you’d be left with plain text for the name. If you’re okay with that, there might be a way to do it, but as I said above, the method to pull it off isn’t coming to me very quickly, and would take some significant time to test.

Am I correct in assuming that you want a form-based solution because you want to allow non-Airtable users to make the relevant changes? If that’s not the case, and you and/or other Airtable users will be doing the updates, it feels like driving the system with a form is overkill. Just edit the records directly. Or do you want to keep a running history of when people were elected to (or removed from) a given role?

[NYCEDU_Admin]

Justin_Barrett:

Am I correct in assuming that you want a form-based solution because you want to allow non-Airtable users to make the relevant changes? If that’s not the case, and you and/or other Airtable users will be doing the updates, it feels like driving the system with a form is overkill. Just edit the records directly. Or do you want to keep a running history of when people were elected to (or removed from) a given role?

That’s a yes!

What happens if I simplify things and track ONLY circle membership?

My survey now asks

1. CONTACT
2. ORGANIZATIONAL CIRCLE
3. Choose from “Elected” “Resigned” “Removed”
4. Date

So now I’ve linked my ORGANIZATIONAL CIRCLE to any associated CIRCLE MEMBERSHIP SURVEYs

Is there some way to take the data from that linked field and filter it so that one of the columns

1. Finds the most recent record associated with the same CONTACT
2. Displays that record if it’s one where status is ‘elected’
   ?

[Justin_Barrett]

Perhaps. I’d need to see a more complete view of your table setup to know if it’s possible to pull that off. As it stands now I’m not certain that I understand how everything is organized.

[NYCEDU_Admin]

CIRCLES

Airtable

All Circles - Airtable

Explore the "All Circles" view on Airtable.

MEMBERSHIP SURVEYS

Airtable

Public Circle Membership Surveys - Airtable

Explore the "Public Circle Membership Surveys" view on Airtable.

[Justin_Barrett]

Thanks for the links. Unfortunately this setup looks to be deeper than I’ve got time to investigate at the moment. If someone else can jump in and assist, that would be greatly appreciated.

[Kamille_Parks]

I think I have a solution:

Airtable

NYCEDU_Admin - Airtable

Explore the "NYCEDU_Admin" base on Airtable.

The base above uses 20 extra fields in order for the [Organization Circles] table to always show the current Leader, Facilitator, etc. I filled the base with some dummy values so you can see that the Leader has been replaced in the column automatically, but not removed from the {All Members} field.

A caveat is the fields are formulas and not lookups, meaning you won’t be able to click the name to expand the Leader’s record.

[NYCEDU_Admin]

Thank you, Kamille! This is very helpful and I will continue exploring it.