How to: Add users to the Employee Training Management learner list

Search Knowledge Base by Keyword

This article describes how to add users to the Employee Training Management learner list.

In this article:

The learner list is a SharePoint list that will be created by the SharePoint|sapiens Employee Training Management app for Office 365. Users will be added to this list automatically as soon as they enroll in an event or a self-paced training the first time. Also you have the option to add users to this list using the new item form or the quick edit mode.

Add new users to learner list

In this article I would like to describe how to add all your users and their manager to this list automatically.

Option 1 – Use Microsoft Flow

The first option is to use Microsoft Flow to synchronize the list regularly with the users in Exchange Online.

Here you can download a Flow template that you can import into your Microsoft Flow app.

Import flow

Select the downloaded ZIP package and click on ‘Import’.

What you can see now is that the Flow has been configured in a demo environment. That’s why you first have to change the resource connections. Click on ‘Select during import’ in the IMPORT SETUP column and create a connection to Office 365 Users and SharePoint.

Import flow

Now click on ‘Import’. Once the workflow is imported, click on ‘Open flow’ at the top.

Open flow

This flow starts with a recurrence trigger that runs every 5 days followed by a ‘Search for users’ action.

Trigger and first action

Click ‘Show advanced options’ on the ‘Search for users’ action and enter the number of results to return. Enter a higher number than the number of Office 365 users to make sure you import all users.

In the flow you should see 3 SharePoint actions, one ‘Get items’, one ‘Update item’ and one ‘Create item’. This actions are currently connected with SharePoint sites in the demo environment, so you have to update these actions and connect them with the SharePoint site where you have the SharePoint|sapiens Employee Training Management app installed and configured.

Trigger and first action

You can now save the flow and click on ‘Run now’. This should now add all users to the learner list.

Depending on the Recurrence trigger this flow will run regularly and add and update users.

Option 2 – Use Windows PowerShell

First make sure the MSOnline and AzureAD PowerShell modules are installed. If not, first install the PackageManagement PowerShell modules: https://www.microsoft.com/en-us/download/details.aspx?id=51451

Now run the following commands to install the packages:

Install-Module -Name MSOnline
Install-Module -Name AzureAD
Install-Module -Name MSOnline
Install-Module -Name AzureAD

Now enter your Office 365 credentials

#get credentials
$credential = Get-Credential
#get credentials
$credential = Get-Credential

Enter you username and password

Now connect to AzureAD Management Shell V1

# Connect to AzureAD Management Shell V1
Connect-MsolService -Credential $credential
# Connect to AzureAD Management Shell V1
Connect-MsolService -Credential $credential

And now to Azure AD Management Shell V2

# Azure AD Management Shell V2
Connect-AzureAD -Credential $credential
# Azure AD Management Shell V2
Connect-AzureAD -Credential $credential

To connect to SharePoint you first have to register the SharePoint Client DLLs. You can download and install the SharePoint Client SDK and load them from the c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI folder …

# Connect to SharePoint online
# option 1 register the SharePoint Client DLLS from 16 HIVE: https://www.microsoft.com/en-us/download/details.aspx?id=51679
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"
# Connect to SharePoint online
# option 1 register the SharePoint Client DLLS from 16 HIVE: https://www.microsoft.com/en-us/download/details.aspx?id=51679
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"

… or if you are on a SharePoint on premise server load them directly from the Global Assembly Cache.

# option 2 register the SharePoint Client DLLS from GAC or local folder
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null
# option 2 register the SharePoint Client DLLS from GAC or local folder
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null

Now create the SharePoint context

#get sharepoint site URL
$webUrl = Read-Host -Prompt "Enter the web site URL"
 
# open sharepoint site
[Microsoft.SharePoint.Client.ClientContext]$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)
#get sharepoint site URL
$webUrl = Read-Host -Prompt "Enter the web site URL"

# open sharepoint site
[Microsoft.SharePoint.Client.ClientContext]$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)

The following script collects all users and the users’ managers.

# get all users with a licnese
$users = Get-MsolUser -MaxResults 2000 | Where-Object { $_.isLicensed -eq $true }
 
$usersWithManager = @()
 
ForEach($u in $users){
    $upn = $u.UserPrincipalName
    $upn_m = ""
    # get manager
    $manager = Get-AzureADUserManager -ObjectId $u.ObjectId
    if($manager -ne $null){
        $upn_m = $manager.UserPrincipalName
    }
    $result = "" | Select user,manager
    $result.user = $upn
    $result.manager = $upn_m
    $usersWithManager += $result
}
# get all users with a licnese
$users = Get-MsolUser -MaxResults 2000 | Where-Object { $_.isLicensed -eq $true }

$usersWithManager = @()

ForEach($u in $users){
    $upn = $u.UserPrincipalName
    $upn_m = ""
    # get manager
    $manager = Get-AzureADUserManager -ObjectId $u.ObjectId
    if($manager -ne $null){
        $upn_m = $manager.UserPrincipalName
    }
    $result = "" | Select user,manager
    $result.user = $upn
    $result.manager = $upn_m
    $usersWithManager += $result
}

You can now export this to CSV…

# export to CSV
$date = (get-date).ticks
$usersWithManager | Export-Csv -Path "users_$date.csv" -NoTypeInformation
# export to CSV
$date = (get-date).ticks
$usersWithManager | Export-Csv -Path "users_$date.csv" -NoTypeInformation

…or update the learner list directly.

# update list in SharePoint
 
# get learner list
$listLearners = $clientContext.Web.Lists.GetByTitle("Learners");
 
# get existing items in learner list
$q = New-Object Microsoft.SharePoint.Client.CAMLQuery
$q.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='etmUser'/><FieldRef Name='etmManager'/></ViewFields><RowLimit>2147483647</RowLimit></View>"
$items = $listLearners.GetItems($q)
$clientContext.Load($items)
$clientContext.ExecuteQuery()
 
ForEach($u in $usersWithManager)
{
    #get user
    $spu = $clientContext.Web.EnsureUser($u.user)
    $clientContext.Load($spu)
    #get manager
    $spm = $null
    if($u.manager -ne $null -and $u.manager -ne ""){
        $spm = $clientContext.Web.EnsureUser($u.manager)
        $clientContext.Load($spm)
    }
    $clientContext.ExecuteQuery()
    $spmID = 0
    if($spm -ne $null) { $spmID = $spm.Id }
    $learnerExists = $false
    ForEach($i in $items)
    {
        if($i.FieldValues["etmUser"] -is [Microsoft.SharePoint.Client.FieldUserValue] -and $i.FieldValues["etmUser"].LookupId -eq $spu.Id)
        {
            $i.FieldValues["etmManager"].LookupId
            $learnerExists = $true
            if(($i.FieldValues["etmManager"] -eq $null -and $spmID -gt 0) -or ($i.FieldValues["etmManager"] -ne $null -and $i.FieldValues["etmManager"].LookupId -ne $spmID))
            {
                # update existing learner with new manager
                if($spmID -gt 0) { $i["etmManager"] = ($spmID.ToString() + ";#") }
                else { $i["etmManager"] = $null }
                $i.Update()
            }
        }
    }
    if($learnerExists -eq $false)
    {
        #add learner
        $createItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $newItem = $listLearners.AddItem($createItemInfo)
        $newItem["Title"] = $spu.Title
        $newItem["etmUser"] =  ($spu.Id.ToString() + ";#")
        if($spmID -gt 0) { $newItem["etmManager"] = ($spmID.ToString() + ";#") }
        else { $newItem["etmManager"] = $null }
        $newItem.Update();
    }
    $clientContext.ExecuteQuery()
}
# update list in SharePoint

# get learner list
$listLearners = $clientContext.Web.Lists.GetByTitle("Learners");

# get existing items in learner list
$q = New-Object Microsoft.SharePoint.Client.CAMLQuery
$q.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='etmUser'/><FieldRef Name='etmManager'/></ViewFields><RowLimit>2147483647</RowLimit></View>"
$items = $listLearners.GetItems($q)
$clientContext.Load($items)
$clientContext.ExecuteQuery()

ForEach($u in $usersWithManager)
{
    #get user
    $spu = $clientContext.Web.EnsureUser($u.user)
    $clientContext.Load($spu)
    #get manager
    $spm = $null
    if($u.manager -ne $null -and $u.manager -ne ""){
        $spm = $clientContext.Web.EnsureUser($u.manager)
        $clientContext.Load($spm)
    }
    $clientContext.ExecuteQuery()
    $spmID = 0
    if($spm -ne $null) { $spmID = $spm.Id }
    $learnerExists = $false
    ForEach($i in $items)
    {
        if($i.FieldValues["etmUser"] -is [Microsoft.SharePoint.Client.FieldUserValue] -and $i.FieldValues["etmUser"].LookupId -eq $spu.Id)
        {
            $i.FieldValues["etmManager"].LookupId
            $learnerExists = $true
            if(($i.FieldValues["etmManager"] -eq $null -and $spmID -gt 0) -or ($i.FieldValues["etmManager"] -ne $null -and $i.FieldValues["etmManager"].LookupId -ne $spmID))
            {
                # update existing learner with new manager
                if($spmID -gt 0) { $i["etmManager"] = ($spmID.ToString() + ";#") }
                else { $i["etmManager"] = $null }
                $i.Update()
            }
        }
    }
    if($learnerExists -eq $false)
    {
        #add learner
        $createItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $newItem = $listLearners.AddItem($createItemInfo)
        $newItem["Title"] = $spu.Title
        $newItem["etmUser"] =  ($spu.Id.ToString() + ";#")
        if($spmID -gt 0) { $newItem["etmManager"] = ($spmID.ToString() + ";#") }
        else { $newItem["etmManager"] = $null }
        $newItem.Update();
    }
    $clientContext.ExecuteQuery()
}

This will add all users to the list of learner. Also the script checks if a user already exists and if the manager has been changed. This also allows you to run this script regularly to update the list.

Here is the entire script:

# Install PackageManagement PowerShell Modules - https://www.microsoft.com/en-us/download/details.aspx?id=51451
# Install-Module -Name MSOnline
# Install-Module -Name AzureAD
 
#get credentials
$credential = Get-Credential
 
# Connect to AzureAD Management Shell V1
Connect-MsolService -Credential $credential
# Connect to Azure AD Management Shell V2
Connect-AzureAD -Credential $credential
 
# Connect to SharePoint online
# option 1 register the SharePoint Client DLLS from 16 HIVE: https://www.microsoft.com/en-us/download/details.aspx?id=51679
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"
 
# option 2 register the SharePoint Client DLLS from GAC or local folder
# [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
# [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null
 
#get sharepoint site URL
$webUrl = Read-Host -Prompt "Enter the web site URL"
 
# open sharepoint site
[Microsoft.SharePoint.Client.ClientContext]$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)
 
# get all users with a licnese
$users = Get-MsolUser -MaxResults 2000 | Where-Object { $_.isLicensed -eq $true }
 
$usersWithManager = @()
 
ForEach($u in $users){
    $upn = $u.UserPrincipalName
    $upn_m = ""
    # get manager
    $manager = Get-AzureADUserManager -ObjectId $u.ObjectId
    if($manager -ne $null){
        $upn_m = $manager.UserPrincipalName
    }
    $result = "" | Select user,manager
    $result.user = $upn
    $result.manager = $upn_m
    $usersWithManager += $result
}
 
# export to CSV
# $date = (get-date).ticks
# $usersWithManager | Export-Csv -Path "users_$date.csv" -NoTypeInformation
 
# update list in SharePoint
 
# get learner list
$listLearners = $clientContext.Web.Lists.GetByTitle("Learners");
 
# get existing items in learner list
$q = New-Object Microsoft.SharePoint.Client.CAMLQuery
$q.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='etmUser'/><FieldRef Name='etmManager'/></ViewFields><RowLimit>2147483647</RowLimit></View>"
$items = $listLearners.GetItems($q)
$clientContext.Load($items)
$clientContext.ExecuteQuery()
 
ForEach($u in $usersWithManager)
{
    #get user
    $spu = $clientContext.Web.EnsureUser($u.user)
    $clientContext.Load($spu)
    #get manager
    $spm = $null
    if($u.manager -ne $null -and $u.manager -ne ""){
        $spm = $clientContext.Web.EnsureUser($u.manager)
        $clientContext.Load($spm)
    }
    $clientContext.ExecuteQuery()
    $spmID = 0
    if($spm -ne $null) { $spmID = $spm.Id }
    $learnerExists = $false
    ForEach($i in $items)
    {
        if($i.FieldValues["etmUser"] -is [Microsoft.SharePoint.Client.FieldUserValue] -and $i.FieldValues["etmUser"].LookupId -eq $spu.Id)
        {
            $i.FieldValues["etmManager"].LookupId
            $learnerExists = $true
            if(($i.FieldValues["etmManager"] -eq $null -and $spmID -gt 0) -or ($i.FieldValues["etmManager"] -ne $null -and $i.FieldValues["etmManager"].LookupId -ne $spmID))
            {
                # update existing learner with new manager
                if($spmID -gt 0) { $i["etmManager"] = ($spmID.ToString() + ";#") }
                else { $i["etmManager"] = $null }
                $i.Update()
            }
        }
    }
    if($learnerExists -eq $false)
    {
        #add learner
        $createItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $newItem = $listLearners.AddItem($createItemInfo)
        $newItem["Title"] = $spu.Title
        $newItem["etmUser"] =  ($spu.Id.ToString() + ";#")
        if($spmID -gt 0) { $newItem["etmManager"] = ($spmID.ToString() + ";#") }
        else { $newItem["etmManager"] = $null }
        $newItem.Update();
    }
    $clientContext.ExecuteQuery()
}
# Install PackageManagement PowerShell Modules - https://www.microsoft.com/en-us/download/details.aspx?id=51451
# Install-Module -Name MSOnline
# Install-Module -Name AzureAD

#get credentials
$credential = Get-Credential

# Connect to AzureAD Management Shell V1
Connect-MsolService -Credential $credential
# Connect to Azure AD Management Shell V2
Connect-AzureAD -Credential $credential

# Connect to SharePoint online
# option 1 register the SharePoint Client DLLS from 16 HIVE: https://www.microsoft.com/en-us/download/details.aspx?id=51679
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"

# option 2 register the SharePoint Client DLLS from GAC or local folder
# [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client") | Out-Null
# [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime") | Out-Null

#get sharepoint site URL
$webUrl = Read-Host -Prompt "Enter the web site URL"

# open sharepoint site
[Microsoft.SharePoint.Client.ClientContext]$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)
$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credential.UserName, $credential.Password)

# get all users with a licnese
$users = Get-MsolUser -MaxResults 2000 | Where-Object { $_.isLicensed -eq $true }

$usersWithManager = @()

ForEach($u in $users){
    $upn = $u.UserPrincipalName
    $upn_m = ""
    # get manager
    $manager = Get-AzureADUserManager -ObjectId $u.ObjectId
    if($manager -ne $null){
        $upn_m = $manager.UserPrincipalName
    }
    $result = "" | Select user,manager
    $result.user = $upn
    $result.manager = $upn_m
    $usersWithManager += $result
}

# export to CSV
# $date = (get-date).ticks
# $usersWithManager | Export-Csv -Path "users_$date.csv" -NoTypeInformation

# update list in SharePoint

# get learner list
$listLearners = $clientContext.Web.Lists.GetByTitle("Learners");

# get existing items in learner list
$q = New-Object Microsoft.SharePoint.Client.CAMLQuery
$q.ViewXml = "<View><Query></Query><ViewFields><FieldRef Name='etmUser'/><FieldRef Name='etmManager'/></ViewFields><RowLimit>2147483647</RowLimit></View>"
$items = $listLearners.GetItems($q)
$clientContext.Load($items)
$clientContext.ExecuteQuery()

ForEach($u in $usersWithManager)
{
    #get user
    $spu = $clientContext.Web.EnsureUser($u.user)
    $clientContext.Load($spu)
    #get manager
    $spm = $null
    if($u.manager -ne $null -and $u.manager -ne ""){
        $spm = $clientContext.Web.EnsureUser($u.manager)
        $clientContext.Load($spm)
    }
    $clientContext.ExecuteQuery()
    $spmID = 0
    if($spm -ne $null) { $spmID = $spm.Id }
    $learnerExists = $false
    ForEach($i in $items)
    {
        if($i.FieldValues["etmUser"] -is [Microsoft.SharePoint.Client.FieldUserValue] -and $i.FieldValues["etmUser"].LookupId -eq $spu.Id)
        {
            $i.FieldValues["etmManager"].LookupId
            $learnerExists = $true
            if(($i.FieldValues["etmManager"] -eq $null -and $spmID -gt 0) -or ($i.FieldValues["etmManager"] -ne $null -and $i.FieldValues["etmManager"].LookupId -ne $spmID))
            {
                # update existing learner with new manager
                if($spmID -gt 0) { $i["etmManager"] = ($spmID.ToString() + ";#") }
                else { $i["etmManager"] = $null }
                $i.Update()
            }
        }
    }
    if($learnerExists -eq $false)
    {
        #add learner
        $createItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
        $newItem = $listLearners.AddItem($createItemInfo)
        $newItem["Title"] = $spu.Title
        $newItem["etmUser"] =  ($spu.Id.ToString() + ";#")
        if($spmID -gt 0) { $newItem["etmManager"] = ($spmID.ToString() + ";#") }
        else { $newItem["etmManager"] = $null }
        $newItem.Update();
    }
    $clientContext.ExecuteQuery()
}