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

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

Determine the current user in a SPS 2010 workflow

Notes for SP workflow:

SharePoint Dragons

What to do when you want to determine the current user in a workflow? It’s possible that the workflow runs inside the w3wp.exe worker process, in which case the current user makes sense. But it’s also possible that a workflow runs inside the owstimer.exe process, at a later time. In these cases, the current user doesn’t make sense. Instead, use the workflow originator or initiator.

To get the e-mail adress of the originator user:

workflowProperties.OriginatorUser.Email

Or

workflowProperties.OriginatorEmail

To get the display name of the originator user:

workflowProperties.Originator

Or

workflowProperties.OriginatorUser.Name

To get the login name (format [domain name]\[user name]:

workflowProperties.OriginatorUser.LoginName

These techniques can be used in workflows that are created programmatically. In a SPD (no-code) workflow, you can also retrieve the workflow initiator’s e-mail address: use the User Profile web service as a data form web part in the .aspx page of the workflow to get the e-mail address whenever the…

View original post 22 more words

Missing Lookup field when created using imported List template

During deployment of a Sharepoint 2010 site components I was  importing lists when this issue popped-out:

Missing lookup

It turns out that when you create list template with lookup fields it references the list’s GUID from source site and importing it to destination site the list GUID has changed and must be updated accordingly.

What to do now?

This won’t be a problem if you only have a few items on your list. You might as well delete the list and recreate them with the missing lookup relationships. However this may not be the best option if we are talking about couple of lists and columns with the same lookup.

I bumped into this blog by Adis and luckily managed my way around this issue by editing manifest.xml.

Inside the manifest.xml look for the <fields> tag you need to edit for all fields with type as lookup.

Modifying Manifest xml  

you can replace the List attribute by the new GUID in the destination site


 <Field ... Type="Lookup" List="{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}">

 

or use the List Name 


<Field ... Type="Lookup" List="Lists/NameOfListToLookup"/>

Few more guides

On editing .stp files and recreating them back to .cab formats I followed this blog.

And believe it or not I needed to google again inorder to find out how to replace .cab file back to .stp

Here:

      1. Open Folder Options, and click on the View tab
      2. Uncheck the Hide extensions for known file types box, then click on OK.
      3. Now the .cab extension at the end of the file is now editable.

 

 

Top Contributors From a document Library using xslt

Requirement: Display the top contributors / authors in a document library with no server-side code but using xslt.

mysort

I searched all over the web and asked in forums but I couldn’t find something close to what I would like to achieve  so when I figured it out I decided that this needed to be posted.  So here is how I achieved my requirement:

Approach:

What has to be done basically is to get all distinct / unique authors from the document library and get the total count for each then you can start sorting and trimming results.

Using a DVWP we will customize the xslt to achieve our requirement.

xsl

Design Tab > Customize XSLT > Customize Entire View 

First we need to get the unique authors list. Then use count( ) to get the total for each unique author.


<xsl:variable name="unique-authors" select="$AllRows[not(./@Author.title=following-sibling::Row/@Author.title)]" />

<xsl:for-each select="$unique-authors">
<xsl:variable name="thisNode" select="."></xsl:variable>
<xsl:value-of select="@Author.title" />
<xsl:value-of select="count($AllRows[./@Author.title = $thisNode/@Author.title])"/>
</xsl:for-each>

So where halfway done here. Now we need to change our code above to get it to sort descendingly. We added them inside a variable named “total-sorted” that will contain item node to store data we want for sorting.


<xsl:variable name="unique-authors" select="$AllRows[not(./@Author.title=following-sibling::Row/@Author.title)]" />

<xsl:variable name="total-sorted">
<xsl:for-each select="$unique-authors">
<xsl:variable name="thisNode" select="."></xsl:variable>
<item id="{generate-id(.)}">
<xsl:attribute name="author">
<xsl:value-of select="$thisNode/@Author.title"/>
</xsl:attribute>
<xsl:attribute name="numOfContribution">
<xsl:value-of select="count($AllRows[./@Author.title=$thisNode/@Author.title])"/>
</xsl:attribute>
</item>
</xsl:for-each>
</xsl:variable>

The sorting part…

<table>
<thead><td>Author</td><td>Contributions</td> </thead>
<xsl:for-each select="msxsl:node-set($total-sorted)/item">

<xsl:sort select="@numOfContribution" order="descending" data-type="number"/>

<tr>
<td>
<xsl:value-of select="@author"/>

</td><td>
<xsl:value-of select="concat('(',@numOfContribution,')')" />

</td>
</tr>

</xsl:for-each>
</table>

Insert some if conditions for formatting…

For alternating rows:

<xsl:if test="position() mod 2 = 1">
<xsl:attribute name="class">ms-alternating</xsl:attribute>
</xsl:if>

For limiting results(e.g. Display Top 5 authors only)

<xsl:if test="position() &lt; 6">
</xsl:if>

For Filtering results (e.g Category=’Best Practices’)

On top of your xsl stylesheet you will find this parameter AllRows which you can filter by column in our example is Category.
filter xslt

You can also use Sharepoint Designer for Filtering list which is a lot easier with it’s UI.