Adding a many to many relationship, and having a form input data into two lists

Question

I'm building a form for a Marketing Event list. The list would track name/type/location/date etc of the event and also those people associated with it (for example recipients or attendees) These people would be pulled from an existing contacts list, selected by whoever is entering the data for the marketing event.

I want to create a separate table to join so I can create a many to many relationship (I understand this is possible through Parent/Child links?) There would be the Marketing Events table, the Contacts table, and an intermediate table that consists of a foreign key-type relationship to each.

Here's where it gets tricky; for the user entering the marketing event, I'd love for them to be able to filter contacts based on criteria (ex. a column having a certain value) then be able to have this filtered list input into the intermediate table. For example, if the filter comes up with five people that fit the criteria, add those five people to the intermediate table, using the name from contacts and the event name from marketing events.

Failing that, I'd at least like to implement something in the form to choose multiple contacts and add them to the list. Of course, this also means the form would be inputting into two lists, unless I had two seperate forms.

Hopefully this makes sense. Is this possible in Sharepoint? Is it all a pipe dream?

Answer 1

To resolve the issue of contacts filter criteria, you need custom development. But if you leave that aside, you can opt for this solution:

1) Create a list of Contacts

2) Create a list of Events

3) Add in the event list a column called Contacts with two characteristics:

A) Field of type LOOKUP

B) that accepts multiple values

Option B allows you to avoid the intermediate table.

I explain? Let me know if you have any doubt.