Chart building on multiple level relationships with more than 3 tables

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
I have two intermediary tables between my parent and child table that collect and specify the child table information. 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 (cost values) 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 table, 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!
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb

Posted 5 years ago

  • 0
  • 1
Photo of QB_Support_Brian

QB_Support_Brian

  • 40 Points
Hi Leanne,



Just as with table reports, you can use any data available on the table to build a chart. What this often means for chains of several tables is you build the chart on the bottommost child table. This is the one place where you have access to data from all tables in the chain. One common issue is that lookup fields need to be explicitly added at each link in the chain, to pass the information all the way down.

If you need more guidance on this, feel free to go to Help > Manage Support Cases > + New Support Case so a customer care specialist can take a look at your setup.



Thanks,

Brian
Photo of Leanne

Leanne

  • 354 Points 250 badge 2x thumb
Hi Brian, thanks for the response. I took the steps of moving my lookup fields down from the master parent table (grandparent I guess) through the intermediaries but the issue was that both RFP table and IO table pulled down the same fields into the Line Items table thereby creating two of the same field on each record: one blank, one full.

My remedy was to create formula fields that took the value from which ever field was not blank (for example, when an RFP value was used, the grandparent table name would be included from the RFP table, and it would be blank in the lookup from the IO table) and created a Line Item master field to create reports from.