Forum Discussion
Setting View Access Control on SharePoint List
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)