November 20, 2020

Show values based on Lookup List Conditions for Lookup Column in Power Apps

Overview:

In this article, we will learn how we can show only specific values as Lookup value based on some conditions in Power Apps.

Real-Life Business Use Case:

We have come across a scenario where we have two SharePoint lists.

Domains List:


Request List:


 - Domain is a Lookup column from Domains List in the Request List.

 - We are implementing a Power Apps Form for Requests List.

 - We only want to show those Domains which are active, so how can we achieve this?

Let’s get started!


Step 1: Open Power Apps Form. By default, all values appear in the Lookup Dropdown.

Below is the code for the Default event which is available for the lookup dropdown.


Step 2: Now, we need to change the code with the following code.

 Filter(  
   RenameColumns(  
     AddColumns(  
       RenameColumns(  
         Choices([@Requests].Domain),  
         "Id",  
         "MID"  
       ),  
       "Status",  
       LookUp(  
         Choices([@Requests].'Domain:Status'),  
         Id = MID  
       ).Value  
     ),  
     "MID",  
     "Id"  
   ),  
   Status = "Active"  
 )  


Understanding the code:

 - By default, Lookup has two columns, ID and Value. With the above formula, we added one additional column –“Status” by using AddColumns() function.

 - To achieve this, we need to use the "RenameColumns" function to make the exact final structure that the lookup value expects.

 

- Now, let’s test the result. We can see only Active records in the dropdown.

 

- Now, let’s fill the form and save the value. 


 - The values are stored successfully in the SharePoint list.


Conclusion:

This is how we can apply a lookup list condition to show only active requests in the Lookup column. Hope this is clear now! Stay connected for amazing Power Apps Articles! Happy Power Apping!

If you have any questions you can reach out to our SharePoint Consulting team here.

November 12, 2020

How to perform Azure App Only Authentication with SharePoint in .Net Core (Azure Function)

Introduction:

In the .NET Framework, there were native classes like  "SharePointCredentials" and "AuthenticatinoManager" which were used for SharePoint Authentication, but now they are removed.

The major difference compared to the .NET Framework CSOM is that the authentication is completely independent of the CSOM library now.

Resolution:

.NET standard CSOM now uses the OAuth for authentication. So we need to get an access token and pass it along with the call to SharePoint Online.

So in this blog, we will learn how to perform Azure App-Only Authentication with SharePoint in new .NET Standard CSOM to get data from SharePoint Online.

When we are using App-Only Authentication, we will have two options:
1. Azure Active Directory App Registration with the client certificate.
2. Create SharePoint App using AppRegNew.aspx and AppInv.aspx.

Here we will discuss the Azure AD App Registration approach.

Step 1:

  • First, we need to register a new app in the Azure Active Directory.
  • Go to http://portal.azure.com/ and select "Azure Active Directory". Now from the "App registrations" option in the left panel, register a new app.


Step 2:

Now we will grant the required permission to the app we created in Step 1. Open the newly created app and click on "API permissions" and click on "Add a permission".

Now select "SharePoint permission" as per your requirement and grant the admin consent. Here we needed "Full Control" permission for SharePoint so we have added "Sites.FullControl.All" and "Sites.Manage.All" permissions.


Step 3:

    1. CertificateName.cert
    2. CertificateName.pfx
  • Consideration: Keep note of the password which you have used to generate a certificate.

Step 4:

  • Now we will configure this certificate with our Azure AD App. 
  • Go to your Azure AD App. Now click on "Certificates and client" from the left panel and click on the "Upload certificate" button.
  • On clicking the "Upload certificate", it will open a panel at the top and allows you to upload the file. Here you need to upload the ".cert" file.

Step 5:

  • Make sure you have selected all required permissions in API Permissions and admin consent has been granted for all required permissions.

Step 6:

  • Now we will create an Azure Function solution using Visual Studio 2019.


Step 7:


Step 8:

  • Now in the Azure Function solution, we need to read the ".pfx" file which is uploaded in our solution. To read the file from the Azure Function solution we need execution context.
  • We can get the execution context using the below parameter in the Azure Function:
          
 ExecutionContext executionContext  

  • Below is the code snippet for how to use execution context:
 [FunctionName("Function1")]  
 public static async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequest req, ILogger log, ExecutionContext executionContext)  

  • Now using this execution context, we can get the ".pfx" file as below:
 var Sfilepath = $"{ System.IO.Directory.GetParent(executionContext.FunctionDirectory).FullName}\\Certificate\\CertificateFilename.pfx";  

  • Here we have uploaded the file in the Certificate folder. So the Sfilepath variable we are reading a file from the Certificate folder.

Step 9:

  • Now we will get ClientContext using this Certificate File and Application ID.
  • To get the context, first, we need an access token.
  • To get the access token we need Application ID, Certificate File Path, Certificate File Password, Tenant ID, and Permission Scope.
  • Permission scope will be as below:
  public static string[] permissionscopes = { "https://tenantname.sharepoint.com/.default" };  

  • We will use the below method to get the access token:
 internal static async Task<string> GetApplicationAuthenticatedClient(string clientId, string Sfilepath, string certificatePassword, string[] scopes, string tenantId)  
     {  
       X509Certificate2 certificate = new X509Certificate2(certThumprint, certificatePassword", X509KeyStorageFlags.MachineKeySet | X509KeyStorageFlags.PersistKeySet);  
       IConfidentialClientApplication clientApp;  
       clientApp = ConfidentialClientApplicationBuilder  
       .Create(clientId)  
       .WithCertificate(certificate)  
       .WithTenantId(tenantId)  
       .Build();  
       AuthenticationResult authResult = await clientApp.AcquireTokenForClient(scopes).ExecuteAsync();  
       string accessToken = authResult.AccessToken;  
       return accessToken;  
     }  

  • We can call the above method as below:
 var accessToken = await GetApplicationAuthenticatedClient(clientId, Sfilepath, certificatePassword, permissionscopes, tenantId);  

  • Now the "accessToken" variable will contain the access token.

Step 10:

  • Now using the access token which we get in Step 8, we will generate client context.
  • We can use the below method, which will return the client context. We will need the Site URL for which we want the client context and the access token.
 public static ClientContext GetClientContextWithAccessToken(string targetUrl, string accessToken)  
     {  
       ClientContext clientContext = new ClientContext(targetUrl);  
       clientContext.ExecutingWebRequest +=  
       delegate (object oSender, WebRequestEventArgs webRequestEventArgs)  
       {  
         webRequestEventArgs.WebRequestExecutor.RequestHeaders["Authorization"] =  
   "Bearer " + accessToken;  
       };  
       return clientContext;  
     }  

  • We can call the above method as below:
 var clientCtx = GetClientContextWithAccessToken(siteUrl, accessToken);  

Step 11:

  • Now we have the client context of the Site URL which you have used in step 9.
  • So we can now use the client context for any operations we want to perform programmatically with CSOM.
 Web web = clientCtx.Web;  
 clientCtx.Load(web);  
 clientCtx.ExecuteQuery();  

Conclusion:

This is how we can authenticate to SharePoint from .Net Core with the use of Azure App-Only Authentication. 

If you have any questions you can reach out our SharePoint Consulting team here.