I have a table for Sales Quotation requests. It is visible by many users such as Sales Reps, Managers, Estimators, etc.
Ideally, I would like a formula (or another solution) that says if the user role is Sales Rep, the Expected Commission field (numeric percentage field) is only visible (once filled out) to the record owner.
This would prevent Sales Rep A from looking at the commission field in the quote request submitted by Sales Rep B. Sales Rep B does not want A knowing what his expected commission will be for the customer.
Other user roles will either have no access to the field or full access to the field.
For the first field, you'll want to modify your formula to look something like this:
IF(User() = [Record Owner],Insert your commissions calculation here,0)
You can then either use Form Rules to show/hide that field based on Role OR have a separate form for the Sales Rep role.
The second field would just be your standard commissions calculation. This would also be the field that you use in any reporting or other calculations your Stakeholders may need to see. To ensure that the Sales Reps role cannot access it, you would select the "Limit Access by Role" option in the properties and make sure that Sales Reps shows as None.
The commission field actually is not a calculation, but instead just an inputted percentage by the Sales Rep. They can technically type in any percentage they want; it is telling us what commission they hope to gain on the sale.
If I am to make a second field, is it possible for the second (view only field) to have a formula to populate with whatever was inputted into the original field by the Sales Rep?
You can create a formula user field that identified the "Current User" User() then create a form rule that says if current user is not equal to record owner hide the "Expected Commission Filed" you can further tailor this to only hide the field from users in the role of "Sales Rep" this would allow users in an admin role to see this fields.
Also keep in mind that you may want to limit viability of this field in reports. if you sales Reps have permission to build reports they can create reports with this filed.
Sure, this should work without you needing to create a new field.
Go to form rules and create a new rule, (this is a two part rule) first select when "The user" "Is in the role" of "Sales Rep", then select add another condition. The second condition will be when "Record Owner" "is not equal" to "the current user". Under Action select "hide" then select the filed "Expected Commission"
see attached pic. Note: please make sure your second condition is "is not equal to" in the picture it shows "is equal to" and this is wrong for your use case.
Sure, go to form rules and create a new rule, (this is a two part rule) first select when "The user" "Is in the role" of "Sales Rep", then select add another condition. The second condition will be when "Record Owner" "is not equal" to "the current user". Under Action select "hide" then select the filed "Expected Commission" (see above under answer I was able to add a picture)