1 Organization Connected to Many Programs: Need help with Linked Table


#1

Hi. I am a very new Airtable user, so hopefully this is an easy fix… but I haven’t yet been able to make it happen. We have a CRM base happening, with many tables linked. (Orgs, Contacts, Programs, Sponsors,… and more tabs.) The Sponsors tab has a multiselect field of Programs, which are of course flushed out in the Programs tab. I want to create a new table with all Sponsors of a new Program included in it. I don’t want one line for the Program, with all the sponsors in one cell, I want a line per Sponsor, so I can track their assets, contacts, where we are in the invoicing process, etc. It will be a project management sort of table, but linking to one program.

Is this possible? And, if so, how?

THANK YOU!


#2

Sounds like you might need a separate table just for sponsors. Making each Sponsor a record. Then have a field in the table you want to link to your Sponsors list. You can select multiple sponsors.

If your sponsors data is not a primary field then you need have link the primary field of the table where your data is and then a create separate field using the lookup function for your data field.

I hope that all makes sense.


#3

Thank you!

I want a table to automatically pull Sponsors who are tagged to one Program. Is that possible? I want each Sponsor to be its own line. We have a Sponsors table already, but it is all sponsors to all programs.

I am probably being clear as mud. Thanks again!


#4

Can you clarify? If you have a Sponsors table, then each row in that table will be a different Sponsor already.

If you want a table to with rows that look like:
Sponsor A - Program 1
Sponsor A - Program 2
Sponsor A - Program 3
Sponsor B - Program 1
Sponsor B - Program 6
etc.

You will need what’s called a junction table. You’'ll want a new table with two Link to Another Record fields: one pointing at Sponsors, and one pointing at Programs. Each row in this junction table will be a unique combination of Sponsor/Program pairs.


#5

What we have is:
Sponsor A - Program 1, Program 2, Program x
Sponsor B - Program x, Program 2

AND

Program 1 - Sponsor A
Program x - Sponsor A, Sponsor B

What I want is, a table called Program 2:

Sponsor A - other fields
Sponsor B - other fields

(automatically)

…possible?

Thank you!


#6

So what might work is have a Table where your primary field is Sponsors. You have a field linked to Programs with multi-links (ie more than one program can be linked to a Sponsor.

Create a new view Then you use the group by Program. The Records would be grouped by Programs. The only down side is you will have a group if overlapping Programs ie a group for Program1 and Program2. As well as a group for Just Program1 and a group for Just Program 2


#7

do as my previous reply states and continue with the following: create a view in your junction table with a filter to only show {Program}=“Program 2”, then add all necessary Lookup fields pointing at the [Sponsor] table to get Sponsor details visible in the table.

Or the easiest method, ignore the junction table idea and just make a new view in [Sponsors] where {Program} contains “Program 2”.


#8

Thank you for your time, everyone!

I guess a view is the easiest, although it means adding lots of fields to many sponsors who don’t need them… and never will.

Cheers!