Forum Discussion

MikeTamoush's avatar
MikeTamoush
Qrew Commander
2 years ago

Creative work around for conditional dropdown conundrum?

I have a table of Sites. Each site has many contracts. 

Flash forward to a table of Projects. On my project I want to select a contract, and I want the sites to be shown conditionally, so that the site shown includes the contract selected (so typically, the conditional drop the 2 fields must equal each other). 

Example:
Site A is associated with Contract 1 and Contract 2 (children).
Site B is associated with Contract 1 (child).

On the project, select Contract 2. In the Site dropdown, only Site A is shown.



------------------------------
Mike Tamoush
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    You should be able to set the Condition that after selecting Related Site of the Contract 
    only show Sites where Record ID matches. 



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      2 years ago

      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.



      ------------------------------
      Mike Tamoush
      ------------------------------
      • BrianSeymour's avatar
        BrianSeymour
        Qrew Assistant Captain
        2 years ago

        So it sounds like your schema is:

        • One to Many: Contracts to Projects
        • Many to Many: Sites to Contract


        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
        ------------------------------