Working with Sharepoint On-Prem data with PowerApps

To work with Sharepoint On-premise data you will need On-Premise data gateway. It acts as a bridge to your data to work with it on Power BI, Microsoft Flow, Logic Apps, and PowerApps services. Sounds fantastic, eh? Not so much.

 

Exploring this option I found caveats on using On-Prem Sharepoint with PowerApps.

 

Many columns are not available in Sharepoint On-Prem but available in Sharepoint Online

  • The list attachment – The list attachment column is not available in On Prem but available in SP Online. Sure you can Add/update/delete list items from your PowerApps form to Sharepoint On prem however you will not be able to add an attachment.

 

  • The link for an item in a document Library – Let’s say you are working with a document library and you want to view/download that item using the direct link to the document. It’s not available in On-Prem but available in SP Online.
  • onprem link
    Limited available OOB columns in Sharepoint On-Prem
    powerapps direct link
    Sharepoint online shows more available columns you can use

    Clearly the On-Prem(2013 and 2016) is behind the Sharepoint online and would present problems when working with PowerApps and/or Flow. Hopefully on the upcoming 2019 version we’ll see them addressed. 🙂

Document Generation in Office 365 using Nintex

“Document generation” action in Nintex workflow is one of the new features in Nintex workflow only available on Office 365 for supporting PDF, Word, Excel, Powerpoint generation.

With this action I can:

  • Specify the Template the action is going to use for filling in workflow data
  • Select the output type. (DOCX, PPTX, PDF or XLSX)
  • Specify the output library of the file generated
  • Specify resulting file name (useful in knowing doc path for email attachment)

 

In Figure 1.2 You can see how the template looks like and how workflow variables can be used to populate data. On the right side, is the Document tagger to help you insert available workflow variables.

2016-08-25_142138
Figure 1.2: Word template where workflow data will be inserted

 

In Figure 1.3 you can see how to configure your document generation action.

doc gen config
Figure 1.3: Configure “Document generation” action

 

In this particular scenario when the Person in charge approves the item, I can attach the generated PDF document in an email to send a copy to the user and manager. We will be using the output variable for creating document path when attaching to an email.

send email
Figure 1.4: Attaching the generated document in email 

 

 

The List and it’s Lookup

We have our main list “Inspection Report” which has a lookup to “Department approver” list. On the Nintex form we can include our attachments such as pictures and relevant files.

Task assigned for approval and notification email with attached generated Document:

 

 

All approved/generated documents go to one library

approved inspection reports

 

An overview of the finished workflow process:

Pros and Cons:

Although this is a really cool addition that made hard tasks seems a breeze now. There are still improvements I’d like to see in the future releases:

  • It would be nice to support inserting images dynamically in the template.
  • There are still unsupported inline functions for manipulating string that are available in On-Prem but not on Office 365.

Licensing

Document Generation has a separate annual subscription for different needs (6000, 12000 and 120000 document generations) and 50 for  trial versions. You may contact Nintex for updated pricing and other inquiries.

Thats it! Enjoy Nintexing!

Simple Approval workflow using Nintex for Office 365

Requirements: Create a one level of approval for requesting office supplies. Send an Email to the user when his request has been sent for approval and send an email when the request has been rejected or approved.

In this scenario we will be creating a custom list with the following fields:

  • Item requested (text)
  • Quantity(text)
  • Manager(People)
  • Supplies type(choice)
  • Details(text)

 

custom list

Now we start creating the workflow. Click Nintex workflow. create nintex workflow.png

From the Workflow Designer click workflow settings, assign name to the workflow and choose “start automatically when item is created”.

workflow settings

Now we proceed creating the steps of our workflow approval.

Step 1: The first workflow action that we will add is “Send an email”. You can add this by dragging from left pane to the node after start process. This will notify the user once the request has been sent for approval.

Step 2: Let’s configure the email details by double clicking the “Send an email” action.

Step 3: Now let’s add a “Set workflow status” action to update status of our workflow. We will set the status to “Pending Approval”.

Step 4: Start assigning a task to the current item’s approver/manager field by adding a “Start a task process” action. Add the manager field as participant. Allow lazy approval.

task process participant

Step 5: Add “Set workflow variable” action on each branch and set our variable approvalStatus to “approved” and “rejected” accordingly.

build string

Step 6: Add “Build string” action to compose our email message using the variable “approvalStatus” and name the output as “emailMessage” variable.

 

Step 7: Now we could set the “status of the workflow” and “Send an Email” using our variables.

Step 8: Save and Publish workflow.

Here is an overview of the finished workflow:

Finished workflow

Creating new item and testing workflow:

 

Thats it! Its really pretty straight forward.

 

 

 

 

 

Sharepoint 2013 development on Azure VM

Running a Sharepoint 2013 development environment will need a decent machine with at least 12GB of RAM (recommended) and multiple cores for faster development experience. If you are considering developing on sharepoint 2013 farm then you should definitely look at using Azure VM.

The advantages:

  • Access from anywhere
  • Reduced cost in the developer machine( RDP on a lesser machine)
  • Developers can become more productive(less time spinning up development VM)

 

Setting Up Your Sharepoint Farm in the cloud

Luckily for Premium MSDN subscribers there is an Azure developer image that comes with SharePoint 2013 and Visual Studio 2013 Ultimate RC pre-installed. The image also contains scripts to provision and configure SharePoint 2013 with or without an Active directory running on a different machine. This post will show you how to create a new virtual machine in Azure using the image.

Step 1: From the Windows Azure Management portal click Virtual Machines > New

step1

Step 2: Choose From Gallery

step2

Step 3: Choose Visual Studio Premium 2013 Update 5 (which includes Sharepoint, SQL and configuration scripts)

step3

Step 4: Choose Basic Tier (suited for dev purposes) and choose A4(Sharepoint requirements)

step-4

Step 5: Choose your DNS name(for RDP)

step5

Step 6: Confirm

step6

Step 7: Wait for the provisioning to finish(approximately 10 minutes) then connect

step-7

Step 8: Click on the Configure developer desktop shortcut and install the SQL server from the install folder

step-8

Step 9: Run powershell and the script ‘configuresharepointfarm.ps1‘ . It will ask for SPFarm Account Name and account password. (Approximately another 10minutes)

step-9

Once the script is finished you will see it launching the Sharepoint site collection. You are now ready for Sharepoint development in the cloud.

step-10

With great power comes great responsibility

We all know this famous line from Spiderman and the same is true with azure. Keep in mind that you pay for the VM by the hour from the moment it is running. Always shutdown your VM after using. As of writing time the A4 instance pricing is $0.696/hr or roughly $6 for 8 hours of compute time according to their site.

 

Conclusion

When you have an MSDN subscription, the SP2013 Azure VM is an excellent option to jump start your development quickly and start developing from anywhere. You can save lots of time by using the image that has SQL Server, Sharepoint, configuration scripts and pre-installed with Visual Studio.

What are your thoughts? Do you think it can give you less gray hair setting up and developing? Can it replace your local development?

Resources:

Create SharePoint 2013 development environments quickly on Azure

SharePoint 2013 in Azure: A Dev Environment in 15 Minutes or Less

Azure Virtual Machine Pricing

 

Powershell + CSOM

The following is a snippet for getting all your site collection and sites in each site collection in Office 365 which can be achieved with help of CSOM. (PnP Powershell commands is also worth checking out)

I recommend the Pluralsight course by Adam Bertram on powershell advanced functions on pluralsight for those wanting to get quick deep dive of functions, parameters in Powershell.

Building Advanced PowerShell Functions and Modules

 

Getting Started with Grunt on Visual Studio

grunt up and running

What is Grunt?

Grunt is a build tool that speeds up tasks by reducing the effort required to prepare your source files for production. You can think about it as a robot to do your manual tasks. 🙂

How can Grunt help?

We have been working on this large scale AngularJS app for some time now and so are the laborious repetitive tasks we have to do everytime we are deploying on different environments. Prior to using Grunt we used to minify files with Visual Studio Web Essentials. What has improved though after implementing Grunt is the automation of series of tasks we do like changing constants from different environment, combining multiple js files into single file, minifying, linting, annotating angular controllers(more about this) and the time saved from automating this tasks.

 

Grunt on Visual Studio

If you’re using Visual Studio 2015 you don’t need to install anything as it comes built in with the 2015 version. However if you are using Visual Studio 2013 you will need to install the following:

Node and NPM: https://nodejs.org/en/download/

Visual Studio 2013 extensions: Grunt Launcher and Task runner explorer

 

Power query issue in office 365 when site is not in english

d43660555a8e000f99d200f449f6035b

I was really excited to get my hands on Power BI on Office 365 for the first time but then a few moments later I got into a black hole.

Power Query is not returning any data from a sharepoint list. After hours of finding my way and looking around the web. I finally  came across the answer to this issue and I have found out that this is a present bug on non-English site.

Issue Description: Power query fails to get data from sharepoint List when site is not in English

References:

Power Query error when connecting to Office 365 SharePoint site Use English as your #0365 (sub) site’s default language or else…(use oData)

power query

 

**************

Update:

Turns out Odata Feed as an alternative is the better way to go.. 🙂