Forum Discussion

NirajShah4's avatar
NirajShah4
Qrew Cadet
6 years ago

Conditional Dropdowns Dependent on Multiple Many to Many Relationships

I am attempting to solve for something which I believe can be solved natively. 

I'm looking to pull in information for "Rates" for a product based on a selection within a Category, Department, Service, Assignment, and Pricing Level. 

The issue I am coming up with is that all the factors that make up the Rate (Category, Department, Service, Assignment, Pricing Level) are all many to many such that a Category can have many Departments (and vice versa), Departments can have many Services (and vice versa), etc. However, once the unique combination of Category, Department, Service, Assignment, and Pricing Level is selected the appropriate Rate would show up.

Is there a way to make this functionality work via conditional dropdowns either by separating all components of the Rate into separate tables or another fashion?
APIs and custom code
Forms
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Niraj, the best solution would probably be to change the Key field of the pricing rates table to be a text concatenated field in the format obtained from the formula field of

    List(":", [Category], [Department], [Service], [Assignment], [Pricing Level])

    Then once you have that setup copy the values from the formula field to a data entry text text field using grid edit, and then set the key field of the prices table to be that text field.  Maintain that going forward with a form rule which ensures that the text Key field is equal to the formula field.  Make sure the formula field is one the form, even if it's hidden by a form rule so that the form rule can see the most update to date calculation of the formula.

    Then on the Products record, make that same concatenation formula and lookup the Rates, in a Relationship  and call the rate like [Rate(lookup)] and then make a snap shot field called [Rate] which will snapshot (freeze) that value so that your historical product records do not change retroactively.


    • NirajShah4's avatar
      NirajShah4
      Qrew Cadet
      6 years ago
      Thanks for the call and feedback, Mark! This worked splendidly.