Dec 29, 2019 06:48 PM
Context: CRM probability assignment to opportunity. Complex value chain, multiple stakeholders resulting in 2 variables (dealtype & stage) required to calc probability. Both variables are single-select fields.
I’m desperately trying to avoid nested-ifs as its a recipe for a maintenance disaster. I tried nesting Swtich() - no luck.
I’ve imported and created this table, and even created separate table with rows [dealtype#-stage#, % ]. Neither seem to work as I cannot select a formula field to lookup off probability table.
Any guidance on how to solve this highly appreciated.
Dec 30, 2019 07:48 AM
Hi there, this might help point you in the right direction… though it’s not perfect.
The matrix is defined on the [Probability Matrix] table. All of those records are linked to a single record on the [Junction] table. In turn, all of the records on the [Opportunities] table must also be linked to the same record on the [Junction] table to get this to work properly.
The [Junction] table contains a rollup field which puts all possible combinations of {Deal Type} and {Stage} into an array. Then, on the [Opportunities] table, that array is parsed via a MID()
formula to find a matching {Deal Type} and {Stage}, and return the associated percentage.
Hope this is helpful to get you started! :slightly_smiling_face:
Dec 30, 2019 03:33 PM
Great, thank you!! This seems like a far more elegant solution compared to my 31 line (!!) function of nested-IFs clustered within a SWITCH function’s options. Will try it and revert.
Regardless, you’ve definitely moved my solutions-toolbox forward a couple of notches. Thank you!