reporting on tables with a many to many relationship

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

I have a table (Contacts) that is related to a Regions table (one contact to many regions). And the contacts table is related to a table called SIC Codes (one contact to many SIC codes)

I'm trying to create a report that will display contacts, region and SIC.

Right now I can only see the summary lookup values.

Any thoughts on how to build a report displaying all of the above info?

Photo of Scott

Scott

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of QB_Support_Brian

QB_Support_Brian

  • 40 Points
Hi Brian,

While you can view a specific Region record and see all the related Contacts or vice versa, unfortunately there's currently a way using native QuickBase to display the information in a report view. You might be able to do this with a custom script, however. Have any developers here put something like this together before?



Thanks,

Brian
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
You have a many to many relationship between SIC Codes and Regions with Contacts as the join table:

SIC Codes >- Contacts -< Regions

Using the report builder you can query the Contacts table and include related fields from the parent SIC Code and Contacts table. To include related fields from the parent tables choose the last option in the "Columns to display" listbox labeled "<Columns from a related table>" and proceed to select the parent table and field you want to include in the report.
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Any way to do this with charts? (which do not have the option to pull from a related table, as far as I can tell.)

My situation is even more complicated, I have two intermediary tables and I need to run charts based on the information in the child table (and the top-most parent table), here are my tables:

Opportunity Table is parent to RFP table and IO table
RFP Table and IO Table are parents to the Line Items Table
*You can only create an IO when an RFP has been created

I need to run charts on the line items table but relate them to the Opportunity table fields (like status, etc.) And since the RFPs come before IOs, I cannot run from the IO table (which has RFP information) because if there is no IO the RFP info won't show up. So I will need to run the report/chart based on Opp. values related back to the RFP table.

I've tried to bring in the RFP-Related Opportunity Info and the IO-Related Opportunity Info to the Line Items form, but they both don't always show up! The IO-related info always does, but the RFP is hit or miss and that is the one that is most needed! (typical:)

Any help is most appreciated. Thanks!