Formula Based Conditional Dropdowns?

  • 1.  Formula Based Conditional Dropdowns?

    Posted 06-05-2019 14:56

    In my ?Submitted Business? app, I have a form (New Business
    Submission) that has 4 multiple choice fields (the choices are from 4 separate-related tables) After you make the first choices the options on the next field are narrowed based on the selection. "Conditional Dropdowns"...These choices take the user from broad financial and insurance categories, to types, down to specific companies then products. Later in the form the user chooses the "Representative" (another related table) who is selling the product as well as up to 2 other representatives that are assisting. These are 3 separate fields generated from 3 relationships to the Representatives table.

    I now have a separate app ?Contracting? that relates or ?joins? the 5 tables in specific combinations.


    then Reps->Category->Type,

    then Reps->Category->Type->Company,

    and finally Reps->Category->Type->Company->Product


    I was able to bring over look up fields that are set up conditional to the original 4 selections to show in that particular dropdown which Rep is able to sell that Category, Type, Company, or Product. I narrowed the need down to being able to see Reps for the Business Type or the Company depending on the category chosen.

    NOW I need to be able to make it so that a user can only choose a Rep in the Primary, Secondary or Tertiary Rep lists if the Reps appear in List A or List B depending on the Category & Type selected unless the credit % is 0 then they can choose any rep.

    Currently the Related Rep Fields are not dependent on any previous selections. I don?t really want the user to have to make a selection in the Related Eligible Business Type or Related Eligible Companies fields in order to make a selection in the rep fields, I just want the rep fields to narrow based on the reps shown in the eligible lists.

    IF I were to make 2 more relationships for each of the 2 related table choices for Primary, Secondary, Tertiary choices...I need to be able to make the lists dependent based on the business type chosen.

    Ex. If Category=Securities, type is required but Company & Product are not. If Category=Accident&Health, Medicare, or Life , type, company, and product are always required. But if Category=Annuities type is always require but Company&Product depend on the type selected. Fixed & Fixed Indexed require Company & Product but Variable does not.

    So in formula thought... if Prim,Sec,Tert Rep % >0 & Category=Securities or Category=Annuities & Type=Variable choose Reps from Prim,Sec,Tert ?Related Business Type Eligibility? list.

    Then have automation or form rule fill in original rep fields based on that selection.

    But if Prim,Sec,Tert Rep% >0 & Category=Accident&Health, Medicare, Life or Category =Annuity& Type contains Fixed choose Reps from Prim, Sec, Tert ?Related Company Appointment? list.

    Then have automation or form rule fill in original rep fields based on that selection.

    ***However, if Business Credit % =0 any rep may be listed. (Choose from full agent list)*** 

    Is it possible to make all this happen somehow, through formulas or dynamic form rules or several tactics combined?