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

Question

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------------------------------

MarkShnierYou · Answer

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
------------------------------

Sign up

Login to the community