Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
5 years ago

Looking Up Data through Complex Relationships

Hi everyone,

I have a project management app that we use to generate client level reports.  One of our clients asked for us to include to fields in future submissions, who the general contractor is that we have hired and who the architect is.  At first glance it seems like no big deal as the information is in our database.  But I am not sure how to get it where it needs to be!  I am going to do the best to map out the relationships using < as a "has many" indicator.  Here is the table layout:

Project < Project Members > Contacts
Companies < Contacts
Projects < Opportunities < Orders

So here is where the data lies.....  On the Companies table there is a "type" field where I have listed if the company is an Architect, General Contractor, Installers, Manufacturer, Etc.  

The report my client sees is on the Orders Table

So basically I need to get the name of the company all the way to the orders table ONLY if they are a General Contractor or an Architect and I need a field on the table for each (I guess) so I can add it to a report.

Any ideas?  The relationships already exist but I am not sure if I need to dynamically create a new relationship to cut out all of the middle tables otherwise there is just too much distance between them all.....

------------------------------
Ivan Weiss
------------------------------
Relationships
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago
    Option 1)
    Lookup fields from Companies to Contacts to Project Members for the Company Name and Company Type. Then, Summary field using Combined Text for Company Name and set the filter to be based on the Company Type. Build this from Project Members to Project. Then use Lookups from Projects to Opportunities to Orders.

    Option 2)
    Build 2 relationships between Project Members and Orders - one for Architect and one for General Contractor. Build a Pipeline that runs when a Project Member is created and the Company Type on that Project Member is Architect. Pipeline will search the active Orders for the Project and update the 'Related Architect' field.

    Option 3) Build 2 relationships between Project and Project Members where Project Members is the parent. One for Architect and one for General Contractor. Set the reference fields for each to be Conditional Drop-downs based on the Project. Have your PM manually select these fields and then use those values to pull the Company name from the Project Member and then use that as Lookups down to Orders.

    And probably several other ways you could do it.

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quickbase Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      I actually gave this a lot of thought after typing it out (funny how sometimes that helps a lot) and decided to give it a go with Pipelines.....  I realized that one of the difficulties I would have is finding all of the orders related to this project member.  The common denominator was the project number (that is my key field for the Projects table).  So I was able to retrieve all of the orders attached to the project and update a manual text field on the orders table.  Maybe not as full proof as a lookup but the data never changes so I think that works well.

      ------------------------------
      Ivan Weiss
      ------------------------------