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...
Forum Discussion
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/
------------------------------
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/
------------------------------
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
------------------------------
------------------------------
Ivan Weiss
------------------------------