Tuesday, 4 September 2018

Microsoft Flow - Add a 'Scope' To a Flow to Group Actions Together

If you want to be able to group actions together and minimise them in one step, you can use SCOPES.  I'm used to them being called 'Action Sets' in Nintex Workflow and constantly have trouble finding it, so as a quick personal reference here's a picture!


Friday, 31 August 2018

Power Query / BI - Data Refresh Error: The column 'Activities' of the table wasn't found

What 
Had an issue in the last few days (approx 30/08/2018) where a Excel Report & a Power BI Report was failing to refresh.  The reports were pulling data from a SharePoint Online List.

Here's the error I was getting

Error: The column 'Activities' of the table wasn't found

Why
When I built these reports, I used Power Query to import the SharePoint List Data & then to make the data easier to look at, I removed all the System Columns that I didn't need to see.  One of these System Columns was called 'Activities'.

Anyway, I'm not sure why, but for some reason when I now get data from SharePoint Lists, the 'Activities' column no longer exists, so the refresh was breaking because it couldn't remove a column I wasn't even using!

How to fix
I just opened up the advanced editor, found the line where I was removing columns, and deleted the reference to "Activities".

Done!

let
    Source = SharePoint.Tables("[SITE URL]", [ApiVersion = 15]),
    #"GetData" = Source{[Id="[LIST ID HERE]"]}[Items],
    #"Removed Columns" = Table.RemoveColumns(#"GetData",{"FileSystemObjectType", "Activities", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId"})
in
    #"Removed Columns"

Lesson Learnt
Look at choosing the columns you want to import instead of just importing the whole SharePoint list & then removing columns...

Thursday, 2 August 2018

SharePoint Online - Conditional Formatting a Column Between Two Dates using JSON

** UPDATE 30/08/2018: I initially wrote this blog at the start of august with the old method (see end of post), however thanks to Dave Paylor (@payl0rd) I found a new, cleaner method to creating conditional formatting which brought my lines of JSON down from 50 to 7.

What
Microsoft have plenty of examples on how to implement conditional formatting on a SharePoint list.  However my specific needs were:

  • If Date >= [Today] + 30 days Then Background WHITE
  • If Date <= [Today] -2 years Then Background WHITE (This covers any empty fields)
  • Else Background ORANGE 

Why
I was building a list to monitor licence expiry, applying conditional formatting to these date rules meant that I could highlight a column whenever a licence was about to expire to give staff a heads up when they visited the list.

I also complimented this list with a flow that runs on a fortnightly schedule and emailed a list of all the staff with drivers licences that were expiring soon. [See that here - coming soon]

How
When you edit a List Column, you can input the following JSON code into the 'Column Formatting' box:
{
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
      "background-color": "=if(@currentField >= @now + 2592000000 || @currentField <= @now - 62208000000,'white', '#ffa59b')"
  }
}

This is what you get!

This code also ensures blank values don't highlight orange.  See how even though Grandma doesn't have a license (and nor should she!), her cell stays white.

Also if you're wondering why those numbers are so big in the JSON, they represent milliseconds.  so the first big number is how many milliseconds in 30 days, and the second is how many are in 2 years.

Old Method
Thank god I'll never have to do this again!

{
  "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "background-color": {
         "operator": "?",
         "operands": [
            {
               "operator": ">=",
               "operands": [
                  "@currentField",
                  {
                     "operator": "+",
                     "operands": [
                        "@now",
                        2592000000
                     ]
                  }
               ]
            },
            "white",
            {
               "operator":"?",
               "operands":[
                  {
                     "operator":">=",
                     "operands":[
                        "@currentField",
                    {
                        "operator": "Number()",
                        "operands": [
                            {
                                "operator": "-",
                                "operands": [
                                    "@now",
                                    62208000000
                                ]
                            }
                        ]
                    }
                     ]
                  },
                  "#ffa59b",
                  "white"
               ]
            }
         ]
      }
   }
}

References

Monday, 2 July 2018

Nintex Workflow for O365 - Permission Issue With Custom Task Form

What
Product: Nintex Workflow & Forms for O365
Scenario: Create a custom Task Form on an 'Assign a Task' action via the 'Edit Task Form' option

Issue: Staff member does not have sufficient permission to approve task, and is instead provided with the following error message "Item does not exist. It may have been deleted by another user."

Why
In my case, I checked permissions on the list hosting the workflow AND the Workflow Tasks list to ensure the user had at least Contribute access on both (as per Nintex instructions here).

However the error message continued to appear.  In the end it was because of two things:
  • On the list hosting the workflow, in 'Advanced Settings' I had set Read Access to 'Read items that were created by the user'.  This was so staff cannot see requests submitted by their colleagues.
  • The moment you edit the task form in Nintex Workflow, the Task Form requests data from the list item running the workflow.
Because we said that you can't see other peoples requests, the task form fails to work and this error message appears:


This isn't a very common scenario, normally I'd allow staff to see any requests submitted in the list.  However, this was unacceptable for this particular client.

How to Fix?
The simplest method to fixing the issue is to provide Full Control permissions to the list hosting the workflow.  Obviously this ruins any ideas you had to hide items from regular users.

So your other option is to delete the custom task form, and instead use the default SharePoint Task Form for approving tasks.  This allows us to apply the correct minimum-required permissions to list items.  However, this also means the end user gets a crappy UI for approving tasks.

So if you have to use a custom task form to make it a smoother experience for the end user, then the cleanest method to do this and ensure that you don't allow users to see all items is:

  • Switch off 'Read items that were created by the user' and change it to 'Read All Items'
  • Give all regular staff 'Contribute without Delete' access to the list (you'll need to create a custom permission access level for this
  • Add the 'Office 365 Update Item Permissions' Action as the first step in your workflow. Give the person who created the task, and the person who is approving the task, 'Contribute' access
That's it.  The only limitation with this solution is that every item will have individual permissions, and there is a limit of 50,000 items in a list with individual permissions in SharePoint Online.

Preferred Solution
Although Nintex is great for quickly modding the 'Task Item' form, I would just ask staff to be happy with whats provided out of the box if they also have these permission requirements.  Otherwise the solution just gets too complex.

If you'd like a great approval form OOTB, you should give Microsoft Flow a go.  Also, if you're looking for forms that work well in SharePoint AND on mobile devices, give PowerApps a go!

Got a better solution to this problem? Let me know in the comments.  

Monday, 12 February 2018

How To Embed Slack On A SharePoint Page

This isn't going to be my cleanest blog post or code...


  • Here's where you go to get the URL for the 'Get Messages' section of the code: https://api.slack.com/methods/channels.history/test
  • Here's where you go to get the URL for the 'Get User' section of the code: https://api.slack.com/methods/users.info/test
  • CompanyName is the name you signed up for in your Slack URL

The rest you should be able to paste into a Script Editor web part

--------------------------------------------------

<link rel="stylesheet" type="text/css" href="https://a.slack-edge.com/edd25/style/rollup-slack_kit_legacy_adapters.css">
<link rel="stylesheet" type="text/css" href="https://a.slack-edge.com/a225c/style/rollup-client_base.css">
<link rel="stylesheet" type="text/css" href="https://a.slack-edge.com/4944c/style/rollup-client_primary.css">
<link rel="stylesheet" type="text/css" href="https://a.slack-edge.com/b1c2e/style/rollup-client_general.css">
<link rel="stylesheet" type="text/css" href="https://a.slack-edge.com/50a1e/style/rollup-client_secondary.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<div id="SlackFeed"></div>

<script> 
//Get User
$.ajax({ url: "https://slack.com/api/users.info?token=[InsertToken]&user=[InsertUser]&pretty=1" }).then(function (data) {  
if (data["ok"] && data["ok"] === true) {
UserInfo = data["user"]; 
//alert(UserInfo.name);
//alert(UserInfo.profile.image_32);
    }
});

//Get Messages
$.ajax({ url: "https://slack.com/api/channels.history?token=[InsertToken]&channel=[InsertChannel&count=10&pretty=1" }).then(function (data) {
if (data["ok"] && data["ok"] === true) {
slackMessages = data["messages"];       
$.each( slackMessages, function( key, value ) {
//document.getElementById("SlackFeed").append(key);
//document.getElementById("SlackFeed").append(value.text);     
            
$("#SlackFeed").append('<div class="message" style="overflow:hidden;width:300px;">' + 
'<div class="message_pic" style="float:left;">' + '<a href="https://[CompanyName].slack.com/team/'+ UserInfo.name+'" target="https://[CompanyName].slack.com/team/'+ UserInfo.name +'"><img src="'+UserInfo.profile.image_48+'"/> </a>' + '</div>' + 
'<div class="message_text" style="float:left;padding-left:5px;width:200px;">' + '<span class="message_body"><b>'+ UserInfo.real_name +'</b></span><br/>' + '<span class="message_body">'+ value.text +'</span>' + '</div></div>');    
}); } });       
     
</script>

Thursday, 7 December 2017

Nintex Workflow for O365 - Get Manager Email Address

What
Nintex Workflow in Office 365 is different from On-Premise, so here's how you can grab a manager's email address.

Why
If you want to assign a task or email the manager of the person who submitted an item.

How
Click in the Text Box you would like to add the email address to and using the right-hand navigation menu select Workflow Context > Manager Email Address



Reference:
https://help.nintex.com/en-US/O365/o365/O365WorkFlow/CreatingWorkflows/Lookups.htm

Office 365 - How to Link Directly To A Users Delve Profile With Email Address

What
You've probably seen it before, when you navigate to a persons Delve profile in Office 365, the URL contains a unique UserID.

Example: https://aus.delve.office.com/?u=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx$v=work

Well there is a way to create a link that will send you to a users Delve profile based on their email address

Why
You may want to programatically show a list of users and link to their Delve Profiles.

How
The link below allows you to input an email address at the end, Office 365 will automatically translate this and re-direct you to the Users Profile

https://[TenantName]-my.sharepoint.com/PersonImmersive.aspx?accountname=i%3A0%23%2Ef%7Cmembership%7C[EmailAddress]