All posts in SharePoint

Modifying the PowerApps Display Styles Based on Field Values

By default, browsing items in a list with a PowerApp the items all look the same.  This is fine in most cases, but I want to ensure the managers viewing the items can see items that are older first.  I could do this by modifying the search parameters for the browse screen to put the oldest at the top, but I want the older ones to jump out a bit.  So right now logging in, a user is going to see this:

Modifying the PowerApps Display Styles Based on Field Values - Default View

I want to modify the view so that the text of each is a different color depending on the age of the request and also how close the request start date is from the current date.  So initially I want the colors set based on the following criteria:

  • If less than 7 days old: Green
  • If more than 7, but but less than 14: Yellow (<– Yellow on white background is hard to see)
  • If more than 14 days: Orange
  • If the current day is less than 21 days from the start date: Red

This is where conditionals come into play.  Like all programming languages, PowerApps allows you to use conditional statements to control objects.  If…then…else, switch statements, etc are all available to allow you to conditionally manipulate your environment.  In this case I want to use conditionals to control how the text will appear.

The format of an IF conditional is If(<Condition>,<then>,<else>) or If(<Condition>,<then>,<elseif condition>,<then>,….)

For example: if I wanted to use an if statement for the text color of the user’s name I would put the following formula on the color attribute:

Today() is a function similar in function to Date.Now().  So what the statement above states is if the item created date is within the last 7 days, change the color to green.  Applying the function to the above list items will give us the second item as green for the Name field:

Modifying the PowerApps Display Styles Based on Field Values - Formatting The Name

If I wanted to do show a couple of my options above I would need to use an if\elseif conditional.  So for the Green and Yellow conditional I want:

Modifying the PowerApps Display Styles Based on Field Values -  Multiple Conditionals

So that’s just two conditionals.  As you can see it could get pretty long.  I would love to say that we could move to a switch statement using something like this:

However, currently only comparisons to constants will work. For instance if you did a math calculation of “NumDaysDif = Today() – ThisItem.Created” and had a result of 5 you could then do this in your switch statement and get the result you want:

Unfortunately this is not an option currently.  So to accomplish what I want to do I need to write a long if statement.  The final statement will look something like this:

And when added to all of the fields in the Browse Gallery your result should look something like this:

Modifying the PowerApps Display Styles Based on Field Values - All Fields Updated

Pretty straight forward once you get going.


Thanks for reading!


Using SharePoint Lookup Type Fields in PowerApp Browse Screens

In my previous post I discussed how to create a new PowerApp directly from a SharePoint list.  I also indicated it had a few limitations that needed to be addressed.  One of these limitations was the default browse screen is not compatible with lookup type fields (People\Groups, Choice, Lookup, etc).  These field types are of course available in the details and edit screens, but not the browse screen.

Using SharePoint Lookup Type Fields in PowerApp Browse Screens - Bad User Experience

I can force the control to look at the field I want, but it doesn’t know how to convert the object into text.

Using SharePoint Lookup Type Fields in PowerApp Browse Screens - spUser Not Compatible

Being limited to just the text fields of the list or at least fields PowerApps knows how to treat as text (Date\Time fields) was creating a bad user experience in the forms I wanted to create. I had to come up with another way.  Using a calculated field in the SharePoint list won’t work because a calculated field can’t see those columns either.  It’s actually

Using SharePoint Lookup Type Fields in PowerApp Browse Screens

Because I want to be able to see these values on my browse screen they need to be converted to text.  The two fields I want on my browse screen that are not compatible with the view are:

  • Training Requested For – The name of the person taking the training (Person or Group field)
  • Training Requested – Title of the training course (Lookup Field)

Luckily there are formulas that can help us out here.  To display the name of the user PowerApps has the ability to call the DisplayName property of the field.

Using SharePoint Lookup Type Fields in PowerApp Browse Screens - Display Name of Person Field

To get the title of the training requested is just another property of the field.

Using SharePoint Lookup Type Fields in PowerApp Browse Screens - Displaying Lookup Field

I decided to get a bit fancier and add some text at the beginning of the value to add to the experience.  I wanted the field to read: Course Name: <“Name of the Course”>.  To do this, you simply combine two strings together (the can be variable based or static (what you typed)) inside a Concatenate function.

The final values I wanted on the form were the start date, End Date and total estimated cost.  Dates were straight forward as they are available from the drop down of the data properties.  The cost I had to get a bit fancy here too.  I wanted the cost to display as Total Estimated Cost: $XXXX.XX.  The problem now is that the Concatenate function requires strings and the TrainingCost field comes back as a numeric.  Enter in a another formula.  Text(<string>, formula) will convert a string to a formula.  The final formula looked like this:

When all was said and done I ended up with my form looking like this”


PowerApps is still pretty new, but there is a lot you can do with it.  I look forward to seeing where it will get to in the near future.


Thanks for Reading!

Creating a PowerApp from a SharePoint List

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.

Read more

PowerApps – Could not resolve jwt token. Could not resolve issuer token

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:

Invalid JWT token. Could not resolve issuer token.

Could not resolve jwt token. Could not resolve issuer token - Error Message

The full error was:


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

Could not resolve jwt token. Could not resolve issuer token - Remove View

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.

PowerApps - Could not resolve jwt token. Could not resolve issuer token - Connection Error

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.

Thanks for reading!

Manipulating SharePoint External Lists using REST

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:

Manipulating SharePoint External Lists using REST - External 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.

Manipulating SharePoint External Lists using REST - Read List Item

Manipulating SharePoint External Lists using REST - Read Item Response

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.

Manipulating SharePoint External Lists using REST - External List Insert

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

Manipulating SharePoint External Lists using REST - External List After Insert

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”.

Manipulating SharePoint External Lists using REST - External List Item Update

You can see that the list item updated successfully.

Manipulating SharePoint External Lists using REST - External List After Update

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).

Manipulating SharePoint External Lists using REST - External List Delete Item

You can see the item is now removed

Manipulating SharePoint External Lists using REST - External List After Delete

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.


Thanks for reading!!