Formulas: Scenario 2 Solution

The solution section provides an overview of how to solve this scenario, and why that solution was chosen.  The Solution Steps section immediately following outlines the exact steps used to replicate the solution.


To view the remainder of this content, you must purchase the Certified Administrator Study Guide.  Please Login or purchase the study guide.

35 Responses to “Formulas: Scenario 2 Solution”

  1. August 19, 2017 at 10:25 pm #

    Since we are using a formula to decide whether to display the field, does that mean that sales users can see others’ commissions via Reports?

    • JohnCoppedge August 25, 2017 at 3:22 pm #

      If you apply field level security to the appropriate field (the non-formula field), then this will carry over in reporting (they will not be able to see commissions in reports)

  2. nitzan.marinov March 2, 2017 at 11:48 am #

    Just a small comment wrt to this: “Note: a null result will display an empty field. A result of 0 will display $0.00, implying that the commission was zero instead of simply hidden.”
    This will only work if one chooses the display option for Blank Field Handling as “Treat blank fields as blanks” and not zeros.

  3. Kjannati September 13, 2016 at 5:03 pm #

    Thanks John – got there in the end!

    A couple of questions – the OWD had to be set private no? Also, if you hide the other original commission field, how could you later on edit or add to the commission field?

    • JohnCoppedge March 13, 2017 at 3:35 pm #

      OWD does not impact field level security – field security is driven through profiles and permission sets… you might want to revisit the premise of the scenario to makes this is clear

  4. RomanPer July 26, 2016 at 9:48 pm #

    It’s weird that it actually works. By definition, only users with profiles that have access to the original field can see the value of that field on the screens and reports. The formula will run in the context of the user currently logged in. So, even though the user is the owner of a specific opportunity, he/she still shouldn’t be able to see a value from a field that he/she doesn’t have permission to see. I understand that it gives you an option to implement conditional read permissions, but it looks like a potential security hole

    • JohnCoppedge August 18, 2016 at 6:34 pm #

      That’s a very reasonable assessment and definitely something to consider in your architecture design. You’ll see similar considerations that need to be evaluated when implement custom code as well.

      • mattl October 6, 2017 at 10:23 am #

        John can you elaborate on why this would be a security hole as my understanding is that the only risk is that sales reps see each others commissions – without administrator rights to use overiding formulas they wouldn’t be able to do this not so?

  5. gauravkj April 21, 2016 at 5:12 pm #

    Hi – How we are going to implement a scenario where the managers need to see their reports commission?

  6. November 13, 2015 at 2:45 am #

    Is it possible to achieve this by using just one custom field? How about using one Commission field and have formula to refer to oppty cost * some percentage to calculate commission and use that in the formula?

    • jrbarretoi November 25, 2015 at 1:12 am #

      Hi Dey, I used this and worked fine with only ONE Formula field. That would have been my approach.

      IF ($User.Id = Owner.Id, Amount * 0.2 , null)

      I guess the “formula” field is not entirely appropiate, since it cannot be manually changed by sales ops as John states: “commission field gets entered by sales operations or by workflow rule”

      Anyhow, I believe that most of the times, commisions are calculated instead of manually populated.


      • JohnCoppedge December 3, 2015 at 12:08 am #

        Spot on – just depends on the scenario

        • mattl October 6, 2017 at 10:13 am #

          On this point is there a specific reason why we created two custom commission fields then? We should always be just creating commission and deal value fields and using a formula to get the result?

  7. sampada.deshpande November 12, 2015 at 5:35 pm #

    Hi John,
    This was a tricky scenario!!
    I set the OWD for Opportunities to Public Read Only. Is that correct?

    The “Sales reps must have the ability to view opportunities owned by other users (without being able to view the commission on that opportunity)”.

    • JohnCoppedge December 3, 2015 at 12:07 am #

      The org wide default will allow the users to view all opportunities, however that won’t take care of the field permissions…

  8. October 13, 2015 at 11:28 pm #

    I am getting an error with the formula. The system indicates return the values in “text” the field is selected as formula currency 2 decimals?

    If I set to text the field will display the formula instead of the results?

    • JohnCoppedge October 14, 2015 at 10:30 am #

      If it is set to text then you will need to convert the numeric value to text in order for the formula to work corrected – I believe the TEXT() function will do that.

  9. nishsfdc2015 July 8, 2015 at 9:16 pm #

    Hi John,

    Doing this by just one field commision and having this formula:

    IF( OwnerId = $User.Id , IF( TEXT(StageName) = “Closed Won”, Amount * 0.15 , Amount * 0) , null)

    Will first check if the owner is current user and then check if commision need to be 0 or some amount based on opportunity won or not. Just an opinion.

    • JohnCoppedge July 10, 2015 at 7:35 pm #

      Yeah that can work if your commission can be calculated in a formula, for sure.

      • Viday August 14, 2015 at 1:41 pm #

        Hi John, awesome explanation of the syntax, very kind of you. I have tested the commission field with my test user and it works fine. The commission value is blank for test user but populated for sys admin.

  10. June 11, 2015 at 4:00 am #

    Frankly, this scenario sucks as you don’t tell us that the commission field is populated “by workflow” or API in the scenario. It’s the only way to hide the input field. Unless you change the ORG defaults and create sharing rules. One would assume that someone has to populate the commission amount and that the field would have to be on the page layout.

    If you did tell us then it would have been very interesting challenge,

    • JohnCoppedge June 11, 2015 at 8:05 pm #

      I think the scenario makes sense:

      “Currently, the value is manually entered by the sales admin when a deal is won, as the commission matrix is quite complex and reflects variables not currently captured in Salesforce.

      The sales reps should be able to view the commission amount on opportunities that they own, but not opportunities owned by other users. ”

      “by the sales admin” being the key in the first sentence, and “view” being the key word on the second sentence (the sales user is not inputting their commission – just viewing; the sales admin would update the actual commission field).

      It is a tricky scenario though, I can see how it would be hard to arrive at the solution.

  11. Maura McNulty April 14, 2015 at 6:12 am #

    Hi John,
    We do need to call the fields individual names, the both show up as new fields on the opportunity page layout. So “my commission” gets added to the Opportunitiy Page Layout, correct? And does it go the the Opportunity Page Layout or the Opportunity (Sales) Layout? I’m taking my test on Thursday, I appreciate your answers to all questions posted this month.
    Thanks, Maura

    • JohnCoppedge April 14, 2015 at 11:33 pm #

      By using field level security, you can have both fields present on any/all of the page layouts. The field level visibility will take care of only showing the field to those that have access.

  12. Cloud Force April 1, 2015 at 12:55 pm #

    Hi John,
    I think it is required to add a field level security for Sales profile (Inside Sales Team, etc.) for the new commission formula field. This step needs to be added under the heading – Create the Commission Formula field:


    • JohnCoppedge April 14, 2015 at 11:32 pm #

      Remove field-level security for the system administrator profile (this will prevent the field from being displayed twice).

      ^^ By only removing the system admin, the rest of the profiles should be selected, and the permissions should be set up correctly.

  13. Munish Sikka March 27, 2015 at 6:04 pm #

    What do you mean by “Remove field-level security for the system administrator profile (this will prevent the field from being displayed twice).”? It’s unclear what the FLS setting should be for the Formula field.

    • JohnCoppedge March 30, 2015 at 3:02 pm #

      Munish – you have two commission fields:

      1. Commission: this is the raw calculation. This is only displayed for admin types.
      2. My Commission (or equiv): this only displays a commission value to the opportunity owner. For other users the field is blank.

      The second field can be set to hidden for the admin profiles, so that they do not see two commission fields.

      The first field would be set to hidden for non-admins, so that they cannot see the value for all commissions.

  14. Sue Stevens October 30, 2014 at 4:47 pm #

    I named the formula field “My Commission”. I think that way it’s clearer what the intent is, e.g. if used in reporting. I also left it displayed for system admins, which makes for easier testing.

  15. Sameer Soneja July 30, 2014 at 11:57 pm #

    Since Opportunity is Private security type, users cannot see opportunities created by others.
    So if we create a regular field , with R/O privileges to Sales users and Visible / Editable to Admin, doesnt that solve the purpose.

    Am I missing something

    • JohnCoppedge July 31, 2014 at 3:37 pm #

      From the scenario: The sales reps should be able to view the commission amount on opportunities that they own, but not opportunities owned by other users.

      If you made the assumption that sales reps would ONLY be able to view opportunities that they owned, you could use OWD (private) to make this the case. I’ve updated the scenario to make this more clear that this is not the case – sales users may be able to see other opportunities (that they do not own).

  16. Steven van Noort April 7, 2014 at 2:45 pm #

    I think the formula is incomplete because there is no test to confirm the opportunity was won. Perhaps the following would be more accurate:

    IF(AND( IsClosed , Owner.Id == $User.Id ), Commission__c ,null)

    • JohnCoppedge April 11, 2014 at 4:16 pm #

      The idea here is that the commission field would only be displayed to the current user – how that field gets populated isn’t baked into this example, but I understand your point. The assumption would be that the commission field gets entered by sales operations or by workflow rule.

Leave a Reply