Many to Many table for Nonprofit communications

[Kristin_Carpent]

Can you help me think through how many tables to set up for a base that helps us track our communications?

I would like to track communications by month, and we of course use several “channels” each month – FaceBook, e-mail, blog posts, newspaper articles. There are topics we might be repeating over a few months, e.g. notice of a scholarship application period that is open from January - March, so this topic might be covered for three months on several different media channels.

Ideally, I would like to be able to look at a view that shows what communications are scheduled by month, as well as what communications are planned by media channel.

Is there where I need a junction table? Or can I create a few tables that are linked?

THANKS! I really like this program but am not left-brain minded enough to be nimble at it.

[Elias_Gomez_Sai]

I think you only need one table :grinning_face_with_sweat: Then you could create Views with Groups and Filters.

[Jeremy_Oglesby]

I agree with @Elias_Gomez_Sainz on this one – your setup seems like it should work with just one table.

If you have a field for “Date” that a communication takes place, you can create another field of the type Formula and isolate the Month out of the date using this:

DATETIME_FORMAT(Date, 'MMMM')

And then you could use a Single Select field for the “Channel” options.

Finally, you can use the Grouping tool at the top and create different views that group your records in the way you need to see them. You could group first by Month, then by Channel to see all communications for December grouped together by Channel.

[Kristin_Carpent]

But that’s my dilemma — a “single select” field for the Channel options doesn’t work, it’s really a multiple select field. If we write an article about a stream habitat restoration project, for example, we want to publish it in two newspapers and a blog post. Each of those publications reaches different audiences. I would prefer not to have to list the same piece of writing three separate times in a table that’s listing our communications outreach. And as I mentioned, we run notices about some projects/programs three or four months in a row, so if I use a “single select” field for the date, I would need to list it three or four separate times.

thanks for your responses,

Kristin

[Jeremy_Oglesby]

Is there a problem with using a Multiple Select field for “Channel”? Is the issue that the same Article might go out to different channels, but on the different days?

[Kristin_Carpent]

If I use a Multiple Select field for Channel, and a Multiple Select field for Target Audience, then I can’t sort for a grid view by either one of those.

Here’s an example for the month of January:

1. article on CR Salmon Harvesters’ Roundtable, to be published in two newspapers and a blog post
2. Pick.Click.Give. appeal in e-mail and FaceBook (this will also run in February and March)
3. CRWP Scholarship application period open, to be included in e-mail, FaceBook, and newspaper notices (this will also run in February, March and April)

Three different outreach pieces, each being published in more than one format, and two for multiple months. Trying to visualize the three-dimensionality of this!

thanks,

Kristin

[Jeremy_Oglesby]

Ok, so maybe you could try this, utilizing the concept of junctions:

Have an “Articles” table. This table has a record for each article written, so you only list each article once, no repeating.

Have a “Pub’s” table, or some-such, that represents the publication of an “Article” via some Channel. Each record will link to an “Article” from the “Articles” table, will have a date of Publication (or just Month and Year Fields if that’s all you need), and then a Single Select for Channel. Thus, an “Article” may have any number of Pub’s on any number of dates and via any number of channels.

If needed, you could split “Channels” out into their own table as well, so that each “Pub” links to a “Channel”.

Either way, you are listing an Article only once, but creating a “Pub” for each instance of that Article’s publication to some channel.

[Kristin_Carpent]

Yes, thank you, that sounds like a breakthrough! Going to try it right now.

[Kristin_Carpent]

That was a huge help Jeremy, thanks again!