Many to Many table for Nonprofit communications

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

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.

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

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?

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

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.

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

That was a huge help Jeremy, thanks again!