Archive for July, 2017

Using PowerShell to Access Data In External Lists

I created my  blog series on SharePoint BCS quite some time ago in order to illustrate how easy it is to access data outside of SharePoint and present it within a list or document library.  I have been meaning for quite some time to demonstrate how writing code for external lists\libraries is very similar to writing code for internal lists\libraries.  Today I am going to start a new three part series on SharePoint BCS development by showing you how you can enhance your solutions using PowerShell to access data in external lists.  The posts aren’t going to be too in-depth, but meant to get you started.  I hope to build on them later and post a series on creating an entire solution around BCS.

Using PowerShell to Manage Data in Internal Lists

With SharePoint PowerShell you are basically using the server object model for development.  So this means you have access to the methods and processes utilised by this system.  What you’ll notice is that code to perform inserts, updates, reads and deletions is pretty much exactly the same.  Where the code differs between internal and external is actually around accessing the item to be updated, read or deleted.

To illustrate I have created a script that performs an insert, update, read and delete.  To show you what’s happening here’s the config for the code:

 

<List ParentSiteURL="http://teams.drevsp2016.com/bcs/" ListName="DemoCode">
    <ListItems>
        <ListItem Action="Insert" ID="">
            <Fields>
                <Field Title="Title" Value="New Item"/>
                <Field Title="Description" Value="This Field was created using PowerShell and SOM"/>
            </Fields>
        </ListItem>
        <ListItem Action="Update" ID="1">
            <Fields>
                <Field Title="Title" Value="BCS is Really Awesome"/>
                <Field Title="Description" Value="This Field was UPDATED using PowerShell and SOM"/>
            </Fields>
        </ListItem>
        <ListItem Action="Update" ID="3">
            <Fields>
                <Field Title="Title" Value="Dave is a great speaker and should get a great review"/>
                <Field Title="Description" Value="This Field was UPDATED using PowerShell and SOM"/>
            </Fields>
        </ListItem>
        <ListItem Action="Read" ID="4">
            <Fields>
                <Field Title="Title" Value=""/>
                <Field Title="Description" Value=""/>
            </Fields>
        </ListItem>
        <ListItem Action="Delete" ID="2">
            <Fields>
                <Field Title="" Value=""/>
                <Field Title="" Value=""/>
            </Fields>
        </ListItem>
    </ListItems>
</List>

As you can see the code should insert a new item, update two, read a third and finally delete.  The code is pretty straight forward:

foreach($xmlItem in $xmlList.ListItems.ListItem)
{
    switch($xmlItem.Action)
    {
        "Insert"
        {
            $newListItem = $spList.AddItem();
            
            foreach($xmlField in $xmlItem.Fields.Field)
            {
                $newListItem[$xmlField.Title] = $xmlField.Value;
            }

            #Update the new list item
            $newListItem.Update();

            break;
        }

        "Update"
        {
            $updateListItem = $spList.GetItemByID($xmlItem.ID);

            foreach($xmlField in $xmlItem.Fields.Field)
            {
                $updateListItem[$xmlField.Title] = $xmlField.Value;
            }

            #Update the new list item
            $updateListItem.Update();

            break;
        }

        "Read"
        {
            $readListItem =  $spList.GetItemByID($xmlItem.ID);

            foreach($xmlField in $xmlItem.Fields.Field)
            {
                write-host("The Value of field '{0}' is '{1}'" -f $xmlField.Title, $readListItem[$xmlField.Title]) -ForegroundColor Cyan;
            }
            
            break;
        }
        "Delete"
        {
            $deleteListItem =  $spList.GetItemByID($xmlItem.ID);

            $deleteListItem.Delete();

            Write-Host("Successfully deleted item #{0}" -f $xmlItem.ID) -ForegroundColor Magenta;
            
            break;
        }
    }
}

Using the following list:

Using PowerShell to Access Data In External Lists - Internal List Before Code

And running the above script:

Using PowerShell to Access Data In External Lists - InternalListRunningCode

We end up with the result:

Using PowerShell to Access Data In External Lists - Internal List After Code

 

Using PowerShell to Update External Lists

The code is almost identical.  What actually changes is the code used for finding the items we want to update.  We also have to define the ServiceContext scope to process the transactions.  External list manipulation requires the scope to import the data catalogue (more information can be found here).  First the config to see what the plan is:

<Lists>
    <List ParentSiteURL="http://teams.drevsp2016.com/bcs/" ParentSiteColURL="http://teams.drevsp2016.com" ListName="Asset List">
        <ListItems>
            <ListItem Action="Insert" AssetID="">
                <Fields>
                    <Field Title="Type" Value="Printer"/>
                    <Field Title="Make" Value="Brother"/>
                    <Field Title="Model" Value="MJD-2500"/>                        
                    <Field Title="Details" Value="This Item Added Via PowerShell"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" AssetID="1">
                <Fields>
                    <Field Title="Location" Value="Denver"/>
                    <Field Title="Details" Value="This Field was UPDATED using PowerShell and SOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Update" AssetID="9">
                <Fields>
                    <Field Title="Type" Value="Monitor"/>
                    <Field Title="Make" Value="Samsung"/>
                    <Field Title="Model" Value="30inch"/>
                    <Field Title="Details" Value="This Field was UPDATED using PowerShell and SOM"/>
                </Fields>
            </ListItem>
            <ListItem Action="Read" AssetID="9">
                <Fields>
                    <Field Title="Type" Value=""/>
                    <Field Title="Make" Value=""/>
                    <Field Title="Model" Value=""/>
                    <Field Title="Details" Value=""/>
                </Fields>
            </ListItem>
            <ListItem Action="Delete" AssetID="22">
                <Fields>
                    <Field Title="" Value=""/>
                    <Field Title="" Value=""/>
                </Fields>
            </ListItem>
        </ListItems>
    </List>
</Lists>

Then using the code:

foreach($xmlList in $testExternalList.Config.Lists.List)
{
    #Define the SPServiceContextScope to access the Data Catalog
    $ctx = get-SPServiceContext $xmlList.ParentSiteColURL;
    $scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx

    $spWeb = Get-SPWeb $xmlList.ParentSiteURL;
    $spList = $spWeb.Lists[$xmlList.ListName];

    foreach($xmlItem in $xmlList.ListItems.ListItem)
    {
        switch($xmlItem.Action)
        {
            "Insert"
            {
                $newListItem = $spList.Items.Add();
                
                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    $newListItem[$xmlField.Title] = $xmlField.Value;
                }

                #Update the new list item
                $newListItem.Update();
                
                Write-Host "Successfully inserted new item" -ForegroundColor Cyan;

                break;
            }

            "Update"
            {
                $updAssetID = [int]$xmlItem.AssetID;

                #Use PowerShell query to find the item to update
                $updateListItem = $spList.Items | Where-Object {$_["AssetID"] -eq $updAssetID};

                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    $updateListItem[$xmlField.Title] = $xmlField.Value;
                }

                #Update the new list item
                $updateListItem.Update();

                Write-Host ("Updated AssetID: {0}" -f $updAssetID) -ForegroundColor Green;
                
                break;
            }

            "Read"
            {
                $readAssetID = [int]$xmlItem.AssetID;

                #Use PowerShell query to find the item to update
                $readListItem =  $spList.Items | Where-Object {$_["AssetID"] -eq $readAssetID};

                foreach($xmlField in $xmlItem.Fields.Field)
                {
                    write-host("The Value of field '{0}' is '{1}'" -f $xmlField.Title, $readListItem[$xmlField.Title]) -ForegroundColor Cyan;
                }
                
                break;
            }
            "Delete"
            {
                $delAssetID = [int]$xmlItem.AssetID;

                #Use PowerShell query to find the item to update
                $deleteListItem =  $spList.Items | Where-Object {$_["AssetID"] -eq $delAssetID};

                $deleteListItem.Delete();

                Write-Host("Successfully deleted item #{0}" -f $delAssetID) -ForegroundColor Magenta;
                
                break;
            }
        }
    }
}

Notice in the code above that we are using a pipe to gather the data.  One thing to note with External Lists is they do not have a numerical ID field.  However, in this case we are grabbing the ID field of the Asset so we can search on the AssetID of the item.

We then update the following list:

Using PowerShell to Access Data In External Lists - External List Before Code

by running the code:

Using PowerShell to Access Data In External Lists - External List Running Code

And receive the following updated list:

Using PowerShell to Access Data In External Lists - External List After Code

 

That’s the basics of updating an External List with PowerShell.  In the next post I will cover updating using CSOM.

 

Thanks for Reading!

Introducing Microsoft365

Remember last year Microsoft announced Secure Productive Enterprise?  Well, today at Microsoft Inspire (the rebranded Partner Conference) they rebranded it and I personally think it’s a great idea.  Welcome to Microsoft365.   In a nutshell, Microsoft365 is Office 365 and Windows 10 bundled together with Mobility for the Enterprise and all wrapped up in enterprise level security.  The following is my take on how the new product focus is going to help your business moving forward.

Microsoft365

Encouraging four main pillars the product is focused on, Satya Nadella made the announcement this morning (July 10, 2017).  Microsoft wants the tool to focus on Creativity, Teamwork, Simplicity, and Security.

Creativity

Creativity of course because with the product you will get all the tools you need to create great things.  You will get the same tools you have been using already like Word, Excel, PowerPoint, OneNote, etc).  With these, you can create everything you need in your day to day work.  From proposals to meeting notes to financial status with Microsoft365, you can create all this and so much more.

Teamwork

With Office365 you will be getting great tools to encourage and support teamwork.  SharePoint of course but now you can increase collaboration within your teams using other fantastic tools like Microsoft Groups, Microsoft Team, OneDrive for Business, Outlook and Exchange, Skype for Business, Yammer, and so much more.  Being able to accomplish tasks as a group has never been easier.

Simplicity

It’s all within one package for you.  No need to buy this tool for that job, or that tool for another job.  Everything you need is in Microsoft365.  And even if it isn’t (yet ;-D ) Microsoft has made connectors to many items for you to do just that.  Microsoft teams have connectors to social media like Twitter and Facebook.  Flow can also access these systems as well.  While it may seem less than simple and when first getting started it may very well be, but as you carve out your usage of Microsoft365 you will find having everything at your fingertips in one product couldn’t make it easier for you to get things done.

Security

With Microsoft365 you will be getting enterprise level security.  You will have Encryption, Data Loss Protection (DLP) and Information Rights Management all through Office365.  Thus fully securing your data from accidental loss, or being viewed by those who shouldn’t.  You will get great protection in your systems with Windows Information Protection, Credential Guard, App Locker and much more.  Remember that Office365 has released a fantastic security console that allows you to fully protect your environment even better than before.

What I think is fantastic is that you don’t have to purchase the items seperately.  As a business you now can group your most used individual products (Windows 10, business applications (Word, Excel, etc), collaboration tools like SharePoint, OneDrive, Skype, etc) and more all in one bundle.  It’s a great option for many businesses to use.  While not a huge new product for Microsoft, I think that the rebranding and launch of Microsoft365 is absolutely fantastic and looking forward to working with businesses to build out and support solutions utilizing this product.

 

Thanks for reading!!