Forum Discussion
Setting View Access Control on SharePoint List
Hi all - I am advocating users in my organisation to use SharePoint for sales management and data tracking (instead of Excel), have build up a list using SharePoint and encounter following business compliance requirements, would appreciate for your input and suggestion on how to handle this:
My organisation sales team is structured as such:
- Team 1 (London)
- Team 2 (Birmingham)
- Team 3 (Liverpool)
- Team 4 (Edinburgh)
Currently I have created a client tracker in such format in the SharePoint list:
Client Name | Team | Sales Rep Name | Revenue | Aging | Remarks
Where our business compliance have such requirements:
- Ideally this list will be maintained by sales rep themselves (if not it will be limited to 1 rep + 1 team lead within the sales team) where they can create a new item and input the client name and details
- Sales rep should not be able to view client name by other teams, but it is ok for the sales rep to view items created by other sales rep within the same team
- However for the Head of Sales should be able to view items created by all sales teams
Questions:
1. Is above business requirement possible to be done by SharePoint? Due to enterprise environment we can only look at JSON or PowerApps, no external apps or add ons shall be installed. What are the possible solutions if not workarounds available? I have thought of creating different views for each sales team however believe it is still possible for them to view other team's entries by switching the views in SharePoint list
2. For the aging computation, possible to set it to business days only?
Appreciate for all the advise and comments so that I can educate the business users better, thanks!
nivota with views it is possible for users to change the view so they could see the other teams' clients. This is because there is no way in a modern SharePoint list to use audience targeting on a view. It is possible if you switch back to classic view to do this and ganeshsanap who is on this forum regularly wrote a blog post about it a while back. But what I would do (other/better solutions are no doubt available) is to use 4 separate lists, one for each team - you can create a list from an existing list to save time. Then I would create 4 different permissions groups in advanced permissions settings, again one for each team and add the team members into each group. Then for each list go to the List settings -> Permissions for this list. Click on the stop inheriting permissions. Then for that list leave just the Owners group and appropriate team group for that list and give the team contribute permissions.. Make sure you add the Head of Sales to the Owners group.
So with this method there is no risk that a rep can just change the view and it makes it straightforward for each team to go to "their" list. The Head of Sales could see all the items in each list because s/he is in the Owners group. But if they wanted to view all the items together in a single list then I would build a flow in Power Automate set to run once a day at, say, 2am, to add all the items created the previous day into the full list. Make sure that only Site Owners have permissions on this list.
For the aging formula, you can't use Today or Now because a calculated column won't update dynamically based on the change in the date. The calculated column for an item only updates when an item is created, edited or the formula changes. So what I do in these situations is to have a column for Today and then a simple flow to update it every day.
This is my SharePoint list:
The formula for the Workdays calculated column is:
=IF(ISERROR(DATEDIF(Start,Today,"d")),"",(DATEDIF(Start,Today,"d"))+1-INT(DATEDIF(Start,Today,"d")/7)*2-IF((WEEKDAY(Today)-WEEKDAY(Start))<0,2,0)-IF(OR(AND(WEEKDAY(Today)=7,WEEKDAY(Start)=7),AND(WEEKDAY(Today)=1,WEEKDAY(Start)=1)),1,0)-IF(AND(WEEKDAY(Start)=1,(WEEKDAY(Today)-WEEKDAY(Start))>0),1,0)-IF(AND(NOT(WEEKDAY(Start)=7),WEEKDAY(Today)=7),1,0))This is the flow that runs at 3am every day, and because it's updating each item the calculated column updates:
In the filter query below it's Include eq 1 because the Include column is a Yes/No column:
I hope that gives you something to think about for your scenario.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform, WSP UK (and classic 1967 Morris Traveller driver)
nivota with views it is possible for users to change the view so they could see the other teams' clients. This is because there is no way in a modern SharePoint list to use audience targeting on a view. It is possible if you switch back to classic view to do this and ganeshsanap who is on this forum regularly wrote a blog post about it a while back. But what I would do (other/better solutions are no doubt available) is to use 4 separate lists, one for each team - you can create a list from an existing list to save time. Then I would create 4 different permissions groups in advanced permissions settings, again one for each team and add the team members into each group. Then for each list go to the List settings -> Permissions for this list. Click on the stop inheriting permissions. Then for that list leave just the Owners group and appropriate team group for that list and give the team contribute permissions.. Make sure you add the Head of Sales to the Owners group.
So with this method there is no risk that a rep can just change the view and it makes it straightforward for each team to go to "their" list. The Head of Sales could see all the items in each list because s/he is in the Owners group. But if they wanted to view all the items together in a single list then I would build a flow in Power Automate set to run once a day at, say, 2am, to add all the items created the previous day into the full list. Make sure that only Site Owners have permissions on this list.
For the aging formula, you can't use Today or Now because a calculated column won't update dynamically based on the change in the date. The calculated column for an item only updates when an item is created, edited or the formula changes. So what I do in these situations is to have a column for Today and then a simple flow to update it every day.
This is my SharePoint list:
The formula for the Workdays calculated column is:
=IF(ISERROR(DATEDIF(Start,Today,"d")),"",(DATEDIF(Start,Today,"d"))+1-INT(DATEDIF(Start,Today,"d")/7)*2-IF((WEEKDAY(Today)-WEEKDAY(Start))<0,2,0)-IF(OR(AND(WEEKDAY(Today)=7,WEEKDAY(Start)=7),AND(WEEKDAY(Today)=1,WEEKDAY(Start)=1)),1,0)-IF(AND(WEEKDAY(Start)=1,(WEEKDAY(Today)-WEEKDAY(Start))>0),1,0)-IF(AND(NOT(WEEKDAY(Start)=7),WEEKDAY(Today)=7),1,0))This is the flow that runs at 3am every day, and because it's updating each item the calculated column updates:
In the filter query below it's Include eq 1 because the Include column is a Yes/No column:
I hope that gives you something to think about for your scenario.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform, WSP UK (and classic 1967 Morris Traveller driver)
