Many to Many with creation of embedded report in one table from record in another table

  • 39
  • 1
  • Question
  • Updated 7 months ago
  • In Progress
I have two tables: User Table and Publication Table. This are linked via a many-to-many table (relationship). As, one user can have many publications and one publication can be written by many users. 

On the publication table, I enter the information for the publication that includes all of the authors. Right now, I have a user field for each author (e.g. [Author 1], [Author 2], etc) as well as a formula user list that takes all of these individual author user fields and make a single user list.  It's a duplicative effort as I'm trying to troubleshoot which is best.   

What I want to do is this: when I enter a new publication and enter the author information (users), I want the user table to create an embedded report of the publication table for each user so that when I select a user from the the user table, I can see all of their publications. 

Now, I can see doing this with a single relationship - one user to many publications. Is there a way to do this with a many-to-many relationship that I have? Or, not to make it confusing, should I create another single relationship in addition to my many-to-many?

Photo of Michael Halbreiner

Michael Halbreiner

  • 230 Points 100 badge 2x thumb

Posted 7 months ago

  • 39
  • 1
I'm trying to understand the ask.
You are saying that this is your setup

Users < Publication Authors > Publications

and in addition on the Pubs table you have say 3 User fields and a list user formula field.  So you have duplicate effort to have those 3 user fields on the pubs table and also that middle join table.

You seem to want to continue to enter the [Authors1, 2 3 etc] directly on the Pubs Table but auto create the join table records. 

Is that the ask?  If so, there is a pretty easy answer with Automations.

Basically I would set up an Automation that would trigger when a Pubs table record is added or modified or deleted and where the Authors were changed.

Step 1 would be to delete all the join table children related to that Pub.
Step 2 would be to add Author 1 child record into the join table.
Step 3 would be to add Author 2 child record into the join table.
Step 4 would be to add Author 3 child record into the join table.
Step 2 would be to delete any child join table record with a blank Author.