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

 

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

Connecting to SQL Server using external content type

Click ‘External Content Types’ from the left panel then click External Content Type in the Upper Left portion.

aaa

On the New External Content Type page, specify the Name, Display Name, Namespace, and the Office Item Type, as shown in the following figure.

bbb

Click ‘Click here to discover external data sources and define operations’ link

ccc

Click ‘Add Connection’ button then choose ‘SQL Server’ as the Data Source Type.

ddd

In the connection properties specify Server and Database Name. Choose Connect with User’s identity then Click OK.

Right click on your table and Select Create All Operations.

vvwf

Click Next.

Click Save on the upper left portion then ‘Click Create Lists & Form’ in the ribbon.

Specify List name then Click OK.

Configuring permissions

Open your team site in the browser. You should now see your newly created list in your quick launch.

When you click on the list it will display as Access denied.

Open Central Administration. Click ‘Manage Service Application’ under Application Management.

fffgg

Click Business Data Connectivity Service.

ghiii

Select your List and click ‘Set Object Permissions’.

Add your account and select the necessary permissions then Click OK.

Go to the List page again and it should now be accessible to you. To verify read and update access to SQL db Go to Items Tab and Click New Item.

Enter new item details and Click Save.

jkl

The List page now displays the added item.

lmno

Verify that the table has been updated in SQL Server Management studio.

Cascading dropdown using SPServices

I have a region and countries list which will be my parent and child list. You will use your childs ListID and other parameters as shown below.

ImageImage

Since the cascading dropdowns I wish to implement are to be used in another list I will go to the Edit Form of this list and insert here the necessary script.

Image

You will notice that when you go to your edit form the Advance Edit mode is disabled.

Image

The trick of SPD is to enter newline before the uneditable codes which are highlighted and the Advance edit mode will be enabled.

Image

Insert yor SPService script here:

Image

XSLT List View Webpart Bug encountered

Hi All, I got this message prompt: “This item is no longer available it may have been deleted by another user. Click Ok to refresh the page.” when I click the ECB from the XSLT List View / XLV Webpart of a Library.

xlv bug xlv bug2

After further reading I have learned that this is a known issue in XsltListviewWebpart when you customized your XSL and place it on a seperate file using XslLink.

Workarounds available:
1. Place your XSL directly into the page

2. Deploy your XSL to the Layouts Folder of your SP site

First option is a quick No- No for me. You would not want all those hundreds of XSL lines to be dumped into your page. So this should be an easy pick for me.