Network Share Migration to SharePoint Online using SharePoint Online Management Shell via Azure Storage

The Problem

A large number and volume of files on a local network share need to be migrated to SharePoint Online. Moving this way is automated from click to upload complete and any document properties need to be carried with the files.

The Approach

Install SPMS https://www.microsoft.com/en-us/download/details.aspx?id=35588

note the account name and account key in the script (the account name and the account key can be found at office 365 admin center > import > + > upload files over the network)

# Azure Storage information

$azureStorageAccountName = ""

$azureStorageAccountKey = ""

Create a local folder C:\temp or similar to hold the xml files and note the network document folder to upload in the sources of the script.

$srcPath = "\\network share path\"

$pkgPath = "C:\temp\SourcePkg"

$targetPkg = "C:\temp\TargetPkg"

Run SPMS 'as Administrator', change directory to the location of the script (I use c:\temp to keep it all together.

Run the script using .\pscriptname.ps1:

$cred365 = (Get-Credentials "adminuser@mysite.onmicrosoft.com")

-This will pop-up a window to get the password for the Office 365 Admin account and store the credentials object in the variable for later use.

$credSP = (Get-Credentials "adminuser@mysite.sharepoint.com")

-This will pop-up a window to get the password for the SharePoint account and store the credentials object in the variable for later use. This account should have Site Administrator permissions on the destination site.

The Result

Import-Module Microsoft.PowerShell.Security
# SharePoint Site Administrator account
$credSP = (Get-Credential "admin@site.sharepoint.com")
# Office 365 Administrator account
$cred365 = (Get-Credential "admin@domain.onmicrosoft.com")
# Azure Storage information
$azureStorageAccountName = "storagename"
$azureStorageAccountKey = "accountkey"
#sources
$srcPath = "\\network share path\"
$pkgPath = "C:\temp\SourcePkg"
$targetPkg = "C:\temp\TargetPkg"
#subsite in SharePoint Online
$targetWeb = "https://site.sharepoint.com/subsiterootpath/"
#target library in the above site
$targetLibrary = "library name"
#Create package and upload to Azure Temporary Storage
New-SPOMigrationPackage -SourceFilesPath $srcPath -OutputPackagePath $pkgPath -NoAzureAdLookup
#Ready package for migration from Azure Storage to Destination
$sourceinAZstore = Set-SPOMigrationPackageAzureSource -SourceFilesPath $srcPath -SourcePackagePath $targetPkg -AccountName $azureStorageAccountName -AccountKey $azureStorageAccountKey -AzureQueueName "fs2sp"
#Submit above Package for Migration to Destination
Submit-SPOMigrationJob -TargetWebUrl $targetWeb -MigrationPackageAzureLocations $sourceinAZstore -Credentials $credSP

#The fully qualified URL and SAS token representing the Azure Storage Reporting Queue where import operations will list events during import.

$myQueueUri = <uri to azure report queue>

# In testing this doesn't appear to do much but is required to prevent errors

Get-SPOMigrationJobProgress -AzureQueueUri $myQueueUri

Going Forward

Script could be called as a function with the variables passed as parameters to allow uploading from multiple locations to multiple site libraries as a batch.

Error capturing, logging could be expanded and implemented.

Multi-select Choice or Lookup fields used in Cascading Dropdowns

The Problem

Cascading multi-select fields. Input form has multiple selection for Types and Specialties for a contact. The desire is to have another form in another library lookup the Types and Specialties in dropdown lists, allowing one type selection and a related, filtered specialty which then filters the list of contacts meeting both the types and specialty selections. Add in a bit of a twist, if the Types match a specific criteria then the Specialties are pulled from a different multi-select column on the same source list.

Lots of potential issues here!

The Approach

Create a new list with Type and Specialty columns of type ‘single line of text’

On the Contact list (with the two multiple select columns) add a workflow triggered onchange and on new.

Set a variable to the Types field values of type Lookup, comma delimited. Loop through the variable, looking for ‘,’ and pull the value from it to a substring variable, trim out the value and comma from the lookup variable. Enter a loop, use the substring to determine the cascade lookup field to use and set a variable to the values of the second tier lookup, pull out the value into substring2, enter a loop(2) and use substring and substring2 in a REST query to the created list – test for a match of both fields. If it exists, move to the next loop(2) value, if it does not match, add an entry to the list. This creates one entry for each type/specialty combination in the new list. At the end of the on-change workflow, set the value of another hidden text field in the contacts list to the display name value of the current record.

On the form to consume the cascading dropdowns add three fields of type lookup – point to the new list Type field and specialty field (do not allow multiple selections!) and a third lookup to the contact list hidden text field for name.

Add a content editor web part to the edit form. Use this to link to a javascript file in site assets. Import the SPServices library and the jQuery library in the head of the linked javascriptfile. On the first field (Types) use the SPServices.SPFilterDropdown and directly after it add the following to remove duplicates from the dropdown results:

var usedTypes = {};

$("[title='Type'] option").each(function () {

if (usedTypes[this.text]) {

$(this).remove();

} else {

usedTypes[this.text] = this.text;

}

});

Add a SPServices.SPCascadeDropdowns call for the Type->Specialty and another for the Specialty->Specialist cascades. Parent column and child column are from the form’s list, the relationship columns and field names from the lookup list (new list created above). In the specialist lookup use the contacts list / hidden name field as the lookup source.

The Result

List: Contact
Types (multiple select)
Specialty (Multiple Select)
Name - hidden text
- workflow that updates cascadelookups on new/change

List: cascadelookups
Type (single text)
Specialty (single text)

List: Forms
Type: Lookup to cascadelookups - cascades to:
Specialty: Lookup to cascadelookups - cascades to:
Specialist: Lookup to Contact Name hidden text field
- javascript included to work the cascade magic.

Going Forward

The update of the lookup list is addition only – if the users remove a specialty or type from a contact and there are no longer any contacts which match them the type field and specialty fields will still show the choices. I haven’t found a graceful way of removing these entries but a brute force method to remove null entries is to delete everything in the lookup list and then go to the contacts list and bulk edit the records, deleting the hidden name field values – triggering the workflow which then adds entries for each unique type/specialty pair. I’m sure there is a better way but I’ve not needed it yet.

Hopefully this helps someone a bit in using multiple select fields in cascading lookups.

Baer's Shared Points

Sunday 24 September 2017