Preventing users from seeing list items worldwide context

[Leonel GUZMAN]

Thank you for reading the case

Scenario:

I'm planning to have a list, only one list, for managers to fill in a questionnaire. They will have to answer the same questionnaire every year, so the list will grow over time. The data will be used for analysis and audit. It's an internal control questionnaire.

The managers are located in 100+ countries, and they may delegate filling out the form to some of their staff, but the send/confirm button is only for them to push (can manage this part by checking who the user is, but suggestions are welcome).

Here's the challenge I have:

The items on the list should only be visible by country; staff in other countries should not see the items for their colleagues abroad.

I may make the draft available only to the author, but if there's more than one author I don't know what to do; for example if the manager delegates the filling-in of the form, two or three people in the same country should be able to see the draft answers.

Is there a way I can control the visibility of the items by creating a special group (or groups) or can you suggest a different solution?

I could create separate lists by country and merge them into a single one for analysis, but if possible I'd rather avoid that overhead.

All suggestions are more than welcome.

Thank you very much

Leonel

[#2]

Hi @Leonel GUZMAN,

I don't believe this is possible with "out of the box" SharePoint lists and permissions. You would have to consider a PowerApps solution where the data retrieval/manipulation can be performed programmatically.

I hope this helps.

Norm 

[#3]

@Leonel GUZMAN 

Agree with Norman, there is no out of the box option for this. We are using Power Apps and Dynamics 365 for the same scenario.

You may want to have more options as an admin which PowerApps + Dynamics giving you these.

Alireza

[#4]

@Leonel GUZMAN 

If users have access to the list, then they have access to the whole list. There are ways to use views on pages to make it difficult for people to see the rest of the list, but it cannot be prevented.

Even if you use Power Apps to control what they see in the form, that's still just fancy smoke and mirrors. It won't prevent users from going to the list to see what they want.

If you must have TRUE access control where you can guarantee that people do not see content from other countries, the only way to do that is to use separate lists with separate permissions. 

But the list maintenance shouldn't be that hard if you use site columns and a content type. This will allow you to manage the columns and values from a central location. Add a new value to the site column and it will populate all the lists using that column. Add a new column to the content type and it will populate all the lists using that content type. 

Analytics are easy if you use Power BI. Add the site as a data source and select all the lists that apply. Since they should have all the same columns and values, it should be pretty easy to set up a dashboard. You can then use row level security in PBI to control who sees which info in the dashboard.

[#5]

In July MS announced that Flow would get the ability to change permissions, see https://flow.microsoft.com/en-us/blog/july-updates-for-microsoft-flow/ but this functionaliy has not yet showed up in my production tenants. In theory, you could use SP Designer to create a workflow to change item level permission, but this approach is no longer recommended. If you can wait, the new features in Flow will make this very easy.