Company-wide contact list in M365

As a company grows we quite often need to maintain important internal contacts. This might be the Heads of departments or Sites, people responsible for certain areas, etc…

This list should be available to all employees without special software as well as to our Apps and Flows, always up-to-date, and in the best case only one list... At the same time, you want others to update the list, you are not the one who knows all the contacts and processes! Besides all that, some people are leaving and in a big company, even responsible contacts might forget to update the list... This can all be achieved on the SharePoint (SPO) List, but it brings some challenges we must consider.

• PowerApps or SPO View can access/display only if there is max. of 12 "Person" columns
• Responsible admins should be able to edit ONLY their part (understand columns)
• Responsible admins should be notified of inactive contacts

Here is a quick view of how to start...

Person columns vs text columns

In order to use it by the PowerPlatform we should stick to Text columns rather than Person Column. PowerApps likes to complain if you have too many "Person" columns and the number is quite low (12). Instead, use text columns and if required format these with JSON for better readability while viewing the list.

I use a format that is similar to email, see this example of two contacts: Jack Reacher <[email protected] >; Kate Columbo <[email protected] >

and JSON (for the column's format) looks like this (replace "MyContact" with the name of the column):

{  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "style": {
    "display": "block",
    "margin-top": "5px" },
  "elmType": "div",
  "children": [
    { "elmType": "div",
      "forEach": "person in split([$MyContact],';')",
      "txtContent": "=substring([$person],0,indexOf([$person],'<'))"}]}        

This code will ensure only names are visible and email / upn is hidden...

Once you open the particular item, you get a view that you can adjust by PowerApps so the form translates the name/email/upn to person (Combo Box with Person Layout). Here the DefaultSelectedItems property of such a combo box.

ForAll(Filter(Split(Substitute(Substitute(Substitute(Parent.Default, "<", ""),">",""), ";",""), " "), "@" in Value),
With( {TheRecord: Office365Users.UserProfileV2(Value)}, {
DisplayName: TheRecord.displayName,
UserPrincipalName: TheRecord.userPrincipalName }))        

In my case, I decided to save via the standard save button just the card Update property is pointing to the Combo Box and adds or removes some signs...

With ({ RecordX: Concat(YourComboBox.SelectedItems,
Substitute(DisplayName,",","") & " <" & UserPrincipalName &  ">" & "; " ) },
IfError(Left(RecordX, (Len(RecordX)-2)),RecordX))        

Permission to the list

While responsible leads should be notified if a contact is no longer active, they also need the ability to update the list. SPO doesn’t support column-level permissions, but here’s my workaround:

1. Grant the responsible leads contribute permissions on the list.
2. Disable Quick Edit (Grid View).
3. Hardcode UPNs into the Form with powerapps:

A, The form / App onStart property gets the UPNs and level, like this:

ClearCollect(yourCollection,
{Level: "Sales", UPN: "[email protected]"},
{Level: "IT", UPN: "[email protected]"}
)        

B, the individual cards (DisplayMode property) in your form are adjusted:

If( YourForm.Mode = FormMode.Edit And      Not(IsBlankOrError(LookUp(yourCollection, UPN = svcUPN.Text And Level = "Sales"))),      DisplayMode.Edit, DisplayMode.View)        

*The svcUPN is just a label that is getting the user UPN, since I use it multiple times I prefer to get it once and reuse it [ Office365Users.MyProfileV2().userPrincipalName ]

I trust the rest should be fairly simple, in fact, the whole process is not rocket science but one must spend a minute on it…

Setup watchdog Process

To keep an eye out for changes, automated processes should notify responsible people...

First, assign responsible individuals for each column in your contact list. For example, if the list contains sales managers for different regions, identify someone who can verify the accuracy of each entry. This could be managed in a separate "admin" list, indicating which person is in charge of which column.

Your watchdog flow should then check if these admins are active and enabled, setting a flag in another column if they are (e.g., a "true" status).

Finally, review the necessary columns to check if any contacts are no longer active. If you find one, notify the responsible lead for further action.

I also recommend doing this flow as parent and child relationship... so every column you need to check is a child that is running based on your admin list from where it gets the instructions on what to check. This way you don't need to edit the flow every time you add a column.

Hope you enjoyed