Need to have multiple many to many join tables in order to report related data

Question

Tables: Parking facilities; Annual Projects; Work Orders; Documents (there are more but will stick with these for this post)

Relationships:
Parking facilities- many Projects,
- many Work Orders
- many Documents
Projects - many facilities
- many work orders
- many documents
Work Orders - one Project
- many facilities
- many documents
So, we create our Projects for the year then begin our Work Orders for the Project. The Work Order record will show the related Project then select (add) each facility to be worked on through a connection many-to-many table. Also within the Work Order we add documents for items like vendor contracts outlining the statement of work and the vendor invoices for the work completed.

It is very important that each Project record show the historical work orders and documents; each facility record show the historical list of Projects, related Work Orders and related documents. I have been able to accomplish the historical work order report under Project records and Facility records but have no idea how to show that list of related documents (under the work order) for each of the related facilities to the work order.

Example: I have Project A for 2018.
Project A has two work orders: W.O.#1 and W.O.#2.
W.O.#1 is covering Garage Able, Garage Baker & Garage Charlie
W.O.#1 also has document record Contract #100 & document record Invoice #200
W.O.#2 is covering Garage Able & Garage Zebra
W.O.#2 has document record As Built plans #300 & document record Invoice #201

I need to be able to see under the record for Garage Able:
Project A for 2018 in the list of current and past project
Work Orders #1 and #2 in the list of current and past work orders
Documents #100, #200, #300 and #201 in the list of related documents (of course a document description in the list)

Of course each list item under a project or facility must be easily identified and be linked to the under lying record to obtain more detail.

I'm thinking this can be accomplished with some report links but I cannot figure how to have it work closer to a many to many to many relationship.

quickbasecoachd · Answer

Scott, before i hurt my head too much on this, are you aware that you do not need to have a relationship to have a Report Link field. You can make a report link field on any table and then all you need to do is to have any field to match on. For example for the first requirement that

Garage Able:
Project A for 2018 in the list of current and past project

On the Report Link field you will create you would be setting up a match between either the [Record ID#] of the Garage or the name of the garage with a similar field on the Work Orders. Any project info you need would come down form the Project record down to the Work Order. Does the Work order have a field on it for the garage name or the related garage?

scottgrund · Answer

I knew this about Report Links but for a different issue. I'm going to see if I can put this into action with one of my more complicated situations.&nbsp; Thanks!

scottgrund · Answer

Okay, my friend, now my head hurts! lol I've tried using report links to walk up the relationship ladder but to no avail.

Overall in this application, we are keeping track and documenting many projects each year (15 to 20). Each project may have multiple Work Orders (some with 5 to 10). Each Work Order may involve a number of assets that are being worked at one time (we are maintaining 16 parking garages and 189 parking lots) (i.e. we may have a resurfacing project for 3 parking lots going into on Work Order. And each Work Order will have multiple documents like contracts, invoices, inspections and even construction plans. Almost 100% of these documents have an attachment (usually signed scanned PDFs)

The ultimate goal is to look at each Project, Asset (garage or surface lot) and Vendor and see a complete history and data as well as status of each.

Projects need to show Work Orders, Assets and Related Documents
Assets need to show Projects, Work Orders and Related Documents
Vendors need to show Projects, Work Orders and Related Documents

If I have Work Order ID#1 with work being done in Garage Able ID#101 and Garage Baker ID#102. The Work Order itself is a large record with all kinds of fields documenting the Related Vendor, details of work to be done and the $ amounts to be spent, and the Related Project. Then we create various documents from the Work Order such as Work Contract Doc ID#300, Invoice Doc ID#301 and Work Completion inspection Doc ID#302.

It would be great to pull up Garage Able and see the history of Projects with related work orders that had Able as one of the assets in that work order as well as all the documents created from that work order, all on the same screen. Of course, the same info would show up on the Garage Baker screen as it relates to Work Order ID#1. It would be great to have the ability to create a multiple key report link. Like, from Assets to Documents send back all documents related to Work Order ID#1 and Garage ID#??? (Garage Able ID#101) thus showing Document ID#300, #301 and #302. The same report would show for Garage Baker ID#102.

Does that make sense?

quickbasecoachd · Answer

Now are the Garage(s) indicated on the Work Order? Are they in fields, like you allow up to 5 Garages per work order or is it a child table.&nbsp;&nbsp;

scottgrund · Answer

Child's table called multiple locations. So I have Multiple Locations records that I have created a report link to documents using the related work order ID# then get back the related documents for that multiple locations' record.

Forum Discussion

Need to have multiple many to many join tables in order to report related data

Related Content

How to relate two tables with the key to a third table?

Relating Multiple Records from another table

Filter out some records on related table

Building a report from multiple tables

Using Grid Edit to Edit Multiple Records at Once