Posts

Showing posts from 2018

PowerApps - Building a Expanding Gallery with a Expanding Text Box

Image
I recently built a questionnaire PowerApp for a customer.  They had a bunch of questions where the expected answers could be anything from 50-500 words. The problem was when you put too many words into a text box, it gets scroll bars. eww.

I'm not against scrollbars for a whole screen, but for text boxes, yeah no. So here's what we did!
Added a 'Blank Flexible Height' Gallery to the PowerAppConnected it to my Collection of questionsAdded a label to display the questionAdded a text box to collect the answersSet the following text box properties:Mode: MultilineHeight: 40 * RoundUp((Len(TextInput1.Text)+0.01) / 90,0)What this does40 - how tall the text box is each time you meet the character limit for one line90 - How many characters it takes to move the 40px multiplier up roundup - ensures the returned number is only ever 1 rounded number.  This means the text box grows in chunks, instead of growing with each individual character typed.+0.01 - ensures that even if the text…

Microsoft Flow - Use the Take() Function to quickly test your loops

Image
The Take() function can be used to filter an array.  For example, if you have an Array with 1000 items in it, you can use the Take() function to only grab the first 5.
WhyOccasionally when you are building a Flow, you need to run a loop across a large array.  The loop can take hours to run depending on how many items are in the array and how many actions are in the loop.  Use the Take() function to quickly check that your loop will run successfully on a small subset of the array.
HowIn this example we are going to create an array of 10 numbers, and iterate through a subset of the array. The steps are as follows: Create an 'Initialize Variable' Action, and build an array with some values in it - here's some values for you: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29]Create an 'Apply to Each' Action and do the followingclick in the blank text box, navigate to the 'Expression' Tab and type: take()now click on the 'Dynamic …

Microsoft Flow - Apply To Each Limitation (5000 items)

Image
What
Had a situation where I was pulling back 70,000 items in an array from a REST API call.  I wanted to create a SharePoint item for each of the items in an array, however the 'Apply To Each' action kept giving me the following error:

ActionFailed. An action failed. No dependent actions succeeded.
Why
This was due to a Microsoft Flow limitation. When you are using the Free Version of MS Flow you are limited to 5,000 items in a loop.  Explained here: https://docs.microsoft.com/en-us/flow/limits-and-config

Workaround
I tried my best to come up with a workaround, tried splitting up the array into batches of 5,000.  All to no avail.  in the end, gave up, and saved the array of items to a CSV and saved the CSV in a SharePoint Library instead.


body('Parse_JSON')?['applist']?['apps']


The only other alternative I could come up with is to grab the 'first 4999 items' from the array, which does work with an expression like this:  take(variable('array'…

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

Image
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!


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

Image
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]", […

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

Image
** UPDATE 16/10/2018: In Q4 2018, microsoft are releasing the capability to do this column formatting using the GUI (no code!):  https://techcommunity.microsoft.com/t5/Microsoft-SharePoint-Blog/Reinventing-SharePoint-business-process-at-Microsoft-Ignite-2018/ba-p/260444

** 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 WHITEIf 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 a…

Nintex Workflow for O365 - Permission Issue With Custom Task Form

Image
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…

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/testHere's where you go to get the URL for the 'Get User' section of the code: https://api.slack.com/methods/users.info/testCompanyName 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=&quo…