Oh I missed a step. A site has many contracts and a contract has many sites. So there is a many to many table in there.
So i could relate it to my SIte/Contracts (many to many table), but I would like to just select the contract, then site.
So it sounds like your schema is:
On the Contract Site pivot table you'd perform looks ups to both the Contracts table and the Sites table. So the pivot could pull in Contract ID, Contract Name, Site ID and Site Name.
Then, on the Projects table you'd select a Related Contract a dropdown (via the "Many to One" from this side of the relationship).
Then, you could also have a relationship to the Contract Site Pivot (not to the Sites Table directly) where you select the Related Contract Site. In that field's properties, you'll see a "Reference field options" section where you can check a "Conditional values" box and set the match criteria where Projects: Related Contract equals Contract Site: Related Contract.
Next on the Related Contract Site Form Element Properties (not Field Props), you can specify a report that's used when conditionally selecting records. So in the Pivot table you'd create a Record Picker for Projects and use the lookups to display the Site ID And Site Name.
Then, once your conditional selection is made, you can use additional lookups on the Project to pull in "relooked up" info to display the Site Name and Site ID. For example, Projects::Contract Site - Site Name.
This is kinda tough to convey with a short forum response, but the key would be selecting records in the pivot table vs. the Site table. You may want to duplicate your current app (with it's data) or use a Builder Account to create a little playground to tinker around with these ideas and architecture.
Ideally, the relationship would be directly to Sites, but I'm not seeing that at the moment anyway … at least not without a Codepage. But, I'm still drinking my first cup of coffee :)
You would still end up with the Site ID though on the Project record (and the ability to "relook up" additional info from the Site via the pivot, which in the end it sounds like that is what you are after.
------------------------------
Brian Seymour
------------------------------
Yeah, I am thinking to do it I may have to link to my many to many table, versus my site table. Then bring a bunch of site details over to my pivot table, and finally to my Projects table. Or I just leave it and let the users have a longer list to search through. I wish the conditional drop down said 'This field is INCLUDED in that field' rather than Equals that field.
------------------------------
Mike Tamoush
------------------------------
I would also like if the conditional values could use 'contains' instead of being limited to 'equal to'. There is a feedback request if you feel like adding your thumbs up: https://feedback.quickbase.com/app/#/case/123591?currentProductId=3c9d7e83-da43-482e-a58c-14265037ecd0
