How can I create a report to display related records in a Many-to-Many relationship?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have 5 tables:

  • Modules
  • Concepts
  • SLOs
  • SLOs + Concepts
  • Modules + SLOs

We have a product organized by modules (Modules), and has student learning objectives (SLOs). Modules are assigned multiple SLOs, but SLOs can also be used in multiple Modules (Modules + SLOs).

We also assign taxonomy concepts (Concepts) to the SLOs, and many Concepts can be assigned to many SLOs (SLOs + Concepts).

If a SLO is assigned to a Module, I want to know what Concepts are assigned by association. In other words, a report displaying all the Concepts of the SLOs assigned to Module 6.

I built yet another table to manually create this relationship between 'SLOs + Concepts' and 'Modules + SLOs', but this is very labor-intensive and...stupid. It feels as though, if QB knows that a SLO is assigned to a Module, and these Concepts are associated with that SLO, it should know what Concepts are specific to that Module.

Photo of Jared


  • 0 Points

Posted 3 years ago

  • 0
  • 1
I know exactly what you are trying to do and can set this up for you, using all native QuickBase, but the setup is too complicated to explain every step here.

Basically what would happen is that you would sit on a Module and press a button.  That would record for the current user their Focus Module Record ID# in a table of Users where the Key field is user.  The user would be automatically edited or created when the button is pushed.

Through a relationship based on Current User formula of User() on the Module, we essentially shine a light on the the single Module and a numeric field becomes = 1.   That light shines though to the Module-SLO Join table by a lookup field and then summarizes up to the SLO. Now the SLO is lit up.  That light is then looked up down to the Concept-SLO join table and then summarized back up to the Concept.   Now the concepts(s) are lit up.

Then the Module record is refreshed during the button push. and we show an embedded table of all lit up Concepts on the Module record using a dummy report link field of 1  and a report filter to just show Concepts with are "lit up"

Contact me off line information in my profile if you want me to set this up for you.