web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Calculated & Rollu...
Microsoft Dynamics CRM (Archived)

Calculated & Rollup fields & Option sets

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I am looking for a way to link values from an option set to callculated & rollup fields

The values of the option sets should be linked to set percentages

ie.

Optionset:

Value1 = 10%

Value2=  20%

Value3 = 30%

After this i need to calculate the following

10% multiplied with numeric value field a and field b = result field c

Whats the best way to do this? Any suggestions?


BR, Rob

*This post is locked for comments

I have the same question (0)
  • Verified answer
    ThomasN Profile Picture
    ThomasN 3,190 on at

    Hi Rob, thanks for reaching out.

    I going off the idea that a user is setting the option set. If that is not a guarantee then you will want to use a workflow instead of a business rule below.

    Create a custom field as a decimal number.

    Create Business rule/workflow to populate that field with a matching decimal value from the option set.

    Create calculated field c off the custom decimal field and your fields a and b.

    Keep in mind calculated fields run at night so the user will need to manually refresh if they need more timely input. You could also not do a calculated field but perform the calculation in the business rule and set the value of field c. Calculated fields are for ever changing metrics that need to be regularly updated.

    Let me know if I missed something. Have a great day!

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Hi Tom,

    I got it working.

    I have created 4 fields

    Field 1 = Option Set

    Field 2 = whole number / simple

    Field 3 = whole number / simple

    Field 5 = whole number / simple

    Field 4 = Currency / Calculated

    1 Business rule 

    It has resulted in the following

    User selects an option . After this a business rule is being triggered to fill out Field 2. User manually fills field 3 and 4. User saves record and Field 5 shows calculation.

    I want to thank you for your quick reply and effort!

  • Community Member Profile Picture
    Community Member on at

    Hi Tom,

    I am trying to do the same without the use of a calculated field.

    But i cant seem to get the formula right through business rules.

    Formula:   Field 2 * Field 3 * Field 4/ 100

    It seems that the business rule wizard only allows a formula for 2 fields instead of the needed 3.

    I have tried to put in more actions but without the wanted result.

    What am i doing wrong?

    Business rule starts with:

    If Field 2, Field 3 and Field 4 contains data

    then

    Set Field 5 to Field 1 * Field 2

    Set Field 5 to Field 2 * Field 3

    Set Field 5 to Field 4 / 100

  • Suggested answer
    ThomasN Profile Picture
    ThomasN 3,190 on at

    I am confused then. Isn't Field 1 the option set Field 2 is the decimal value of that percentage? So in that case FORMULA: Field 2 * Field 3 would give you the same as (Field 2 * Field 3)/100.

    I think you have too many steps. If you make the transformation of the Option Set value in one business rule, you can use another business rule for the calculation.

    Depending on complexity of existing environment, I want to be careful to not over complicate things for you. But if you feel administration of this will not be too cumbersome or there is not too much else going on this may work.

    Instead of using Field 4 for everything add Field 5. Make sure fields that don't take user input are "read only".

    • IF Field 1 equals 10%
      • Set Field 2 to .10
    • ELSE IF Field 1 equals 20%
      • Set Field 2 to .20
    • ELSE IF Field 1 equals 30%
      • Set Field 2 to .30
    • END BUSINESS RULE

    New Business Rule

    • IF Field 2 AND Field 3 contains data
      • Set Field 4 to (Field 3 * Field 2)

    No division by 100 because the rule setting Field 2 handles that conversion. Thoughts?

  • Community Member Profile Picture
    Community Member on at

    Hi Tom,

    It seems i have made a mistake regarding how many fields.

    In Total there are 5 fields

    Field 1 = Optionset

    Field 2 =  Whole number

    Field 3 = Whole number

    Field 4 = Whole number

    Field 5 = Calculated

    Businessrule

    IF Field 1 equals  "Value 1" ( of option set)

    Set Field 2 to 10

    ELSE IF Field 1 equals "Value 2"

    Set Field 2 to 20

    ELSE IF Field 1 equals "Value 3"

    Set Field 2 to 30

    User story:

    Current setup:

    User selects Field 1 and selects Value 1 of the Option set

    Business rule creates input value of 10 for Field 2

    User manual fills input for Field 3 and Field 4

    After saving formula of the calculated field (Field 5) gets triggered.

    Having the following formula:

    Field 2 * Field 3 * Field 4/100 = Output for Field 5

    If i try this without the use of a calculated field then it doesnt seem to work.

    Having the following business rule in place (solely for calculation purposes)

    If Field 2 , 3 and 4 contains data then:

    Set Field 5 to Field 2 * Field 3

    Set Field 5 to Field 3 * Field 4

    Set Field 5 to Field 5 / 100

    ps. i understand why u want to use .10 cause you then dont have to divide it by 100. But .10 is a decimal number which can be misleading if you are talking about percentages instead. Field name shows % value = 10

  • ThomasN Profile Picture
    ThomasN 3,190 on at

    Hi Rob,

    I get it with that many fields and varying inputs the business rules get tricky. Typically when I do this I hide the fields I am populating from business rules since the user is seeing the percentage in the drop down. Thanks for your patience working through this. I hope it works out for you.

  • Community Member Profile Picture
    Community Member on at

    Hi Tom,

    Thanks for the advice and feedback.

    It currently works as it should with a downside that the record has to be saved to trigger the calculation.

    But at this moment this seems to be the only possible way to do so, considering the number of fields being used.

    BR, Rob

  • Community Member Profile Picture
    Community Member on at

    Conceptually I have the same requirement but with like 100 drop down fields (option sets) spread out over forked business processes.  I am dreading creating 100 integer or decimal fields and the drop downs are used in other places so they will likely have default values when this record is created.  They have this many drop downs because they were using an excel doc with lookup tables where they had a pretty text name and an integer operator referenced with =Vlookup() to calculate the overall rating with 30+ fields each with a dozen potential options.  Anyone have ideas or suggestions?  I was thinking about trying to pull the data into an excel template but it looks like unlike word the only excel reports you can pull are the list ones.  I am considering using Flow which might have an easier time with it.

    Darn it would be nice if I could just reference the value of the line in the drop down and make that value the number in which a calculated field pulled from without having to create a ton of extra hidden fields and processes. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans