Forum Discussion

AlexGale's avatar
AlexGale
Qrew Captain
4 years ago

Embedded report of child records for parents across many-many relationship

Hi, 

I have three tables: Buildings, Opportunities, and Orders.

  • Opportunities are a parent table to Orders - each Opportunity has many Orders
  • Opportunities and Buildings are connected in a many-many relationship through a join table
  • Buildings and Orders are not directly connected at all
What I want is an embedded report on my Buildings form that shows all Orders that are under Opportunities that Building is connected to. 

Is this possible? I've been trying to figure this out for a while, and I can't wrap my head around it. 

Thank you in advance.

------------------------------
Alex
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    This can be done but you would need to push a button while sitting on a building record.

    I can briefly describe the process and if you need help you can contact me for one one one assistance.

    I will describe the simpler which assumes that there is not much concurrent use of this button.

    You will create a helper table with 1 record.  It will hold the Record ID# of the focus Building. The button will populate that field.  That gets looked up from he focus record down to buildings.

    Have a formula field [building is in focus] which = 1 if the building is in focus. Look up down to the join table.  Summary max up to Opportunities.
     Lookup own to orders.

    Then create a record of all orders which are for a focus building.

    Put that reports on the Building record.

    Have a form rule to only show the report link field if the building is in focus.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AlexGale's avatar
      AlexGale
      Qrew Captain
      4 years ago
      That makes a lot of sense, thank you. It's not ideal that the users have to click a button to see the report, but it's not a huge deal.

      ------------------------------
      Thanks,
      Alex
      ------------------------------