Forum Discussion

MichaelHalbrein's avatar
MichaelHalbrein
Qrew Cadet
7 years ago

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

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?

Thanks,
Scott
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.