Forum Discussion
AlexCertificati
7 years agoQrew Cadet
I feel like you can come up with a solution without going that far. If you're willing to forego multi-select, anyway. Create multiple 'cause of LE' fields (set form rules to only display 2nd cause if 1st cause is populated, etc). Create a (hidden) formula field that's an inverse of the count of non-empty cause fields. Then have your reporting sum the weights by matching cause. Would that work?
Not sure if there's a way to keep the multi-select field and parse out the individual selections into a formula field, I've avoided working with multi-select fields for similar reasons... but I imagine not, as if there was, I guess Mark would have suggested it.
Or, if there are a fairly manageable number of different causes, you could go another route and create formula checkbox fields for each cause, using CONTAINS against the multi-select field (does CONTAINS(ToText([multi-select])) work?) to check them, another field to invert the count of the checked causes, and then sum those weights in reporting.
EDIT: Bless the 'Related Conversations', Mark has already written a formula to part out multi-select fields: https://community.quickbase.com/quickbase/topics/trying-to-count-the-number-of-selections-in-a-multi...
So you don't need to know the names of the causes to do it, then - just the maximum number of causes that might be selected. If it's, say, 5, create 5 fields to use that Part formula to store the individual causes selected, another field to invert the count of the non empty of those, and Bob's your uncle.
Not sure if there's a way to keep the multi-select field and parse out the individual selections into a formula field, I've avoided working with multi-select fields for similar reasons... but I imagine not, as if there was, I guess Mark would have suggested it.
Or, if there are a fairly manageable number of different causes, you could go another route and create formula checkbox fields for each cause, using CONTAINS against the multi-select field (does CONTAINS(ToText([multi-select])) work?) to check them, another field to invert the count of the checked causes, and then sum those weights in reporting.
EDIT: Bless the 'Related Conversations', Mark has already written a formula to part out multi-select fields: https://community.quickbase.com/quickbase/topics/trying-to-count-the-number-of-selections-in-a-multi...
So you don't need to know the names of the causes to do it, then - just the maximum number of causes that might be selected. If it's, say, 5, create 5 fields to use that Part formula to store the individual causes selected, another field to invert the count of the non empty of those, and Bob's your uncle.