Making a calculation from an option set field

(1) Share

Report

Posted on by Community Member

Hello,

I wonder if any of you kind people may be able to point me in the right direction for something that will save me huge amounts of time when exporting data and having to inset manual calculations.

In my opportunity I have 'close percentage' which is an option set with a small handful of values (10, 20, 30, 50, 70, 90). I then have a field for revenue. I want to create a new field to hold 'weighted value' i.e. the opportunity value divided by the close percentage so I have the weighting.

The problem I am having is if I create a new field of 'weighted value' I cant do a calculation against the close percentage as it is an 'option set' and not a number field.

Is there a way to either calculation via the value in the option set or happy for a workaround like creating a new hidden numerical field for close percent that the option set value can be loaded into so I can then do the calculation from there?

I know the other solution is to make a new field for 'close percentage' that is just a numerical but the problem is users then have a tendency to put any percentage number they want in rather than the set values above that we use as a standard to work to.

Appreciate any suggestions on how I may be able to use the already held option set value to get a calculation made.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Radu Chiribelea 6,667 on at

Like (0)

Report

Hello,

You can create a Plug-in or a JavaScript function that does this. Within the code library you can create a dictionary collection that stores the OptionSet Value and the corresponding factor value and then do the calculations based on that.

If you are not very comfortable with writing code - you could see if what you are after could be implemented with Business Rules. (I personally haven't tested this scenario so i can't tell if this will work or not for your requirements.)

Hope this help you get started,

Radu

Was this reply helpful? Yes No
Suggested answer

Kokulan 18,054 on at

Like (2)

Report

Hi

Please see the following for No Code solution

You can create a backing field to hold your close percentage option set value - whole number or decimal.

You then have to create a Business Rule to set the value to this backing field from your option set

You can create your Weighted Value field as a Calculated field and add the formula to that so that it can dynamically calculate values.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Hello, thanks for your response. I did try that before with no luck but maybe I am missing something.

I created a new business rule in opportunity to set the value of the new weighted field (A New Weight RC) to the value held in the 'Close Percentage' option set field but unfortunately the 'Close Percentage' field isn't in the pick list. I had assumed because my new field is a numerical and the close percentage is an option set and for that reason as they aren't the same field type it wont allow you to populate one from the other.

As per picture below, the drop-down highlighted yellow contains all of my fields in my opportunity entity but not the 'Close Percentage'

Thanks

Was this reply helpful? Yes No
Suggested answer

Kokulan 18,054 on at

Like (1)

Report

Hi

In your BR, you will have to set the value manually by checking the option set value.

If the Option Set value is 10, then you can set value 10 to the Close Percentage backing field

else if Option Set value is 20, then set value 20

You wont be able to directly assign value from OptionSet to number

Hope this helps

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Thanks Kokulan. Doesn't sound like using a BR will work then because an opportunity may be open a few months and during its life the 'close percentage' will change value often stating low at 10% and going up to 90% as we reach a deal so I cant manually set the value due to them often changing in the option set close percentage field. Sounds like the only way may be using some JS as was suggested earlier.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Radu, thanks for your reply. While my CRM config skills are basic (I have had to take over after our in house developer left), as a software house, we do have JavaScript developers albeit they have no CRM knowledge. If I can get one of our developers to write the snippet of JavaScript to do this, could you point me to where in the config tools I would need to set it up as I have only worked with business Rules/Processes before for doing tasks, not JS, thanks

Was this reply helpful? Yes No
Suggested answer

Kokulan 18,054 on at

Like (0)

Report

You can set the scope of the business rule to Entity, once you set like this, it will apply the rule at the server level not from form.

Was this reply helpful? Yes No
Suggested answer

Radu Chiribelea 6,667 on at

Like (0)

Report

Hi

Please find below some links which could help you get started (these are valid for CRM 9.0 or later - as the client side model has changed with this build)

docs.microsoft.com/.../walkthrough-write-your-first-client-script

docs.microsoft.com/.../reference

docs.microsoft.com/.../client-scripting

docs.microsoft.com/.../use-javascript

Hope this helps,

Radu

Was this reply helpful? Yes No
Suggested answer

Hammed 366 User Group Leader on at

Like (0)

Report

I was able to achieve this by simply using the classic workflow to update the value of the field when that field(optionset) contains data. This is done without written any code.

Was this reply helpful? Yes No