One Contact, Multiple Roles, Different Status Per Role

[Nikiska]

Help! We are a Charity that trains Assistance Dogs for Autistic Families. I have a table with ALL the people that have ever had to do with thecontact/dealings with charity at some point... however, this is my dilemma:
Mr X is currently a 'Volunteer' and a 'Subscriber' but he used to be a 'Fostererer' so his Contact Type is currently a multiple select field where all three are listed. BUT it doesn't tell me which Type is active and I have been asked to show who's doing what currently and not previously. What do I need to do to my base to identify that Mr X has 'Active' status on both Volunteer and Subscriber but 'Inactive' status for Fostererer.

[ScottWorld · Accepted solution]

The only way that you can do this is to restructure your database as a “many-to-many relationship”, which is a more complicated type of relationship that requires 3 tables (instead of 2 tables) for your record linking.

You can read more about many-to-many relationships in Airtable’s support document here.

I also give a demonstration of many-to-many relationships on this Airtable podcast episode.

p.s. If you need to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consultant — ScottWorld

[seferiannie]

Split your users as Active and Inactive status. and each status can have many titles (example fosterer, trainer, , volunteer, subscriber.." 

if your business structure is that you reset at a specific time (example annually), you can create a tick field for the years & one for status (or multiselect) & then create a view for your year- this helps for me to choose the perks per year (example last year we gave perk X but this is not valid for this year, without losing the perk data)

[Nikiska]

Hi @ScottWorld , Many many thanks for your advise above. I have restructured my base to accommodate your suggestions and all the linked records are working perfectly. However, it only works 'backwards'. By that I mean, if I create a new contact in my master People list and use the newly created linked field to select what kind of contact/contacts they are ie. Mrs Y is a 'Fundraiser' and also a 'Volunteer' - it does not create invidual records in my junction table where I am able to select their status - ie active/inactive/potential etc etc. But, it would be really cool if in my new human form i can actually select the various types of contacts and what their status is respectively but I can only do that in my junction table AFTER a new human has been created. x

[ScottWorld]

You’re welcome!

If you’re doing the data entry from within Airtable as a logged in user, you can create brand new records in other tables without leaving the existing table that you’re working in.

However, if you’re doing data entry using a form, Airtable won’t let you add new records in other tables through a form. One workaround for this is to use Fillout’s advanced forms for Airtable, which not only gives you this capability, but also gives you about 100 other advanced capabilities that Airtable’s native forms don’t offer. It’s free, too!

[Nikiska]

Hi @ScottWorld , you are incredibly helpful.
One last thing - I am tyring to transpose my Linked Record with Multiple Humans in one cell to individual rows/records in a new table... I know there's lots of scripts flying around but none seem to work for my fields (very very very sad and frustrated face!!).
This is what I have
Primary Field                    Linked Records Field/Cell
Volunteer                           Mr X      Mrs Y    Ms Z
Supporter                          Mr X      Ms Z

I need it to be turned into a new table thus:
Primary Field                     Type
Mr X                                   Volunteer
Mr X                                   Supporter
Ms Z                                   Volunteer
Ms Z                                   Supporter
Mrs Y                                  Volunteer

[ScottWorld]

The junction assistant extension that @Kamille_Parks created might be able to help you do that quickly.

[Nikiska]

Thank you @ScottWorld , however I managed to create them through an automation using the repeat for each group in option. 🙂