Removing External Users fully from a SharePoint Tenancy using PowerShell


This blog post has all come about as the client I was working for was having problems sharing documents in SharePoint with some external users. It turned out that the user was already in Azure AD as a Contact which is part of Exchange. This meant when an internal person attempted to share/Invite into SharePoint/MSTeams it all appeared to work correctly for the external user, but sometimes it didn’t. When looking at external users through the Admin portal, this external user was showing, but their email address was blank. After speaking with Microsoft, it turns out, because the email address was already found within the tenancy, it creates a unique violation when adding the external user to the Active Directory.

I have been working with Microsoft support regarding this, and the resolution was that this is as design!!??! Only by feeding back on the Office 365 uservoice this issue “might” looked at and fixed. See resolution notes below:

Symptom:
When you invite external users who exist as contacts in your environment, their email does not get populated in their guest user ID which results in them not being able to login to your environment and access the shared data.
Cause:
The issue is coming from a conflict caused by the email address which is already populated for the mail contact.
Resolution:
This is behavior by design as all objects in Azure AD have to be unique.
You cannot have 2 objects with the same email address.

When you invite one of your contacts to your content in O365, it actually creates a completely new guest user object in your environment and since the email address which is supposed to be populated in the email attribute is already in use by the contact, the email address does not get populated.

The only way to resolve this issue at the moment is to eliminate any conflicts that are in place, by removing the conflicting email contact and re-invite the user to your content.
More information:
The best thing I can offer to you is the following:

Please go to our UserVoice portal where other people are facing the same behavior and up-vote it, comment and have the whole IT department do the same as well.

Allow a “Guest User” to be converted to a different account type
https://office365.uservoice.com/forums/273493-office-365-admin/suggestions/19966537-allow-a-guest-user-to-be-converted-to-a-differen

This led me to working on a process and script that would remove the users from everywhere.

Locations to remove the External User from:

  • Contacts
  • Azure AD Guest Users
  • Azure AD Deleted Users
  • All SharePoint Sites
  • All SharePoint Hidden User lists
  • SharePoint User Profile

Contacts

To remove the External User from the contacts you will need to use the MSOL PowerShell module.

$UserEmail = "<ExternalUserEmailAddress>"
Connect-MsolService
Get-MsolContact | ? EmailAddress -eq $UserEmail | Remove-MsolContact -Force

Or you can manually do this by going to admin.microsoft.com and under Users -> Contacts select the user and click Delete contacts.

Azure AD

To remove the External User from Azure AD you will still require using the MSOL PowerShell module. In fact, this script and the above script could be merged.

$Environment = "<TenantName>"
$UserEmail = "<ExternalUserEmailAddress>"
Connect-MsolService
$externalConversionEmail = ($UserEmail -replace '@', '_') + "#EXT#@" + $Environment + ".onmicrosoft.com"
$FoundUser = Get-MsolUser | ? UserPrincipalName -eq $externalConversionEmail
if($FoundUser){
Remove-MsolUser -UserPrincipalName $($FoundUser.UserPrincipalName) -Force
#To see All Deleted User Get-MsolUser -ReturnDeletedUsers
Remove-MsolUser -UserPrincipalName $($FoundUser.UserPrincipalName) -RemoveFromRecycelBin -Force
#To Remove All Deleted Users Get-MsolUsers -ReturnDeletedUsers | Remove-MsolUser -RemoveFromRecycleBin -Force
}

To do this manually, in admin.microsoft.com under Users -> Guest Users, select the user and click delete.

Then go into Users -> Deleted users and remove them from there.

Remove from SharePoint

To remove from SharePoint, if you have a large tenancy and you don’t know all the places where the external user could have been shared with, then you will have to use the following script. This script will remove the external user from the SharePoint Site, ensure that they are removed from the User Information list, and then lastly it will clear the person from the SharePoint User Profile.

I discovered that if I didn’t remove them from the User Profile, when attempted to reshare a document with that user, the people picker would grab the internal userprincipalname (<ExternalUserEmail>#EXT#@<Tenant>.onmicrosoft.com) as the email address and then prevent me clicking the Sharing button. This is because the people picker uses Graph API /Me/People and grabs the value from there. Once removed from everywhere, including the User Profile this no longer happens.

The following script uses SPO PowerShell Module and you will need to connect first using Connect-SPOService. The account that you use, needs to be a SharePoint Global Administrator.

The script checks if it can find the ExternalUser, and if it can remove the user using Remove-SPOExternalUser.

Then it loops through every site collection and looks for the user using Get-SPOUser with the internal userprincipalname. If found it removes the user using Remove-SPOUser. Once it has looped through all SharePoint sites, it then checks the SharePoint User Profile and removes the user from UserProfile Remove-SPOUserProfile. This command will remove a user from the UserProfile if they in the “Active Profiles” or the “Profiles Missing from Import”

<#
.SYNOPSIS
Loops through the SharePoint sites of the tenant, looking for the external user and removing them.
You need to have already connected to the Tenant as a SharePoint Global Adminstrator using Connect-SPOService -url:https://<tenant>-admin.sharepoint.com
.EXAMPLE
.\Remove-ExternalUserFromTenant.ps1 -Environment:<tenant> -UserEmail:<externalEmailAddres>
#For Tenant called Dev34223 and external email address fred.bloggs@outlookdomain.com
.\Remove-ExternalUserFromTenant.ps1 -Environment:Dev34223 -UserEmail:fred.bloggs@outlookdomain.com
#>
[CmdletBinding(SupportsShouldProcess)]
param(
[Parameter(Mandatory)]
[string]
$Environment,
[Parameter(Mandatory)]
[string]
$UserEmail
)
Clear-Host
$sites = Get-SPOSite -Limit ALL
$externalConversionEmail = ($UserEmail -replace '@', '_') + "#EXT#@" + $Environment + ".onmicrosoft.com"
$ErrorActionPreference = 'Stop'
$InformationPreference = 'Continue'
Write-Information -MessageData "Get $UserEmail External User within SharePoint"
$ExtUser = Get-SPOExternalUser -Filter $UserEmail
if ($null -ne $ExtUser) {
Write-Information -MessageData "Remove $UserEmail within SharePoint"
Remove-SPOExternalUser -UniqueIDs @($ExtUser.UniqueId) -Confirm:$false
}
$found = $false
$Sites | ForEach-Object {
$site = $PSItem
$i = $i + 1
try {
Get-SPOUser -site:$($site.Url) -LoginName:$externalConversionEmail
write-Information "Found user $UserEmail in site $($site.Title) Url:$($site.Url)"
Remove-SPOUser -site:$($site.Url) -LoginName:$externalConversionEmail
$found = $true;
}
catch {
#User not found.
}
Write-Progress -Activity "Removing User - $UserEmail" -Status "Progress:$($site.Url)" -PercentComplete ($i / $Sites.count * 100)
}
if ($found) {
Write-Information "User $UserEmail removed from SharePoint Sites"
}
else {
Write-Information "User $UserEmail wasn't found within SharePoint Sites"
}
Write-Information -MessageData "Remove $externalConversionEmail from SharePoint User profile"
try {
Remove-SPOUserProfile -LoginName $externalConversionEmail
}
catch {
Write-Information "Unable to find $externalConversionEmail in the user profiles."
}

If the plan is to add the external person back into your tenant, once the script has run, you will need to wait at least a few hours (maybe leave it for a day to be sure) to ensure all back end processes of Microsoft have completed.

When you share a document/folder with the external user they will get the invited link and enter a code experience, this way they do not turn up inside you Azure AD. However, if you share a site with them, or add them to a MS Teams, they will appear in your Azure AD correctly.

Advertisement

View Trace Logs with PnP Provisioning Templates using PowerShell


When running the Get-PnPProvisioningTemplate and the Apply-PnPProvisioningTemplate it is sometime necessary to see what is going on while the call is processing.

Just run the following command first before calling your Get/Apply -PNPProvisioningTemplate command.

Set-PnPTraceLog -On -Level:Debug

If you need to turn it off again

Set-PnPTraceLog -Off

PnP Provisioning Templates Adding – Everyone except external users


On a recent project of mine, I’ve been working with the PnP Provisioning engine using PowerShell. It’s really the first time I’ve really worked with it, and I must say I’m impressed at how easy it is to use.

If you have never used it before I recommend you checking out the following articles on MSDN.

https://msdn.microsoft.com/en-us/pnp_powershell/pnp-powershell-overview

https://msdn.microsoft.com/en-us/pnp_articles/pnp-provisioning-framework

https://msdn.microsoft.com/en-us/pnp_articles/pnp-remote-provisioning

Basic installation setup

If you have a Windows 10 device or have installed PowerShellGet, to check if you have the latest version installed in your PowerShell environment, run the following below

Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name, Version | Sort-Object Version -Descending

I already have the latest version at the time of writing this 2.17.1708.1.

You can install or Upgrade the SharePointPNPPowerShell with the following commands.

To Install

#SharePoint Online
Install-Module SharePointPnpPowerShellOnline

#SharePoint 2016
Install-Module SharePointPnPPowerShell2016

#SharePoint 2013
Install-Module SharePointPnPPowerShell2013

To Upgrade

Update-Module SharePointPnPPowerShell*

Everyone and Everyone Except External Users

The quickest way to create a PnP Provisioning template ready to use again, is to create your site within SharePoint using point and click.

I’ve created my site with Members set up to “Everyone except external users” and Visitors set up to “Everyone”.

After you have created your site, you use the following commands to connect and export the template.

Connect-PnpOnline -Url:https://mytenant.sharepoint.com/sites/pnpexamples -Credentials: (Get-Credential)
Get-PnPProvisioningTemplate -Out 'pnpExample.xml'

If you open the XML file, and look in the <pnp:Security> section, you will see that Additional Members has c:0-.f|rolemanager|spo-grid-all-users/{GUID} and Additional Visitors has c:0(.s|true. These represent Everyone except external users and Visitors respectively.

If you are only using this template to create more sites in the same tenant that you exported it from, then you are good. The GUID after ‘spo-grid-all-users’ will always be that GUID in your tenant. However, when you want to use this template in other tenants – for example you have a staging and production environment – then this GUID will not work and the importing of the template will not add Everyone except external users to your members group.

What is the GUID after spo-grid-all-users?

It turns out that the GUID relates to the Authentication Realm ID of your tenant, and luckily PnP have a PowerShell command

Get-PnPAuthenticationRealm

How does that help us?

You can pass parameters into the Apply-PnPTemplate. First we need to change the XML inside <pnp:AdditionalMembers>

From:

<pnp:User Name="c:0-.f|rolemanager|spo-grid-all-users/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx" />

To:

<pnp:User Name="c:0-.f|rolemanager|spo-grid-all-users/{parameter:AuthenticationRealm}" />

Save the XML template and now you can run the following commands to apply the template to a new site in a different tenant. (Please note my team site I’m applying this to has already been created)

Connect-PnpOnline -Url:https://myOtherTenant.sharepoint.com/sites/pnpexamples -Credentials: (Get-Credential)
$AuthenticationRealm = Get-PnPAuthenticationRealm
Apply-PnPProvisioningTemplate -Path:'pnpExample.xml' -Parameters:@{"AuthenticationRealm"=$AuthenticationRealm}

After applying the template to another tenant, you will see your “Everyone except external users” inserted correctly.

Using the Windows Credentials Manager with PnP PowerShell


Do you get fed up keep typing in your username and password when you are connection to SharePoint via PowerShell? Or you have to keep changing between multiple tenants and get fed up keep typing in the username and password? Did you know you could use the built-in Windows Credential Manager to help ease your pain?

  • Open your Credential Manager

  • Under the Generic Credentials, click ‘Add a generic credential

  • For each tenant/user account you need, create a Generic Credential.
    • Put a label name to indicate what the permissions are for (e.g DevAdmin, TestAdmin, TestUser etc)
    • Put the username of the account
    • Put the password of the account

  • After you have created your Generic Credential(s), when you try to Connect to SharePoint using PNP, you can pass your Label to the credentials.
    Connect-PnPOnline -Url "https://mydevtenant.sharepoint.com/sites/pnpexamples" -Credentials:devAdmin
    

    In the screenshot below, I’m connecting to my tenant using a label I created called CFAdmin1

Using the above technique of Credential Manager labels, you can make your PowerShell scripts easier by creating a string variable called label and pass it in. This will make running the same script for multiple environments easier.

[CmdletBinding(SupportsShouldProcess)]
param(
    # The environment label to use for connection
    [Parameter(Mandatory)]
    [string]
    $Label,

    # The URL of the tenancy to create the site collections in, do not include the -admin
    [Parameter(Mandatory)]
    [string]
    $URL,
)
if ($VerbosePreference) {
    Set-PnPTraceLog -On -Level:Debug
}
else {
    Set-PnPTraceLog -Off
}
Connect-PnPOnline -Url:$URL -Credentials:$Label
…
… #Additional code to update Web
…

If the above file was called UpdateWebSite.ps1 I would type in the following:


.\UpdateWebSite.ps1 -Url:'https://mydevtenant.sharepoint.com/sites/pnpexamples' -Label:devAdmin

Power BI using Parameters to use the same PBIX file for different tenants


In the past I, have created a Power BI file connecting it to the developer tenant data, design the reports based on the dummy data in my development tenant, before publishing it to the production tenant using the production data. For those that have done this before, you will know it’s not easy because all your data queries are pointing to the URL you first used, every query you have created will have this URL in, also GUIDs for lists filters etc could all be different in another tenant.

I will be continuing from my last example of a movie database from my previous post. I will be updating the queries so that in future, there is one location to change my parameters. My previous post has 4 queries which was my main Movies list from SharePoint, the TaxonomyHiddenList from SharePoint, and two references of the TaxonomyHiddenList renamed and filtered for Genre and Country.

First I need to think what might be different in the other tenant compared to my original one.

  • Tenant Name
  • Site/Web URL
  • Genre TermSet Id
  • Country TermSet Id

I need to create 4 paramters and name them to correspond with possible changing values. To do this, I need to ensure I’m in the Edit Queries. From your report, on the ribbon click Edit Queries. This will bring up the Edit Queries editor.

Underneath your current queries, right click and from the context menu, select New Group. Name the group Parameters. Your original queries will be places in a group folder called Other Queries.

Right click the Parameters folder, and from the context menu, select New Parameter…

Add 4 new parameters:

  • Tenant:
    • Name: Tenant
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: mytenant.sharepoint.com
  • WebSiteUrl:
    • Name: WebSiteUrl
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: /sites/taxonomy
  • GenreTermSetId:
    • Name: GenreTermSetId
    • Required:True
    • Type: Text
    • Suggested Values: Any
    • Current Value: f46ebfeb-cc18-479f-bac8-48fdca36dd6c
  • CountryTermSetId:
    • Name: CountryTermSetId
    • Required: True
    • Type: Text
    • Suggested Values: Any
    • Current Value: 6e0c3d2e-5514-4744-a08d-7318fd437a45

Click OK.

You should now have 4 parameters showing in your Query window. Now we need to use these.

Starting with the CountryTermSetId, previously my Country query was filtered on a text value that was the GUID of the TermSetId. Now I have a parameter I can use instead. I will want to update my query to use a parameter instead of free text. In the Applied Steps for Country, I can click the cog next to Filtered Rows which is step 2.

This brings up a Filter Rows dialog, where you can see it current is using a Text value to query against.

By selecting the dropdown where it currently shows ABC, you can select parameter instead. Then in the last dropdown box you are given all your parameters, here I would select CountryTermSetId.

I then repeat those steps for Genre query, using the GenreTermSetId instead.

For the movies query, I need to click the Advanced editor from the ribbon bar to amend the query to use parameters.

The text in this window is the query that has been currently set.

let
    Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),
    #"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Id="acd616ae-ee59-4597-86f3-3d5b65e64547"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"acd616ae-ee59-4597-86f3-3d5b65e64547",{{"ID", "ID.1"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "Total Worldwide Box_", "Genre", "Country"}),
    #"Expanded Genre" = Table.ExpandRecordColumn(#"Removed Other Columns", "Genre", {"TermGuid"}, {"Genre.TermGuid"}),
    #"Expanded Country" = Table.ExpandRecordColumn(#"Expanded Genre", "Country", {"TermGuid"}, {"Country.TermGuid"})
in
    #"Expanded Country"

We need to change the Source row (Line 2), and the #”acd616ae-ee59-4597-86f3-3d5b65e64547″ (Line3).

The Source row (Line 2), would need the URL part changed from

Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),

to

Source = SharePoint.Tables("https://"&tenant&WebSiteUrl, [ApiVersion = 15]),

You can see we are inserting the parameters names and concatenating them using the ampersand.

The Line 3 row we are using the Title of the list instead of the ID as this would be different in another tenant.

#"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Id="acd616ae-ee59-4597-86f3-3d5b65e64547"]}[Items],

to

#"acd616ae-ee59-4597-86f3-3d5b65e64547" = Source{[Title="Movies"]}[Items],

We would also do something similar to the TaxnomyHiddenList query too. Changing the first 3 lines from:

let
    Source = SharePoint.Tables("https://mytenant.sharepoint.com/sites/taxonomy", [ApiVersion = 15]),
    #"63f7f485-a5ca-4be5-815f-d0e3235e96d1" = Source{[Id="63f7f485-a5ca-4be5-815f-d0e3235e96d1"]}[Items],

to

let
    Source = SharePoint.Tables("https://"&Tenant&WebSiteUrl", [ApiVersion = 15]),
    #"63f7f485-a5ca-4be5-815f-d0e3235e96d1" = Source{[Title="TaxonomyHiddenList"]}[Items],
 

If you have followed this correctly, you would find that your data still works, and there are no issues. Click Close & Apply from the ribbon. The apply query changes will happen but your data will be the same. In the future when you want to publish to a different tenant you just need to change your parameters, instead of going to each query and updating the URL ID’s/GUIDs.

Setting the landing page for a wiki library


When you create a new wiki library, just by clicking on the library takes you directly to the home page of the wiki. Not to the list of pages like you would get if you clicked on the document library, or even the Site Pages library (Which is also a wiki library). In wiki libraries, this happens because there is a value in the property bag of the list called “vti_welcomepage”. This value is set to a page within the library, typically “home.aspx”. This can be change, unfortunately it can only be changed in code. I haven’t found a way to do this in the GUI.

UPDATE: As pointed out to me by Ronnie Holm (Thanks), you shouldn’t modify the vti_welcomepage value in the property bag. There is a method on the RootFolder called “WelcomePage” that you can get or set this value. I wasn’t aware of this method originally. When changing the WelcomePage via this method it automatically changes vti_welcomepage in the property bag.

The below PowerShell will be able to make the change for you, just update the page name to the name you wish to use.

 
$UserName = Read-Host -Prompt "UserName" 
$Password = Read-Host -Prompt "Password" -AsSecureString 
$Url = "https://mySharePoint365.sharepoint.com/sites/TestSite" 

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" 
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($Url) 
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password) 
$ctx.ExecuteQuery(); 

$list = $ctx.Web.Lists.GetByTitle("My Wiki") 
$list.RootFolder.WelcomePage = "What you should know about wiki's.aspx" 
$list.RootFolder.Update(); 
$ctx.ExecuteQuery(); 

$ctx.Load($list.RootFolder)
$ctx.ExecuteQuery();

Write-host("Current Welcome Page : " + $list.RootFolder.WelcomePage);
write-host("Complete");

Now when you click on your wiki library or navigate to the URL of the wiki library, it will redirect and display the wiki page you have set as the home page.

Updated Pages link in Wiki Pages missing



When creating a Wiki page library, every page you create/modify appears in the Updated Pages section above your quick launch navigation. Now, out of the box a team site has site pages. Site pages is a Wiki page library, and therefore it make sense just to use the Site pages as your wiki library. However, when you are using the Site Pages library, the Updated Pages link is missing.

There are lots of blog post out there showing you how to hide the Updated Pages link. Easiest way is using a bit of css :

.ms-quicklaunchouter{
 display:none;
}

.ms-core-listMenu-separatorLine{
 border-style:none;
}

However, I was struggling to find anywhere that showed you how show it. (Reversing the CSS didn’t make any difference, as the Updated Pages link wasn’t in the HTML in the first place.

Showing the Updated Pages in SitePages library.

What’s in a URL? Quite a lot it seems in this case. There must be some server side code that states, if the URL is /SitePages then don’t display Updated Pages. So the solution is to change the URL of the SitePages list. (Or don’t use the Site pages list in the first place, however, we already have content in our Site Pages list).

UPDATE: Do not do the below, if you do, when you go to “add a page”, SharePoint will ask you to create the default Wiki Library which is Site Pages. Therefore, it would be best to just create a new Wiki Library with the name you want to give, and move the existing wiki pages from Site Pages to your new Wiki Library. As long as you are on a page within the new Wiki Library, when you select “Add a Page” from the Cog, the page will be added to the Wiki Library.

There is 3 ways you could change the url.

  1. Using SharePoint designer. Find the SitePages library in the All Files section. Right click it and rename it.
  2. Using File Explorer. Find a list that you can open with Explorer (as Site Pages doesn’t allow you to) and then navigate to your site structure. Rename the Site Pages Folder. (I don’t recommend doing this way)
  3. PowerShell. I would recommend this way over the other two.
$UserName = Read-Host -Prompt "UserName"
$Password = Read-Host -Prompt "Password" -AsSecureString

$Url = "https://mysharepoint365Url.sharepoint.com/sites/TestSite"

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $Password)
$ctx.ExecuteQuery();

$list = $ctx.Web.Lists.GetByTitle("Site Pages")
$list.RootFolder.MoveTo("NewLocation");
$ctx.ExecuteQuery();

write-host("Complete");

As soon as the URL has changed, the Updated Pages link will show.

NOTE: The only time it will never show up, is if you make the page your home page to your site. It doesn’t matter what the URL is, it will not show.

UPDATE: Do not move the Site Pages library, if you do, when you go to “add a page”, SharePoint will ask you to create the default Wiki Library which is Site Pages. Therefore, it would be best to just create a new Wiki Library with the name you want to give, and move the existing wiki pages from Site Pages to your new Wiki Library. As long as you are on a page within the new Wiki Library, when you select “Add a Page” from the Cog, the page will be added to the Wiki Library.

Creating lookup and dependency columns in CSOM


What is a dependency column?

When you create a list in SharePoint this list can be used as a lookup. So for example (a very bad example, but gives you the idea), you have a customer list and an order list. On the order list you want to lookup to your Customer List. You would pick the column you want to point to for the lookup, in this case it would probably be the Customer column. However there might be other columns from the Customer list that you want to pull through to your orders list. (Customer ID, Date Joined etc). These extra columns are the dependency columns.

The customer you pick to associate with the order, these extra columns are pulled through. On the lookup list, if any of the data changes for the customer, this data will automatically be updated on the order list.

Not all columns can be used as a lookup/dependency column, only columns that can are:

  • Single Line of text
  • Number
  • Date
  • Calculated

The Demo

I have put together a demo, using a SharePoint hosted Add-In (SharePoint App). This SharePoint Add-in acts as a provisioning page to create my columns and lists, (similar to how OfficeDevPnp samples work) I will not be explaining in this post how to create a SharePoint Add-In.

The main point of the code that adds the dependant lookups uses the method AddDependentLookup which is part of the Microsoft.SharePoint.Client namespace.

public Field AddDependentLookup(string displayName, Field primaryLookupField, string lookupField ) 

I will be explaining the relevant sections of the code. All the methods have been written so that if anything already exists, it will not recreate it. My code also using OfficeDevpnp dlls, which I have obtained through NuGet. It is not until near the end after creating the Order List will I be adding the dependency columns.


Creating the Customer List

This is standard SharePoint CSOM code, I’m checking if the list already exists, if it doesn’t then I create it.

 
List customerList = null; 
if(!ctx.Web.ListExists("Customers")) 
{ 
  customerList = ctx.Web.CreateList(ListTemplateType.GenericList, "Customers", false, false); 
}
else { 
  customerList = ctx.Web.Lists.GetByTitle("Customers"); 
} 
customerList.Update(); 
ctx.Load(customerList); 
ctx.ExecuteQueryRetry(); 

I then change the Title display value from Title to Customer Name.

var title = customerList.Fields.GetByInternalNameOrTitle("Title"); 
title.Title = "Customer Name"; 
title.Update(); 

Lastly I create 3 fields if they don’t exist, Account ID, Address, Date Joined. Once the Date Joined column is created, I’m then ensuring that the Date column is just using Date Only, instead of Date Time values.

 if(!customerList.FieldExistsById("E99BF256-BC01-4A37-B35A-B39BCC5FB82E")) 
 { 
   var accountId = new FieldCreationInformation(FieldType.Text){ 
                           AddToDefaultView = true, 
                           DisplayName = "Account ID", 
                           Id = new Guid("E99BF256-BC01-4A37-B35A-B39BCC5FB82E"), 
                           Group="Lookups", 
                           Required=true, 
                           InternalName = "AccountID" 
                      }; 
    customerList.CreateField(accountId, true); 
 }

 if(!customerList.FieldExistsById("2A8ABC2E-B7F0-4187-ADCA-7831648AFAD3")) 
 {
   var address = new FieldCreationInformation(FieldType.Note){
                           AddToDefaultView = true, 
                           DisplayName = "Address", 
                           Id = new Guid("2A8ABC2E-B7F0-4187-ADCA-7831648AFAD3"), 
                           Group = "Lookups", 
                           Required =true, 
                           InternalName = "CustomerAddress" 
                       }; 
   customerList.CreateField(address, true); 
 } 

 if(!customerList.FieldExistsById("C73123E9-85C8-4156-B280-E7783EEB119C")) 
 { 
   var dateJoined = new FieldCreationInformation(FieldType.DateTime){
                             AddToDefaultView = true, 
                             DisplayName = "Date Joined", 
                             Id= new Guid("C73123E9-85C8-4156-B280-E7783EEB119C"), 
                             Group = "Lookups", 
                             Required = true, 
                             InternalName = "DateJoined" 
                         }; 
   customerList.CreateField(dateJoined, true); 

   var dateJoinedField = ctx.CastTo<FieldDateTime>(customerList.Fields.GetById(new Guid("C73123E9-85C8-4156-B280-E7783EEB119C"))); 
   ctx.Load(dateJoinedField); 
   ctx.ExecuteQueryRetry(); 

 if(dateJoinedField.DisplayFormat == DateTimeFieldFormatType.DateTime) 
 { 
  dateJoinedField.DisplayFormat = DateTimeFieldFormatType.DateOnly; 
  dateJoinedField.UpdateAndPushChanges(true); 
  ctx.ExecuteQueryRetry(); 
 } 
}

 

As this is just a demo, I want to ensure that my Customer list already has some data in it ready to use. Therefore I’ve added a method that just adds some data to the Customer list.

private void CreateCustomerData(ClientContext ctx) 
{ 
  List customerList = ctx.Web.Lists.GetByTitle("Customers"); 
  ctx.Load(customerList); 
  ctx.ExecuteQueryRetry(); 
  if(customerList.ItemCount == 0) { 
      //Add Data. 
      Microsoft.SharePoint.Client.ListItem cust1 = customerList.AddItem(new ListItemCreationInformation()); 
      cust1["Title"] = "Customer A"; 
      cust1["AccountID"] = "A12345"; 
      cust1["CustomerAddress"] = "85 Abbott Close, \r\nLondon"; 
      cust1["DateJoined"] = new DateTime(2015, 6, 4).ToString("o"); 
      cust1.Update(); 

      Microsoft.SharePoint.Client.ListItem cust2 = customerList.AddItem(new ListItemCreationInformation()); 
      cust2["Title"] = "Customer B"; 
      cust2["AccountID"] = "B26554"; 
      cust2["CustomerAddress"] = "745 Rose Drive, \r\nLondon"; 
      cust2["DateJoined"] = new DateTime(2014, 8, 14).ToString("o"); 
      cust2.Update(); 
      
      Microsoft.SharePoint.Client.ListItem cust3 = customerList.AddItem(new ListItemCreationInformation()); 
      cust3["Title"] = "Customer C"; 
      cust3["AccountID"] = "C44575"; 
      cust3["CustomerAddress"] = "547 Cooper Way, \r\nLondon"; 
      cust3["DateJoined"] = new DateTime(2011, 1, 24).ToString("o"); 
      cust3.Update(); 

      ctx.ExecuteQueryRetry(); 
 } 
} 

 

Now we can move onto creating the Orders list. I have written this code very similar to how I started to write the Customer list, where I’m first checking if it exists first before creating it. I’m then changing the Title display value from Title to Orders. So that I have access to the Customers list columns I’m loading the list and columns.

 List orderList = null; 
 if (!ctx.Web.ListExists("Orders")) 
 { 
   orderList = ctx.Web.CreateList(ListTemplateType.GenericList, "Orders", false, false); 
 } 
 else 
 { 
   orderList = ctx.Web.Lists.GetByTitle("Orders"); 
 } 

 //Change Title 
 var title = orderList.Fields.GetByInternalNameOrTitle("Title"); 
 title.Title = "Order Item"; 
 title.Update(); 

 //Get the customer list 
 List customerList = ctx.Web.Lists.GetByTitle("Customers");
 //Load Lists, fields and views ready to add more fields and lookup fields. 
 ctx.Load(orderList); 
 ctx.Load(customerList); 
 ctx.Load(customerList.Fields); 
 ctx.ExecuteQueryRetry();

I now need to add the columns to the Order list. I’m going to start with the Cost column, as this is a standard currency column. I’m checking if the column exists first before adding it to the list.

 Field cost = null; 
 if (!orderList.FieldExistsById(new Guid("70420D11-3D40-4B53-AAF4-21B57D51C033"))) 
 { 
  FieldCreationInformation orderCost = new FieldCreationInformation(FieldType.Currency) 
  { 
    DisplayName = "Cost", 
    Id = new Guid("70420D11-3D40-4B53-AAF4-21B57D51C033"), 
    AddToDefaultView = true, 
    Group = "Lookups", 
    Required = true, 
    InternalName = "Cost" 
  }; 

 cost = orderList.CreateField(orderCost, true); 
 ctx.Load(cost); 
 } 

This is the section where the AddDependentLookup method is being used. First I need to create the lookup column from the Customers list to the Orders list. If it already exists I need to load this column as I require the Field when I call the AddDependentLookup method. Once this is created, I will check to see if the dependency column has already been added. Unfortunately when it gets added, you don’t have control over what the GUID of the column will be. Therefore you will need to check by internal name. This name will be the title of the column that you give it encoded the way SharePoint encodes spaces, punctuation etc. If the column doesn’t exist it is then added to the Order list using the AddDependentLookup method passing in the column display name, the lookupfield, and the internal name of the dependant column within the customer list.

FieldLookup customerLookupField = null; 
if (!orderList.FieldExistsById("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0")) 
{ 
  FieldCreationInformation customerLookup = new FieldCreationInformation(FieldType.Lookup) { 
       DisplayName = "Customer", 
       Id = new Guid("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0"), 
       Group = "Lookups",
       Required = true, 
       AddToDefaultView = true, 
       InternalName = "CustomerLookup" 
 }; 
  customerLookupField = ctx.CastTo<FieldLookup>(orderList.CreateField(customerLookup, false)); 
  customerLookupField.LookupList = customerList.Id.ToString(); 
  customerLookupField.LookupField = "Title"; 
  customerLookupField.Update(); 
  ctx.ExecuteQueryRetry(); 
} 
else 
{ 
  customerLookupField = ctx.CastTo<FieldLookup>(orderList.Fields.GetById(new Guid("FE9ED460-02E7-4124-A4F0-BFE5A3DDA4D0"))); 
  ctx.Load(customerLookupField); 
  ctx.ExecuteQueryRetry(); 
 } 
 
 //Add Dependency fields. AccountID, DateJoined 
 Field accountDependency = null; 
 if (!orderList.FieldExistsByName("Cust_x002e__x0020_Account")) 
 {
   accountDependency = orderList.Fields.AddDependentLookup("Cust. Account", customerLookupField, "AccountID"); 
   ctx.Load(accountDependency); 
 } 
  Field dateJoinedDependency = null; 
  if (!orderList.FieldExistsByName("Cust_x002e__x0020_Joined")) 
  { 
    dateJoinedDependency = orderList.Fields.AddDependentLookup("Cust. Joined", customerLookupField, "DateJoined"); 
  ctx.Load(dateJoinedDependency); 
  } 
  ctx.ExecuteQueryRetry();

After deploying my app, and loading it up, I am able to create the lookup lists.

Customer List


Creating a new Order


Orders List


Reference

https://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.fieldcollection.adddependentlookup.aspx

Link to Visual Studio Project

http://1drv.ms/1WWzyoi