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:
- CONTACT
- ORGANIZATION CIRCLE
- Role (“leader”, “secretary”, “facilitator”, “delegate”, or leave blank if general member)
- Election Date (to fill out if you’re updating who’s been elected to a role)
- 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.
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.
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.
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!
