Looking Up Data through Complex Relationships

Question

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 > ContactsCompanies < ContactsProjects < Opportunities < OrdersSo 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 TableSo 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------------------------------

BlakeHarrison · Answer

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 Harrisonbharrison@datablender.ioDataBlender - Quickbase Solution ProviderAtlanta GA404.800.1702 / http://datablender.io/------------------------------

Sign up

Login to the community