Archive for July, 2017

One of the recent innovations from Microsoft is the ability to integrate a PowerApp with a SharePoint list. What this means is you no longer have to create the PowerApp separately from SharePoint and then create connections to the environment, but instead you can actually create the PowerApp from within the list itself. What this doesn’t mean is that a PowerApp can replace the list forms for a SharePoint list. However, in a recent post from the PowerApps team we will be getting this functionality very soon.

In this post, I am going to show you how to create a PowerApp from a custom list and show you around the PowerApp development tools a bit. This post is actually the first in a series that I intend to use to show you the different things you can currently do with PowerApps. I intend to integrate Microsoft Flow as well later as well. Since this is the first post it makes sense to start with a brief explanation of the design and how to create the App.

Today I had decided to start a new blog series centred around Microsoft PowerApps. I decided I wanted to build out a quick little training request app on a SharePoint list. While starting out I ran into a bit of a snag while creating the first part of the PowerApp. But I will summarise how things occurred. I created the metadata I wanted to have in my list. The list contained about 9 different fields including a User\Group field and a couple of look-ups. When I attempted to create a Power App directly from the list I received the following error:

Going back to the list didn’t help any. Even though I didn’t have a PowerApp created, the list thought it existed.

Clicking “Remove this view” removes the PowerApp view from the list and doesn’t harm the metadata.

I thought at first I had a badly configured field, but nothing seemed out of place. All of the fields were populating data as expected and the data saved successfully with the list forms. Reviewing the error a bit closer it looked a lot like an access issue. Problem is this is my tenant and currently I have only 3 accounts. The one I was using was the global admin, so it shouldn’t have been that.

I then took a look at my PowerApps Admin console and noticed an exclamation point on the Connections menu. Clicking on it, the newly created connection indicated the password needed to be updated.

Clicking on the “Update Password” option opens a dialog to the Microsoft portal. I was then able to update my portal. Next time I attempted to create my app it worked like a charm. I’ll talk about that next week. Hope this helps someone with a similar issue.

In this third post on accessing SharePoint External Lists we are going to be manipulating sharePoint external lists using REST APIs. You can view the previous two posts of this series by clicking on one of the following links:

Of all the methods of coding against external lists I would have to say that REST is probably the easiest. You don’t have to create any complicated CAML queries to find items to read, delete or update as you would with CSOM. You don’t have to add Service Context scope as you do with PowerShell. In fact, with REST you can actually access data using the ID of the list item.

“But Dave, I thought you said external lists don’t have IDs”

Actually, they do. However, as I said in the first post, the do not have Numerical IDs. The IDs are actual text based. Once fantastic about SharePoint REST API is you actually have a method you can call named: GetItemByStringId. This method is exactly as it sounds. Using it you will be able to get the internal ID of the list item generated when the item is added into the external list.

Unlike my previous posts I am not going to show you how to access internal lists and then compare the external access. There isn’t a lot of code to write for this as I just want to give you the basics and Fiddler allows for that perfectly. There is a great post on how to work with internal lists on the Office Dev Center. You can access that here.

Manipulating SharePoint External Lists using REST

I decided that because I had done my previous posts using SharePoint on-prem that this time to show it doesn’t matter which environment you develop against, I am actually updating lists in my SharePoint Online tenant.

Just a quick disclaimer: You will notice that all of my Fiddler screenshots contain a cookie entry in the Request Header. This is simply for accessing SharePoint Online via Fiddler. It is not required for SharePoint on-premises.

For the purpose of this post I will be working with the following list:

Note I have included the BDC Identity column. For external lists, this is the item id.

Read Items

To read an item from the list we will use the endpoint: “_api/Web/Lists/getbytitle(‘<ListName>’)/GetItemByStringId(‘<item id>’)” Special note: when using the BDC Identity, ensure it starts with a double underscore.

Insert New Items

To insert data into an external list is actually exactly the same as it is for an internal list. The endpoint: “_api/web/lists/getbytitle(‘SmallAsset’)/Items” is used here. Having data in the Request Body (seen in the picture below) is what tells SharePoint to insert the data.

You can see that line 10 was added to the list:

Update List Item

As discussed above, getting an item from an external list is a bit different. With external lists you have to use GetByStringID instead of simply placing the item id after the indicating you are accessing items from a list in the REST call. The call to the endpoint is similar to: “_api/Web/Lists/getbytitle(‘<List Title>’)/GetItemByStringId(‘<BDC Identity>’)“. In the following I want to update AssetID#3 and set the location to Regina and the Details to “Updated with Rest”.

You can see that the list item updated successfully.

Delete List Item

Deleting a list item is very similar to updating an existing item. You simply need to change the http method to “DELETE” and remove the request body. The call to the endpoint will be the same as the Update one in fact (“_api/Web/Lists/getbytitle(‘<List Title>’)/GetItemByStringId(‘<BDC Identity>’)“). You can see in following screenshots that I deleted the entry that indicated it was added by Fiddler (AssetID: #9).

You can see the item is now removed

That’s the basics of working with External Lists and REST APIs. As you can see it’s really easy. Each of the methods I illustrated today could be moved to on-prem SharePoint simply by changing the URL and nothing else.

This is the second part in my series on developing code to modify external lists. In this post we will be updating SharePoint external lists using CSOM. Like standard PowerShell there is not any real difference in the execution of the update between internal and external lists. What is different is how the data is obtained when performing an update, read or deletion. Like PowerShell, in CSOM you can find the item you wish to access using the ID. However, because there isn’t a list ID field in an external list you have to query to find the item(s) you need. You could loop through each item in the list until you find one with the particular attributes you are looking for, but that is not efficient and it may be difficult if the fields you are checking have multiple rows with the same values. So brush up on your CAML query.

Using CSOM to Manage Data in Internal Lists

CSOM has come a long way since its inception in SharePoint 2010. I would still be recommending it over REST except that unlike REST things are a bit different between on-prem and SharePoint Online CSOM versions (you need a different versions for each). This post isn’t meant as a discussion on the pros and cons of REST vs CSOM though (You can get more information here if you are curious though). CSOM is probably a little more complicated than the PowerShell examples I provided in my previous post, but one you get the hang of loading the data you need before working with it the process is pretty easy.

To show you what we are going to be doing here’s the list we will be starting off with:

<Field Title="Details"Value="This Field was UPDATED using PowerShell and CSOM"/>

</Fields>

</ListItem>

<ListItem Action="Update"AssetID="29">

<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 CSOM"/>

</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="32">

<Fields>

<Field Title=""Value=""/>

<Field Title=""Value=""/>

</Fields>

</ListItem>

</ListItems>

</List>

</Lists>

Reviewing the code, note the insertion of the CAML Query to find a specific item. One important thing to note with the CAML query. You have to use the name of the function used to define the Read-List process in the External Content Type

So again, other than gathering the item to update, managing items in an external list is very similar to managing items in an internal list. My next post we will cover the differences in manipulating internal and external items with REST.

David humbly considers himself a SharePoint JoAT (Jack of All Trades). He is the SharePoint Services Lead at Solvera Solutions, and an Office Servers and Services MVP. Over the last 16 years, David has worked in a wide variety of areas in IT ranging from DesktopServer support to developing in C++ and .NET technologies. Over that period, David has been able to add value to a wide variety of clients in several sectors, including utilities, government, banking, and agriculture.

Since early 2010, David has been working exclusively in the SharePoint domain. He has helped stand up multiple Enterprise SharePoint environments as well as designed and built numerous SharePoint solutions. David’s extensive experience in SharePoint is complemented by a range of SharePoint training courses and Certification in Microsoft SharePoint 2010. He is a knowledgeable and sought-after international speaker within IT circles and consulted regularly by companies formulating their SharePoint strategies.