Power Apps – Get Values from Multi-Select Fields

Most of the times in custom list forms or in web power apps you have a requirement to perform an action based on values selected in a field. It is very straightforward for simple fields like single line of text, number, single-select drop-downs but gets little tricky if the field is multi-select. For example, in a demo application, I want to enter my details along with my hobbies. Let’s say the condition is; if “Reading” is one of my hobby, I need to ask user his/her preferred book format. If not, this field should not be visible. Below post is about achieving this.

Currently the form looks like this:

I need to display “Book Format” field only when I select “Reading” as one of the “Hobbies”.

  1. The first logical step is to record the selected hobbies in a variable. We can bind it to the “OnChange” event of the “Hobbies” drop-down. It can simply be achieved by using the UpdateContext function

    UpdateContext({SelectedHobbies: HobbiesDropDown.SelectedItems})The above statement declares a variable named “SelectedHobbies” and set its value to selected items in the drop down list. I have renamed the data card value to “HobbiesDropDown” for ease of use.

  2. The above variable “SelectedHobbies” is of “Table” type. If you are confused, you can confirm the type of variable by selecting the “File” menu item and checking “Variables” section
  3. Now it is just a matter of checking if “Reading” is one of the value in “SelectedHobbies” table variable and then set visible property of “Book Format” drop-down accordingly. To check this, you can use LookUp function present in Power Apps. I used the below query:If(LookUp(SelectedHobbies, Value = “Reading”, Value) = “Reading”, true, false)

    What it says is, if “Reading” is one of the “Value” present in the “SelectedHobbies” table, then set visible property of “Book Format” to true else false. Notice the use of “Value” keyword here. LookUp searches the given collection (first parameter), based on a condition (second parameter) and return the field you want (third parameter) satisfying that condition. In our case, if “SelectedHobbies” collection has Reading as its one of the values, then return that particular value i.e. “Reading”.

  4. That’s it and you are done. Run your application to check.”Reading” not in the hobby list

    “Reading” selected as hobby:

Simple enough. I was going through multiple ways to do it when I stumbled onto the “Value” keyword. I did not find much documentation on this and hence the blog post. Needless to say, same behavior applies to power apps list form too.

Also, you can either replicate the same behavior in View and Edit form or use the Set function to create a global variable instead of UpdateContext which you can use across the app.

P.S. The above will not work for multi-select people picker column. It works for Choice and LookUp fields only.

Hope it helps!

Power Apps – An entry is required or has invalid value

Recently I started exploring Power Apps in more depth. Its a nice tool but not without some issues. My scenario was to create a power app based on a SharePoint list which uses a custom content type. All the fields in the content type were optional. But still when I tried to save the form, it gave classic “An entry is required or has invalid value” exception.

Generally this error means that a required field is missing a value but I have no such fields in my content type. Also, strangely, I was able to save an item using the default new list item form.

Update (July,2018): I tested this again and now even the default list form throws error.

All the fields in the power app has “Required” property set to “false”. All the fields in the custom content type were “Optional” and I had only one content type present on the list.

After wasting almost 2 hours on this, I realized that power app, unlike default new list item form, does not take content type “Required/Optional” attribute into account. It gives preference to column settings. So in my case the erroneous field was “Title” column. It was optional in content type but not in column settings.





As soon as I set this to “No” in the column settings, my power app worked perfectly. You might have some other column like this, so please check.

P.S. I encountered the same for Power Apps list forms.

Hope this helps!

Access Denied on O365

Recently while working on a utility, I faced a very unusual error. I was trying to remove default documents from document set using managed client object model (Default Documents) and it was giving me classic “Access denied. You do not have permission to perform this action or access this resource” error, even though my account had site collection admin as well as tenant admin rights. Besides that, I was doing other operations on the same document set and it was working perfectly fine. One thing to notice was, this error was present only in root site collection and not in another site collections.

After searching a lot, I realized that this is happening due to a certain setting at tenant level and that was “Custom Script”. So if you navigate to SharePoint admin center and click on Settings, there is a section for custom script which says “Prevent users from running custom script on self-service created sites”. This is enabled by default and you need to “Allow users to run custom script on self-service created sites” for this to work. Also note that this change may take up to 24 hours to take effect.


I am not sure about the reason behind this behavior but hope this helps someone.

Besides access denied issue, other issues which I faced if custom script is turned off for root site collection are:

  1. I was not able to open root site collection in SP Designer and it was giving me “Forbidden” error. The complete error was “you do not have permission to open this web site in sharepoint designer
  2. Content Editor and Script Editor web parts were not available in the root site collection.

    Once this feature was turned on, both of the issues were solved.

Happy coding!

Standalone SharePoint Add-In

This post is about developing and deploying an application which works both as a provider hosted add-in as well as a standalone application depending on the URL from which it is launched. So, if a user logins to O365 and click on the app, it will open a provider hosted add-in and follow O365 authentication and if I launch the app directly using URL, then it will ask me to login using ASP.Net credentials and will follow ASP.Net Identity Framework.

It took me some time to decide on the approach as normal ASP.Net application uses AuthorizeAttribute while SharePoint uses ActionFilterAttribute and TokenHelper.cs class which comes by default while creating provider hosted add-in. This post assumes that the reader is aware about:

  • Provider Hosted Add-ins
  • ASP.Net Web Application projects using Identity Framework
  • Basics of MVC

Below are the steps I followed to achieve this.

  1. Create a provider hosted add-in using Visual Studio. You will end up with the below project structure:


  2. Install relevant nuget packages. Some of these will install other packages on which they are dependent. I installed the following:
    1. Microsoft ASP.NET Identity Core
    2. Microsoft ASP.NET Identity Owin
    3. Microsoft ASP.NET Identity EntityFramework
    4. Microsoft.Owin.Host.SystemWeb


  3. Add relevant files in the project. When you create an ASP.Net MVC application, there are few files which gets added by default. Following is the list of such files which I added in the project:
    1. App_Data/IdentityConfig.cs
    2. App_Data/Startup.cs
    3. Controllers/AccountController
    4. Controllers/ManageController
    5. Models/AccountViewModel.cs
    6. Models/IdentityModel.cs
    7. Models/ManageViewModels.cs
    8. Views/Account -> complete folder
    9. Views/Manage -> complere folder
    10. Views/Shared/_LoginPartial.cshtml

      P.S. While copying all of these files; make sure the namespace of classes are correct


  4. Create the tables to store user credentials in your database. You can find the script here or download from internet. Run this script in your relevant database and add connection string in web.config.
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=AuthenticationProviderHosted;Integrated Security=True" providerName="System.Data.SqlClient" />

    Also add this connection string name in IdentityModel.cs file.

  5. The final step is to add a common filter attribute which will work with both Sharepoint authentication and asp.net authentication. For this I created a new class called CommonContextFilterAttribute which inherits from AuthorizeAttribute. The code is simple here, if request has host URL present then authenticate using SharePoint else move to asp.net.


  6. In the HomeController.cs file change [SharePointContextFilter] to [CommonContextFilter]. Also, modify the Index action to handle the SP User class. I just added a host URL filter again to check if it is SP user or ASP.Net User. And you are done!

Try running the app as provider hosted add-in, just click on “Start” at the top and you will get the below screen


And if you run as standalone app then you will get below screen:


You can register a new user here and login to get the desired screen:


Notice the user name here i.e. garima@outlook.com. I used this login to register a user and then logged in to the site.

Similarly, you can modify the code to integrate login through Facebook, Twitter or any other website using OWIN.

You can download the complete source code from here.

Note: To debug the add-in as a standalone application, please make the following changes:

  • Set the web project as start-up project
  • In the “Properties” of web project -> “Web” tab -> select “Current Page” as “Start Action”.
  • Revert these changes to debug as SP add-in.
  • You can deploy this project as a normal provider hosted add-in and it should work both ways. I tested by deploying the web project in IIS. Skipping the steps here as it is straightforward.

Hope this helps. Happy coding!

O365 OneNote API in Web Application

Microsoft recently announced OneNote API for O365 notebooks. I decided to give it a try but most of the examples/documentation I found was for native client applications which is obviously not working for web apps. After spending a lot of time on it and getting help from Microsoft OneNote API team, I was able to resolve it. Below are the steps you need to follow to access your notebooks using OneNote API for O365. Please note that the below code is for single tenant app and OneNote API are in preview.

  1. Register your application in Azure AD: We need to register the app in Azure AD as well as give OneNote API proper permissions. The steps are as follows:
    • Go to Azure AD related to your O365 account

      Azure AD
    • Navigate to Active Directory and click on Applications tab. Add a new application and select “Add an application my organization is developing”NewApplication
    • Give proper name to the app and select Web Application/OR Web API optionWebAppORWebAPI
    • Provide a Sign-On URL and a unique App ID URI.
      • Sign-On URL: https://localhost:44327/
      • APP ID URI: https://<tenantname>.sharepoint.com/SingleTenantOneNoteAPIDemo, replace <tenantname> with the name of your Office365 tenant.
    • Now the new application is ready. Go to “Configure” tab and at the bottom you will get the option to manage permissions. Azure AD already has “Enable sign-on and read user’s profiles” permission.ExistingPermissions
    • Click on “Add application” and select “OneNote” and give appropriate permission.OneNotePermission
    • In “Keys” area select 2 years. You will get a message that “The Key value will be displayed after you save it”. Make sure to copy this key value as this is client secret for your application. Also, copy the client id.Keys/ClientSecret
    • Now go to “Reply URL” and add a new one which will correspond to the address in which you will write the code to handle the return flow.ReplyURL
    • Click on “Save” and DON’T FORGET TO COPY KEY VALUE AND CLIENT ID. That’s it, your application is configured in Azure AD now.
  2. Access OneNote API in code
    • Open Visual Studio -> Click on “New Project” -> Select ASP.NET Web Application and give a proper name to your application. Click on OK.
    • In the next screen, select “Change Authentication” and click on “Organization Accounts”. Provide domain name of your O365 account as well as APP ID URI which you used while configuring the app in Azure AD.ChnageAuthentication
    • Click on “OK” and you will be asked to login to the O365 account. Login using the account you used while creating the entry in Azure AD and hit “OK”.
    • Your project will be set up. Open the web.config and you will notice tenant related entries. In the “appsettings” section enter the other details like client id and app key. You can directly use these in code or add them in web.config. I usually prefer web.configweb.config
    • Add nuget for ADAL in your project.ADAL
    • The code for accessing the OneNote API is given below. Please note, that here I am using AcquireTokenByAuthorizationCodeAsync method for authorization.

This is just basic code which will return all your notebooks. You can also create/edit the notebooks based on the permissions you provided to the application in Azure AD. You can change these permissions later.

My next step is to achieve this in multi-tenant app. As apparent from the code, the authorization URL contains the tenant URL which is not possible in multi-tenant apps. I will soon update the post for this scenario.

Lastly, I would like to thank Microsoft OneNote API team for their help while I was stuck in these issues. Apart from that, please go through Using ADAL’s AcquireTokenBy AuthorizationCode to Call a Web API From a Web App post, it explains the process in more detail.

Hope this helps. Happy coding!

Update Aug 4, 2015: For using OneNote API in multi-tenant app, go through WebApp-WebAPI-MultiTenant-OpenIdConnect-DotNet example. This is for graph API but works as it is for OneNote API too. A very good and self-explanatory sample.

Manage Licenses in O365 using Powershell

Few days back, I got stuck in a weird issue while creating users in O365. For some of the users, the services like SharePoint, Exchange etc. were displaying “setting up…” status. I checked and was informed that it might take up to 4 hours for services to set up but even after 2 days, the issue was not resolved. I tried to remove and assign the license again for one of the user and it started working. As I have lots of users, I can not do it manually so eventually I ended up using powershell.

This post will take you through the process I followed while trying to resolve this issue and also the powershell script.

  1. The first step was to install Microsoft Online Services Sign-In assistant and Windows Azure Active Directory (Azure AD) module for Windows Powershell in my system. Both are 64 bit versions and you can find more details about it here.
  2. After that launch the powershell and connect to the tenant which is facing the issue using the Connect-MsolService command. It will ask for your O365 credentials.
    Powershell Credential Window
  3. To get the AccountSkuId and included ServicePlans for your tenant, you can use the following command:
    Get-MsolAccountSku | Where-Object {$_.SkuPartNumber -eq “yourpackname“} | ForEach-Object{$_.ServiceStatus}
    Account Details
    As you can see in the above screenshot, we now have tenant’s AccountSkuId and all the service plans with their status. Similarly, we can get the status of service plan assigned to all the users. For faulty licence assignment, usually the provisioning status is “Pending Input”.
  4. Next step is to create a CSV of all the users for which this issue is present. Let’s say the name of CSV is UsersList.csv.
  5. Run the below powershell command to remove and assign licenses again:
    $allUsers = Get-Content "{PathofFile}\UsersList.csv"
    :Outer foreach ($user in $allUsers)
      Write-Host($user) -foregroundcolor "magenta"
      $IsFaulty = "false";
      $O365User = Get-MsolUser -User $user
      $Licenses = $O365User.Licenses[0].ServiceStatus
      :Inner foreach($lic in $Licenses) {
         If ($lic.ServicePlan.ServiceName -eq "SHAREPOINTSTANDARD_EDU" -and $lic.ProvisioningStatus -ne "Success")
           $IsFaulty = "true"
            break Inner
     If ($IsFaulty -eq "true")
       Set-MsolUserLicense -User $user -RemoveLicenses {AccountSkuId:screenshot2}
       Set-MsolUserLicense -User $user -AddLicenses {AccountSkuId:screenshot2}

The above code first reads all the users from the CSV file. After that it checks their SharePoint service status and reassigns the licence if the status is not equal to “Success”. You can check for any other service which is applicable to you.

The name of services are different for different plans so make sure to execute step 3 for exact service names. One important thing to note here is, if you remove the license, users might lose all of their data. In my scenario, the users were newly created so that was not an issue, but if that is the case don’t run this script.

Hope this helps!