Office 365

PowerApps Licensing Cheat Sheet

During the App in a Day sessions I’ve held there were often questions regarding the types of license that is required, based on the options/features that are part of the built solution. Based on the fact that an image is worth a thousand words, here’s how I’ve been able to easily demonstrate the basic differences between Office365 license, PowerApp P1 and P2 licenses. It’s not about all the nitty gritty details, but the base is there. In doubt always refer to Microsoft’s official documentation!

 

Microsoft Flow – How to move SharePoint Online list items to folders

Let’s say you have a SharePoint list with folders organized by continent and you have as well a choice column with the continent name in this. You want to move automatically all the list items with the chosen continent to the specific continent folder.

The first idea would be to use Microsoft Flow to do that, but there is no out-of-the-box action that moves list items to folders. At the moment of writing this post, there is an action to move files but nothing for list items. Even thinking about using SharePoint Rest API with the ‘Send an HTTP request to SharePoint’ connector there is no endpoint to move list items to a folder.

This is not clear in the Microsoft documentation, but actually, the same Rest API endpoint and action that is available for files can be used for list items by using the item file property. In this post, it will be shown how to use SharePoint Rest API to move items with Microsoft Flow, so as soon as an item is created or edited, it will be moved to the right location.

Basically, this Flow will do after being triggered:

1.   Get current list root folder path

2.   Get current item file name and the current path

3.   Build a new file path

4.   Check if the path is actually new and if so move the item

To better understand the following steps, knowledge on SharePoint Rest API and Microsoft Flow expressions is helpful.

To start building the flow, the trigger used is ‘When an item is created or modified’.

In this example, variables are used to store the list title, destination folder name and the site Url. Because the same values will be used in different connectors, it is a better practice to not let them hard-coded in the actions. For example, if this flow is exported to another environment or copied to be used in a different list, the changes to the flow will be minimal, only the initial variables and the trigger, instead of updating a bunch of connectors with hard-coded values.

Next step is to get the list folder Url using the SharePoint Rest API, using the ‘Send an HTTP Request to SharePoint’ action with the GET method. The action was renamed to ‘GetRootFolder’ so it is easier to access its output later in expressions. All the variables actions in this example are renamed as well, just for easier maintenance.

After this action, a new variable is initialized to store the list root folder Url with the following formula (use expressions to access the JSON content that is returned by the SharePoint Rest API, with the body function and the action name you can access the content):

body(‘GetRootFolder’)[‘d’][‘ServerRelativeUrl’]

Next action is to get the current list item File Server relative Url using SharePoint rest API like it was done with the list Root folder. In this case, it is needed to explicitly tell the Rest endpoint to load the ‘FileRef’ and ‘FileLeafRef’ properties.

The following variables are used to build the new list item folder Url, so the below formulas are used to assign values to the proper variables:

Item file name (Set with current file name only, used to build final destination path):

body(‘GetItem’)[‘d’][‘FileLeafRef’]

Item Url (Set with current item server relative Url, using the expression):

body(‘GetItem’)[‘d’][‘FileRef’]

New item Url (Root Folder/New Folder/Item File Name):

concat(variables(‘RootFolder’),‘/’,variables(‘MoveToFolderName’),‘/’,variables(‘ItemFileName’))

Then, the SharePoint ‘Send an HTTP connector to SharePoint’ is used again, to call the Files REST endpoint and move the item to the new location (yes, the same endpoint used for files).

It is validated if the File new folder path is different then the current one:

If it is, then a call to the API for the File/moveTo method is done in order to do the move:

This time a POST request is sent because we actually will request a change in this call to the endpoint, which will take care of the item move (also renamed the action to ‘MoveItem’).

After this last action is called, the item is moved to the proper folder.

The final flow will look like this:

With this flow, regardless of where the items are saved, they will be moved to the proper folder after being created/edited.

Hack4Good – My First Hackathon

Hack4Good Group Photo

TL;DR

I’ll warn you – this is a long read! To summarise though – this Community is beyond awesome and the Hack4Good event just proved that we can genuinely change the world.

The Hype

When TDG announced that there was to be a hackathon in London, with the focus of it being the Non-Profit/Charity sector, I was straight in there on the registration (after which Mrs H was then informed  that I was booked in – easier to ask forgiveness than seek permission)

This was to be my first ever hackathon, a year ago I hadn’t even HEARD of hackathons, and it ticked so many boxes for me. For those who don’t know, it’s not hacking in the sense of breaking into systems etc – this is all about using software and platforms that are at your disposal to hack together a solution to a scenario within a given time limit. The most innovative, practical, deliverable, and potential-filled solution would win the day.

When the emails started to come out Chris asked (in typical CAPS LOCK STYLE) if I would lead a team. Me being me, I jumped at the chance – in for a penny, in for a pound.

And so the excitement began. Weeks turned into days, and my poor family and friends got fed up of hearing how stoked I was. When I saw this list of other team leaders, and saw the people who were on my team, I started to question my credentials. There were so many legends of the community involved – people I look up to, and follow with eagerness and anticipation.

The Buildup

At 5:30am on Saturday 16th April, loaded with snacks and tech, I headed towards the railway station. Nerves meeting with excitement, doubts meeting determination.

Arriving just before 8am I was struck by just how, on first impressions, the Microsoft Reactor in London is a strange space. Fully stocked drinks area, with stereotypical caffeine overload available, games area, and then a large open space with tables and a large video screen. It almost seemed spartan in its simplicity.

As everyone started to arrive, and we set up our various laptops and devices, that open space suddenly became this hive of technology and potential.

Hugs and Hellos were dished out with abandon, and cries of “It’s so good to meet you at last” were deafening in their abundance. I moved from person to person and finally got to meet people who I’d talked to online or who I’d been following for ages. I was even surprised to find people who wanted to meet me!

The Morning

With typical fervour and energy the trio of Chris Huntingford, Kyle Hill and William Dorrington (who had come over for the start despite having removal lorries outside his front door!) kicked off the day.

A surprise video message from James Phillips, Corporate Vice-President of Microsoft, impressed upon all of us just how much the community is noticed by Microsoft and raised the expectations of all in the room another notch. If our dials were at 11 before that video, they were at 12 afterwards – and even Spinal Tap didn’t get to 12!

I’ll be honest at this point and admit that I can’t remember who presented exactly what and when – my mind was a maelstrom of ideas and planning.

The engaging Architect and Storyteller Alex Rijnoveanu (@WomanVsTech) from Microsoft delivered enthusiasm and encouragement.

The very funny, and trying-not-to-be-as-sweary, Sarah Critchley (@crmcat)presented in a way that only she could – with an idea about helping out stray cats using powerapps and other bits.

m-hance presented alongside Solent Mind, and that I related to what they did in a huge way because of the work I see in my day job at St. Andrew’s Healthcare. It was a sobering presentation in many ways, but also opened up our eyes as to “the art of the possible”.

Saurabh Pant and Sameer Bhangar had flown in from Microsoft (yes, all the way from Seattle) just for this event and then through away their planned roadmap presentation to give us all a major pep talk and stir us up even more. I have to say that the funniest thing was their very friendly (and also slightly sweary) rant about how much they had heard about Samit Saini in the past year! In so doing, it just served to show us all just what was possible – those who knew Samits journey smiled and laughed, and those who didn’t had their eyes opened to a new level of potential.

Quantiq presented some of the work they had done with the Leonard Cheshire charity and also give a glimpse of their toolkit for healthcare and the ideas kept flowing. As I look around at the other teams I could see people taking notes, typing away, and whispering to each other. This hackathon was going to be competitive, but boy was it going to deliver some amazing results.

I’ll apologise now to all the presenters as I haven’t done you justice in my few words, and I may have mangled your presentations up, but believe me when I say that all the presentations hit home with all of us listening. Those presentations took our plans, determination, and enthusiasm up to levels you just wouldn’t believe if you weren’t there!

Let The Hacking Commence

With a final presentation to lay down the rules of engagement, and also to make it clear that stopping for lunch was most definitely not an option, the starters gun was fired and the 4.5 hours of planning, building, and preparing began.

The buzz in the room was electric as each team discussed and planned out their scenario and then grabbing whiteboards and white space to map out what a solution could look like.

I’ll be writing more about the Team White proposal in the coming days, as there is more to come from that, but we settled on a solution that would utilise so much of the stack but would be able to be modularised and deployed as a “solution-in-a-box” approach.

With my amazing team of Penny, Josh, Denis and Raj we set about building Microsoft Forms, PowerApps, Dynamics 365 solutions, Flows, and the concept of the Hololens. Oh yes, Gadget King Raj had brought us a Hololens – and that just expanded the possibilities for us. We weren’t looking at gimmicks and tech-for-techs-sake, we were looking at a genuinely life-changing solution using some amazing software and hardware.

With a soundtrack of some amazing 80’s rock being pumped out (and yes, thanks Chris for Rickrolling us!), everyone was doing something. If you could have harnessed the energy in that room at that point you would have been able to power half of London.

Floor walkers popped by each of the teams each one listening and absorbing before offering advice, help, suggestions and more – but what was even more amazing was that the teams were all talking to each other. You read that right, the teams all talked to each other.

There was sharing of scenarios, encouragement, suggestions for improvement or additions, and helping hands. This was a competition that was like no other. This was a competition in which we ALL wanted to see every team achieve their goals. I’m a mildly (ok, seriously) competitive person at times and yet there was no sense of barging past each other to reach the finish line. This was collaboration and cooperation in competition towards a common goal.

The Winners

And with 4 and a half hours gone in the blink of an eye, the race was run. It was time to do the 5(ish) minute speed-dating presentation of the solutions.

As each team stepped up and shared I really do not know how I held it together. These were genuine scenarios, delivered with innovative solutions, and by passionate people.

Every last one.

We all watched, applauded and cheered. None of us could separate the competition. Judging was going to be tough, and so it proved.

With our hosts waffling as much as possible whilst the judges adjudicated, we all sat wondering just who it would be. We all wanted to win, but we all knew that whoever did win was fully deserving of it.

With the decision made, the announcement came that Team Grey (who had flown over from Germany to take part!) had won with an app for rounding up as you ordered food or transport and donated this to your charity of choice. Writing that makes it sound simplistic, but if you think of the implications of it you soon realise that it has massive potential.

It Is NOT Over!

The final speeches and thank you’s were made, the applause leaving hands feeling rather raw and sore, but this isn’t the end. Every proposition in the room has legs, and every person in the room knew that this couldn’t stop just because the clock had run down.

Saturday saw the start of something, the spark that starts a fire. We all felt it and reading all the posts on twitter and LinkedIn etc after the event just reaffirms that determination.

We saw not a glimpse, but rather a bright shining beacon of the power of the community. I go on and on (and on) about Community but what happened in that room on Saturday, with just a part of the enthusiastic and passionate community present, just proved what we can all achieve if we put our minds to it.

Here TDG we have the Community Collaboration Portal for working on community projects together, and there’s the Power Platform Bank for making solutions available, and then there’s all the social media channels out there as well.

Let’s turn this spark into a raging fire of change. Let’s use our collective skills to build new solutions to old problems.

Oh, and let’s do this again real soon!

 

 

 

SharePoint Modern Lists: Force forms to open in full size

SharePoint list forms on modern experience lists will open in a dialog side box by design (at the moment of writing this post, no out-of-the-box options are available to change this).

In my opinion for non-customized forms that’s ok, but for PowerApps forms in SharePoint lists, the layout is not fine when the form layout becomes large, due to currently the forms inside the lists not being perfectly responsive.

Depending on the screen resolution we get unnecessary scrollbars which makes it look poorly done. If you use the form link which can be copied from the form dialog, you can open the item in a full-screen view.

It would be nice if there was an option to configure the item to open in this view out of the box, but for the moment, it is not available. Even if we change the list settings to disable dialogs, that doesn’t take effect on modern experience lists.

But we can count on SharePoint column formatting to overcome this!

With column formatting, we can use custom JSON script to change how a field is displayed, editing or adding HTML elements to the field view. You can find a full overview of column formatting here.

In the case mentioned to force the form to open in full screen, what we need to do:

  • Choose a field to change the formatting and include a link
  • Format the link based in the item ID and the list Forms URL

If the ID field allowed column formatting we could build the link directly in the ID field, but unfortunately, that’s not an option. So we’ll have to change another field formatting, but accessing the ID field value to build the URL.

You can use the Title field which is normally used to open the items to do this but keep in mind that by overriding the field formatting you’ll lose the field button to open the contextual menu.

The JSON script for the field will be something simple as we will add just one HTML element, here’s the example:

Quick explanation:

  •  elmType: the HTML tag type you are going to display
  • txtContent: value to be displayed inside the HTML Element – in this case the current field
  • attributes: a set of other attributes to be added to the HTML Element, in our case
  • href: the link URL – built dynamically using the ID field value and Current Site URL. Add the ‘Source’ parameter to the querystring so that after saving the form you are going to be redirected to the list view (change ‘/lists/yourlist’ above to be your list Site Relative path.
  •  target: _self was chosen in this case, to open the form in the same window.

In the targeted list settings, open the column you want to apply the JSON formatting, paste the JSON content under ‘Column Formatting’ and save the changes.

Now all the items in the list when opened using the chosen field will open in a full page with this little trick.

Security Trimming PowerApps based on Office 365 Groups

PowerApps already does security trim for item-level permissions based on the Data Source permissions, but in some cases you may want to implement extra rules or more complex trimming types that are not possible with the selected Data Source. That’s when Office 365 groups come to help.

Showing or hiding controls in a PowerApps based on Office 365 group membership is not obvious but it is not a complex task.

First of all, you’ll need to get your Office 365 group Id (Guid).

If the group is a normal Office 365 Group, you can find the Group ID from Outlook on the web (https://outlook.office.com) logging with your Office 365 account.

Just select the group under the groups list, find the group welcome message, and copy the “Add to the team site” link URL (Right-click and copy URL address).

 

Paste it in any text editor, you’ll find it in the following format:

https://<tenant>.sharepoint.com/_layouts/groupstatus.aspx?id=<your id here>&target=site

Note: If the group is a security only group (without mailbox and connected SharePoint site), you’ll have to use Office 365/Azure AD PowerShell to find the group Id (https://docs.microsoft.com/en-us/office365/enterprise/manage-office-365-groups-with-powershell)

Now that you already have your group ID, open the desired PowerApp in edit mode, and add a new connection for Office 365 Groups:

In the control or card you want to condition the visibility just for members of the group you got the ID, set the visible property to be:

CountRows(Filter(Office365Groups.ListGroupMembers(“your group guid here“).value, mail = User().Email)) > 0

Which means if the group contains a user with the current user email address, the current user is a member of the group, then the formula returns true and the control is shown. Now inside your app, the control or card is going to be shown just if the user is a member of the specified group.

This is just a simple sample, but the idea can be reused and improved for more complex scenarios, for instance, assigning the membership to a variable on the OnVisible/OnStart events to manipulate it later in the PowerApp, or conditioning the DisplayMode of a control to be View instead of Edit based on the output a formula containing this rule combined with other rules.

Hope this sample helps!

Using Eircode (Ireland Postcodes) to get Geolocation from Google Maps with Microsoft Flow

Let’s say you have a SharePoint list storing information with an Eircode column (postal code for addresses in Ireland), and you want to use that information in Power BI later to generate a map organizing all your items by location. Unfortunately, the Power BI maps don’t work fine with Eircodes, so how could we get the locations with the most precise information?

Let’s use Microsoft Flow and Google Maps API for that!

(Sorry Microsoft for not using Bing Maps)

Before start building the Flow, get the Google maps developer API key in: https://developers.google.com/maps/documentation/javascript/get-api-key

And include two new columns of type number and automatic decimal places in the SharePoint list: Latitude and Longitude

With those two things set up, it’s time to begin the Flow creation.

We will use the SharePoint ‘When an item is Created or Modified’ Trigger to start our flow. After starting the Flow creation with this trigger, add an ‘HTTP’ action. To get the Latitude and Longitude information, make a get Request to Google Maps API using the Eircode coming from the SharePoint item as Address filter:

In this case, the search is filtered to be just in Ireland as you can see in the parameters.

The results we get from Google Maps API are in the following JSON structure:

Note we have the latitude and longitude under geometry/location. So, to manage that information easily, create two variables ‘Latitude’ and ‘Longitude’, of type Float.

Assign the value coming from the HTTP request body to the variables using an expression for each (which will navigate through the JSON object to access the data):

Latitude: float(body(‘HTTP’)[‘results’][0][‘geometry’][‘location’][‘lat’])

Longitude:float(body(‘HTTP’)[‘results’][0][‘geometry’][‘location’][‘lng’])

Now that you have your values properly assigned, check if one of them differ from the existing ones in SharePoint, in case it is different it means the item needs to be updated. Then add an ‘Update List Item’ Action and set it to update your list items with the new Latitude and Longitude values to finish.

 The final layout of the flow will be the following:

Now, as soon as your items are updated or created in the SharePoint list, the information will be ready to be used in the Map visual on Power BI with the SharePoint list as data source.

Have fun!

Dynamics 365 Saturday – Dublin 2018

The Dynamics 365 Saturday 2018 took place in Microsoft Dublin (One Microsoft Place) and was hosted by Janet Robb and her Team. Congratulations on Janet for a great work on organising such well-structured event.

Lots of cool sessions provided, as well as workshops for PowerApps and CRM for the ones who were interested.

 

Even though I’m not a Dynamics consultant, to me as a SharePoint/Office 365 Consultant, it was very cool to see how the same stack of products (Power platform + Azure) that we use on top of SharePoint/Office 365 is been adopted in the Dynamics Community as well. As mentioned on the keynote, for all D365 specialists it’s been a constant work to keep up to date with tons of new features been released (I can assure that for Office 365 as well), and an event like this helps to connect the community and to get a summarized overview of what’s been there in the market.

Special thanks to our Dynamics Guys who came from the UK and smashed in their presentations:

Chris Huntingford – Dynamics 365 for Marketing

Kylie Hill – My Favorite new D365 Features

It was a pleasure to meet those legends in person.

Also, the interaction from the community posting and sharing content and pictures on social media (Hashtag #365SatDub) was really nice, cool to see such engagement and nice pictures from everyone on Linkedin and Twitter.

Looking forward to the next #365SatDub!!

Skills for the PowerApp developer

It seems to me that PowerApps will prove to be a game-changer by enabling businesses and organisations to build bespoke apps designed to meet very specific needs.

The curious thing about PowerApps is that the product is pretty well where it needs to be as of today but the major blocker for a global revolution is that there simply aren’t enough people out there with anything like the skills that they would need to be able to build robust business product.

So let’s assume that you can’t buy in PowerApps skills or maybe that you have some but need to grow some more.  What would you be looking for in an individual to identify them as a potential PowerApps developer?  In this article I’m going to outline some of the skills that I would be looking for when identifying candidates with the potential to be high performers.

Excel

It feels kind of strange to start with a product rather than personal skills but in this case the Excel formula structure and Excel cell construction is so closely related to that of PowerApps that is the number 1 skill.

The Excel and PowerApps teams work closely together so that any new formulas created are aligned to each other.  With this in mind this also opens the door to the many years of formulas created by individuals within excel that can theoretically be borrowed or referenced to enhance a PowerApp.

Maths

PowerApps is the product that is the most mathematical that I’ve ever seen.  Variables can be easily created that can be used in an algebraic fashion.  X and Y properties exist to govern the position of everything that you see on a screen.

Empathy

You need to be able to consider your products from the perspective of other users.  If people don’t like your product they won’t use it, and you should be aiming for them to love them.

Logic

To my mind there are 2 kinds of logic.  Firstly, the more mathematical/excel based ‘If X=1 to this otherwise do that’ and secondly there is the type of logic where you put yourself in the shoes of a user or administrator and be able to see whether or not a form (as in business world this is the most likely application) has a logical flow.

Design

Having an eye for design is a skill people have to a greater or lesser extent.  To be fair it doesn’t come naturally to me, but I do know that from making lots of products some designs work better than others and it is a skill that can be learned.

Initiative

PowerApps won’t go to you, you have to go to PowerApps.  If you create a blank app it will stay blank until you do something.  Additionally, many technical solutions may require techniques that feel like work-arounds mainly because the problems you have been facing haven’t been faced before, so looking them up on Google won’t necessarily yield any results.

Lifelong Learning

The product is ready, but is changing all the time.  Good sources for keeping up to date are twitter and the PowerApps blog, but even the latter tend to focus on ‘big’ updates whereas some of smaller adjustments can still be game changers.  Keep up with it.  I’ll be honest in saying that there are times when staying at the cutting edge can be difficult to manage versus traditional tools and skills that stay very similar over long periods of time.

Perseverance

Some problems will require high levels of determination to find the exact syntax you need to solve your problem and when those fail you may need to pull back stick your head up and work out if there ways around the problem or if the items is even needed (i.e. do you really need to collect the data at all or is it a nice to have)

Patience

Not necessarily my forte, but patience may be required of yourself as you may not have the skills that you need at your fingertips and similarly the same may be true of those around you.  You’ll also need a level of patience on the part of your sponsors or users.  I can almost guarantee that unless they are very familiar with digital form building you may produce a product to the exact specification requested but nevertheless you, and they, know that it’s not quite right and that adjustments, sometimes significant may be required.

Use the community

The PowerApps community at this stage is currently populate by a relatively small but passionate group of individuals building highly imaginative solutions designed to stretch the product.  Make use of these by joining https://powerusers.microsoft.com, follow people on twitter posting using the #powerapps or @PowerApps references.  By all means take a look at my youtube channel www.youtube.com/dataspinners and don’t forget to join https://dynamics365society.uk which contains a PowerApps bank that you can make use of once you sign up (for free).

Go on a course

Personally, I’m quite happy with an online course as typically these are completed over a longer period of time which for me is a better way of allowing the learning to sink in.  The best single free resource is https://courses.edx.org where DAT207x is a very useful starting point.  You can search up courses run in a classroom setting, but you will need to ensure that you invest time in utilitizing the product one you’ve completed it as otherwise you’ll lose your knowledge quite quickly.

Concluding remarks

This article was intended for hirers or individuals trying to make some sense as to the range skills needed for PowerApps excellence.  Hopefully, it isn’t too daunting.  Whatever you do, start small but build usable solutions.  This particular tool offers us astonishing scope for us to run our businesses in efficient ways and the more we can grow as a community the closer we will all get to a culture of efficiency the impact of which may be widespread, more operations, better scheduling less wastage all by putting the right information in front of the right people at the right time.

 

 

Do you want to get up to speed in PowerApps?

Do you want to start building your business applications with PowerApps and don’t know where to start?

I’ve been working with PowerApps + Flow on top off Office 365 for a while, and as am always studying and learning new stuff, I came across with some different content sources to get updated.

Here are some excellent resources to begin with:

Developing Business Applications with Power Apps and Microsoft Flow edX Course

This course will get you started on creating your PowerApps and understanding the core concepts of the platform by excellent videos and labs. Quick and straight to the point. The course is free, but if you want to get a certificate from edX, you should pay a fee for that.

YouTube channels to Follow:

  • Shane Young’s Youtube Channel (Excellent demos for tricky scenarios involving PowerApps)
  • Audrie Gordon’s Youtube Channel (Day by day tips about PowerApps)

Twitter accounts to follow

Following we have PowerApps and Flow related twitter accounts, from specialists around the globe and Microsoft staff who are always posting news and good tips (if you don’t use Twitter I strongly recommend you to start and follow these guys):

Shane Young: https://twitter.com/ShanesCows

Pieter Veenstra: https://twitter.com/PieterVeenstra

William Dorrington: https://twitter.com/WilliamDorringt

Audrie Gordon: https://twitter.com/ArtsyPowerApper

James Olenik: https://twitter.com/joleinik

Office Dev PnP: https://twitter.com/officedevpnp

Paul Gallagher: https://twitter.com/spgally

Chris O’brien: https://twitter.com/ChrisO_Brien

Pratap Ladhani: https://twitter.com/pratapladhani

Melissa Hubbard: https://twitter.com/melihubb

Serge Luca: https://twitter.com/sergeluca

Laura Rodgers: https://twitter.com/WonderLaura

Matt Wade: https://twitter.com/thatmattwade

Communities to get involved

PowerApps Community on PowerUsers – Official Microsoft Community forum for PowerApps. Here you can find lot’s of questions and answers, issues, and can even be answered by a Microsoft employee.

Those Dynamics Guys and PowerApps Bank – recently PowerApps bank added, which you can download cool samples for free.

Microsoft Sites:

PowerApps webinars from Microsoft

Do you know any other good resources or accounts to follow? Please leave it in the comments!

PowerApps Formula reference for PowerApps

Formula reference for PowerApps

When building PowerApps I have found the below from Microsoft incredibly really helpful, an index of awesomeness for all things related to PowerApp formulas. I have lifted this from the Microsoft page but kept all the hyper links to allow you to drill into each piece of functionality.

A

Abs – Absolute value of a number.

Acceleration – Reads the acceleration sensor in your device.

Acos – Returns the arccosine of a number, in radians.

Acot – Returns the arccotangent of a number, in radians.

AddColumns – Returns a table with columns added.

And – Boolean logic AND. Returns true if all arguments are true. You can also use the && operator.

App – Returns information about the currently running app, such as which screen is currently displayed.

Asin – Returns the arcsine of a number, in radians.

Atan – Returns the arctangent of a number, in radians.

Atan2 – Returns the arctangent based on an (x,y) coordinate, in radians.

Average – Calculates the average of a table expression or a set of arguments.

B

Back – Displays the previous screen.

Blank – Returns a blank value that can be used to insert a NULL value in a data source.

C

Calendar – Retrieves information about the calendar for the current locale.

Char – Translates a character code into a string.

Clear – Deletes all data from a collection.

ClearCollect – Deletes all data from a collection and then adds a set of records.

Clock – Retrieves information about the clock for the current locale.

Coalesce – Replaces blank values while leaving non-blank values unchanged.

Collect – Creates a collection or adds data to a data source.

Color – Sets a property to a built-in color value.

ColorFade – Fades a color value.

ColorValue – Translates a CSS color name or a hex code to a color value.

Compass – Returns your compass heading.

Concat – Concatenates strings in a data source.

Concatenate – Concatenates strings.

Connection – Returns information about your network connection.

Count – Counts table records that contain numbers.

Cos – Returns the cosine of an angle specified in radians.

Cot – Returns the cotangent of an angle specified in radians.

CountA – Counts table records that aren’t empty.

CountIf – Counts table records that satisfy a condition.

CountRows – Counts table records.

D

DataSourceInfo – Provides information about a data source.

Date – Returns a date/time value, based on YearMonth, and Day values.

DateAdd – Adds days, months, quarters, or years to a date/time value.

DateDiff – Subtracts two date values, and shows the result in days, months, quarters, or years.

DateTimeValue – Converts a date and time string to a date/time value.

DateValue – Converts a date-only string to a date/time value.

Day – Retrieves the day portion of a date/time value.

Defaults – Returns the default values for a data source.

Degrees – Converts radians to degrees.

Disable – Disables a signal, such as Location for reading the GPS.

Distinct – Summarizes records of a table, removing duplicates.

Download – Downloads a file from the web to the local device.

DropColumns – Returns a table with one or more columns removed.

E

EditForm – Resets a form control for editing of an item.

Enable – Enables a signal, such as Location for reading the GPS.

EndsWith – Checks whether a text string ends with another text string.

Errors – Provides error information for previous changes to a data source.

EncodeUrl – Encodes special characters using URL encoding.

Exit – Exits the currently running app.

Exp – Returns e raised to a power.

F

Filter – Returns a filtered table based on one or more criteria.

Find – Checks whether one string appears within another and returns the location.

First – Returns the first record of a table.

FirstN – Returns the first set of records (N records) of a table.

ForAll – Calculates values and performs actions for all records of a table.

G

GroupBy – Returns a table with records grouped together.

H

HashTags – Extracts the hashtags (#strings) from a string.

Hour – Returns the hour portion of a date/time value.

I

If – Returns one value if a condition is true and another value if not.

IfError – Detects errors and provides an alternative value or takes action.

IsBlank – Checks for a blank value.

IsEmpty – Checks for an empty table.

IsMatch – Checks a string against a pattern. Regular expressions can be used.

IsNumeric – Checks for a numeric value.

IsToday – Checks whether a date/time value is sometime today.

L

Language – Returns the language tag of the current user.

Last – Returns the last record of a table.

LastN – Returns the last set of records (N records) of a table.

Launch – Launches a web address or an app.

Left – Returns the left-most portion of a string.

Len – Returns the length of a string.

Ln – Returns the natural log.

LoadData – Loads a collection from PowerApps private storage.

Location – Returns your location as a map coordinate by using the Global Positioning System (GPS) and other information.

LookUp – Looks up a single record in a table based on one or more criteria.

Lower – Converts letters in a string of text to all lowercase.

M

Max – Maximum value of a table expression or a set of arguments.

Mid – Returns the middle portion of a string.

Min – Minimum value of a table expression or a set of arguments.

Minute – Retrieves the minute portion of a date/time value.

Mod – Returns the remainder after a dividend is divided by a divisor.

Month – Retrieves the month portion of a date/time value.

N

Navigate – Changes which screen is displayed.

NewForm – Resets a form control for creation of an item.

Not – Boolean logic NOT. Returns true if its argument is false, and returns false if its argument is true. You can also use the ! operator.

Now – Returns the current date/time value.

O

Or – Boolean logic OR. Returns true if any of its arguments are true. You can also use the || operator.

P

Param – Provides access to parameters passed to the app when the user opened it.

Parent – Provides access to a container control’s properties.

Patch – Modifies or creates a record in a data source, or merges records outside of a data source.

Pi – Returns the number π.

PlainText – Removes HTML and XML tags from a string.

Power – Returns a number raised to a power. You can also use the ^ operator.

Proper – Converts the first letter of each word in a string to uppercase, and converts the rest to lowercase.

R

Radians – Converts degrees to radians.

Rand – Returns a pseudo-random number.

Refresh – Refreshes the records of a data source.

Remove – Removes one or more specific records from a data source.

RemoveIf – Removes records from a data source based on a condition.

RenameColumns – Renames columns of a table.

Replace – Replaces part of a string with another string, by starting position of the string.

Reset – Resets an input control to its default value, discarding any user changes.

ResetForm – Resets a form control for editing of an existing item.

Revert – Reloads and clears errors for the records of a data source.

RGBA – Returns a color value for a set of red, green, blue, and alpha components.

Right – Returns the right-most portion of a string.

Round – Rounds to the closest number.

RoundDown – Rounds down to the largest previous number.

RoundUp – Rounds up to the smallest next number.

S

SaveData – Saves a collection to PowerApps private storage.

Search – Finds records in a table that contain a string in one of their columns.

Second – Retrieves the second portion of a date/time value.

Set – Sets the value of a global variable.

ShowColumns – Returns a table with only selected columns.

ShowError – Displays an error message to the user.

Shuffle – Randomly reorders the records of a table.

Sin – Returns the sine of an angle specified in radians.

Sort – Returns a sorted table based on a formula.

SortByColumns – Returns a sorted table based on one or more columns.

Split – Splits a text string into a table of substrings.

Sqrt – Returns the square root of a number.

StartsWith – Checks if a text string begins with another text string.

StdevP – Returns the standard deviation of its arguments.

Substitute – Replaces part of a string with another string, by matching strings.

SubmitForm – Saves the item in a form control to the data source.

Sum – Calculates the sum of a table expression or a set of arguments.

Switch – Matches with a set of values and then evaluates a corresponding formula.

T

Table – Creates a temporary table.

Tan – Returns the tangent of an angle specified in radians.

Text – Formats a number as a string for display.

ThisItem – When in a gallery or form, returns the data for the current item from the container.

Time – Returns a date/time value, based on HourMinute, and Second values.

TimeValue – Converts a time-only string to a date/time value.

TimeZoneOffset – Returns the difference between UTC and the user’s local time in minutes.

Today – Returns the current date/time value.

Trim – Removes extra spaces from the ends and interior of a string of text.

TrimEnds – Removes extra spaces from the ends of a string of text only.

U

Ungroup – Removes a grouping.

Update – Replaces a record in a data source.

UpdateContext – Sets the value of one or more context variables of the current screen.

UpdateIf – Modifies a set of records in a data source based on a condition.

Upper – Converts letters in a string of text to all uppercase.

User – Returns information about the current user.

V

Validate – Checks whether the value of a single column or a complete record is valid for a data source.

Value – Converts a string to a number.

VarP – Returns the variance of its arguments.

ViewForm – Resets a form control for viewing of an existing item.

W

Weekday – Retrieves the weekday portion of a date/time value.

Y

Year – Retrieves the year portion of a date/time value.

 

[Source: https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/formula-reference]

PowerApps – Camera Integration Part 1

Part one of the ‘Those Dynamics Guys’ PowerApps Series.

As promised via Linked-In I am going to publish a series of Blogs on how to start using PowerApps. PowerApps is part of the Dynamics 365 stack that allows a user to create various application that connect with not only Dynamics 365 Data Sources but a prodigious plethora of others such as:

  • WordPress
  • SharePoint
  • Twitter
  • Skype For Business
  • Outlook
  • Bing Maps
  • Google Maps

 

Selecting a connection/data set:

This will then take you to the next step which is where you can either create a new connection and select a connector of your choice (this example would use Dynamics 365 for Finance and Operations – known as jsut Dynamics 365 for Operations in PowerApps) or if you have pre-existing connections available select the Dynamics 365 for Operations connector and choose an environment, preferably Test Environment that you have full System Administrative access too,  and select it as your data set.

 

Selecting a table:

Now we have our data source we need to let the system know which Data Entity we wish for it to use – for this example we are going to use ‘Projects’, use the search functionality to filter through the various DEs to find the correct one choose it and select “Connect”:

A ‘Building Your App’ process will kick off then you’ll be greeted by the PowerApps designer. Then we are ready to start throwing this beast together!

 

Navigation of the PowerApps Designer (an Introduction):

Please note the menu is dynamic in nature so options will disappear and reappear depending on what has been selected – the navigation shown here is when no items are selected and should be seen as an introductory overview.

  1. Home – the home area on the action pane allows the user to choose from a various array of formatting option (text size, text weight, Italics, borders, fill, pre-set themes etc.)
  2. Insert – Allows the user to insert various items such as; buttons, icons, various controls, input text, labels, data, media, charts etc.
  3. Action – this allows you to create certain actions for various items including; ‘Navigation’ (where you’d like a certain item (Icon, button etc.)) to take you to a certain screen within the app, ‘Collect’ allows the user to create collections (this will be covered later on in the article), ‘Flows’ allows to integrate a flow or the option to create a new one. There are other options but this is enough to start with.
  4. Screen – select images for the background of the screen, state positioning for background images and select various colours to fill the background with.
  5. New Screen (Home Tab) – this allows the user to insert a new screen and select from various options such as whethere you wish for a blank fixed screen, a scroll enabled screen etc.
  6. Tree View – this view displays a break-down structure of the selected screen. Which allows you to navigate around the design of your app.
  7. Thumbnail view – this view represents the screens via small thumbnail images. Which allows you to navigate around the design of your app.
  8. Tree View (break-down) – separates into various items that can be drilled into to focus on each item independently.
  9. Play Icon – this allows the user to preview the app in it’s current, unpublished, state – you can also push F5 as a shortcut.
  10. Properties – allows to set various options for the selected item such as formatting of the item itself, default values etc.
  11. Rules – this allows the user to apply a rule that modifies an app based on predetermined criteria on the item selected.
  12. Advanced- depending the item selected this is where the user can apply various commands (some of which we’ll address in this article) applied to data elements, action elements and design elements.

One final item is the ‘View’ tab which allows the user to view the ‘Data Sources’ being used by the application (create new connections, refresh connections and delete connections), view any media being used by the App, view collections and variables.

 

Presenting the ‘Projects’ DE in a more useful manner:

The first stage to build our camera app is to have the first browse screen render the data in a more useful manner. Currently it is browse screen with some values in my case the “Allow Negative Values To Be Carried Forward” value:

To achieve this select the main ‘Browse Gallery 1’ area – then select, from the ‘Properties’ area within the ‘Layout’ grouping “Title, Subtitle and body” this will bring up the data dialogue where was can start picking and choosing the data from the Projects Data Entity that we wish to expose onto the gallery.

To change what is shown in the gallery we can change the following data inputs for the labels:

  • Body1
  • Subtitle1
  • Title1

I am going to change the drop down for each one of these to the following values:

  • Body1 = DeliveryName
  • Subtitle1 = ProjectStage
  • Title1 = ProjectName

This will then populate the Gallery with the selected values (push F5 of the play icon to view the app in it’s current state).

Now we have presented the Gallery in a more readable manner we now have a basic app that will allows us to view projects, select a project to view further details on the selected project and even edit certain values and publish them back to Dynamics 365 for Finance & Operations.

We will come back to adding further fields to the project details area but for now let’s jump straight in to, what I deem, the more exciting part- building the camera functionality and storage.

 

Building the camera functionality:

Right now on to the awesome part let’s build a camera app! First stage is creating a new screen. To achieve this go Home>New Screen then select Scrollable Screen.

Insert a new screen:

Change a label value:

This will generate and insert a new screen into the viewing pane and the designer will take you to the newly inserted screen The first thing i’d like to do and familarise ourselves with is changing the label which in this case is the Title. It is possible to change the label text in three locations:

  • Formula bar (by selecting ‘Text’ from the list and put quotations “” around it)
  • Properties – within the ‘Text’ area
  • Direct – by typing into the label itself

 

The next stage is to insert the ‘Camera’ Control – to achieve this first select the ‘Add an item from the insert tab’ (this isn’t a necessity but is a quick and easy way to remove the text from the screen) from the ‘Insert’ tab on the action pane and select Media>Camera.

Insert Camera and capture images:

This will then insert the Camera:

Now we need to start a collection that allows us to capture a picture and store it (we will then look into passing the image through to SharePoint and email later on in this article series). First select the Camera and go to the ‘Advanced’ area- we are now going to put in an ‘OnSelect command’ this come be achieved in two ways:

  • Advanced – within the advanced area there is an OnSelect field.
  • Formula Bar – change the list to ‘OnSelect’.

Type in the ‘OnSelect’ field the following:      ClearCollect(CameraImage,Camera3.Photo)

  • ClearCollect means that it’ll only store one image in the collection at a time (remove ‘Clear’ to amass a large collection.
  • “CameraImage” is the name of the collection – rename this this to whatever you’d like the collection to be called.
  • “Camera3” is the name of the Camera item and “Photo” the media the collection is expected to capture.

Now whilst still in the screen that contains the Camera ‘Play’ (Push F5) the app – this will open the App in the screen that you are currently in – now click the area that contains the camera. This will engage the ‘OnSelect’ command which will engage the ‘Collection’ to grab a still image from the camera.

Now let’s view the image. Go to  View>Collections:

This will take you to the collections list where we can select ‘CameraImage’ (the name we chose as the collections name in the step above) and view the captured photo.

The next article will show us how to present the image under the camera (shown below), then we’ll move on to flows! Article 2 click here!

 

 

 

 

HOUSING HACKATHON – Opinion Interview with Addi Ammar – Housing Rockstar!

Recently I had the privileged of having a chat to Addi Ammar, one of the Dynamics 365 housing specialists at Hitachi Solutions and also one of my mates. Addi is SUPER passionate about housing and has working in the industry for over 5 years. Her passion for the technology and for how it can be utilised to provide solutions in the housing industry is infectious. I really loved chatting.

Firstly, Thanks to Addi for taking the time out to chat to me. Was super fun and I learnt a whole lot. Recently Addi attended a housing hackathon hosted by Microsoft at the Thames Valley Park Offices on the 14th of March. Addi and her team were the proud winners J very cool and congratulations to all who put in so much hard work to make this event possible.

Normally we would do a recording, but I thought we would try this in a Q&A / dialogue type structure just to see how it works. We did laugh a lot: D so, here we go.

Chris: Welcome Addi, thanks for taking the time to chat. So, after attending the housing hackathon at Microsoft yesterday, what did you think? Was it fun to hang around a bunch of like-minded people and chat tech / problem solving?

Addi: I thought it was a really interesting and insightful day. There were a number of housing associations there as well as a number of vendors with different technologies and different offerings. So, it was really good to get everybody in a room together and, kind of, pass the ideas around. What was interesting, was that every HA had the same pain points… Even though this is something that we know, it’s really nice to get that sanity check and to hear everybody saying, Do you know what? Repairs, how do we manage them? How do we make it more intelligent? And, in fact, for the hackathon, all the teams picked repairs as their biggest pain point.

Chris: HAHAHA, no ways! That’s really interesting.

Addi: EVERYBODY! So it was really hard for the judges, because they were judging 5 repairs solutions.

Chris: Wow

Addi: So our table was predominantly Dynamic vendors. We had Advanced at the table, we had RSM, and obviously Hitachi. We had ANS there who weirdly, I used to work for. ANS offer a solution where they can migrate your current platform up into the cloud. We also had our housing association we were working with. What was cool is at our table, they had brought one of their tenants, so instead of bringing all IT staff and people from the business, they brought a tenant as well, which is probably what gave us the edge, because we were able to talk about technology and its impact on people at the other end. The GENUINE customer experience. I thought it was a fab day. We had loads of idea sharing.

Chris: That’s very cool! So did you and your team come up with a great solution to solve problems with repairs? Did you focus on anything in particular about repairs, or just the whole process?

Addi: We looked at the End-to-End journey and we started by saying that you should be able to raise a repair through any mechanism because it has to be what meets the needs of the tenant. So as well as having a mobile app, Artificial Intelligence, Chat bots that learn based on what’s been done before, being able to take a photograph of the repair and being able to recognise what that is. We also looked at things like Alexa and Google home because there are tenants that might have a smart phone but have no idea how to use it, whereas, Alexa is something you can physically speak to. We actually talked about tenants with dementia and Alexa might be easier for them to interact with than handling a mobile phone or interacting over a portal. Then we looked at flowing that information in and routing it out to the correct services. We also looked at the commercial element as well.

Chris: Oh Really?

Addi: Yeah I think it’s one of the things that set us apart from the other teams and one of the reasons we came first. We said well, we’re putting all this good stuff in, but HA’s firstly have maintenance staff that aren’t always busy, but they could also run like a trusted traders thing. SO, for tenants and non-tenants, they could access a service and say, you know what, my flat needs a piece of work doing, it needs painting or a toilet seat and you could pick a trusted trader that the housing association has either employed or verified. It’s an income stream, because you could charge for those repairs AND if you are letting trusted traders advertise through your housing association, you are generating income. SO, Yeah, they really liked that.

Chris: So you managed to bring a load of the bits and pieces together from a repair point of view. Do you think that there alignment between the HA’s and the partners? I suppose different ideas come from different places! Do you think the HA’s even knew some of this technology existed?

Addi: My impression was, there is a lot of buzzwords in the industry at the moment and for some housing associations, they have not even started their CRM journey. They have a load of disparate systems, they have all this data that is sitting in different systems, but they can’t use it. They have heard buzzwords in the industry like Alexa and Chat Bots but they don’t know where to begin in applying that because they have not got the fundamentals in place yet. I think that was really interesting in its–self. I think different people are at different stages of the journey, and that was evident in the room. So one of the housing associations that presented have gone through their digital, I’m not going to say “transformation”. HAHAAHA! I’m not going to say “Digital Transformation” because that implies that there is a destination, it’s more of a journey.

Chris: I love that!!!

Addi: You should ALWAYS be moving with the new technology and moving with the times. Striving to always be better. But the HA previously mentioned, they have done their CRM bit, they have done their standardising and rationalisation of data, systems and processes, so they are now able to look at how to make all of this more innovative. How do we get our tenants plugged into this digital shift? Some of the other HA’s haven’t started that journey, so everyone is at different points. There is A LOT of noise in the industry at the moment about chat bots, AI, Alexa bit I don’t think people realistically know how they can be applied.

Chris: You know something interesting; I was having a conversation about Alexa the other day about using Alexa in areas like Social Care. You have a scenario where an elderly person is in a smallish house, they have a fall and they can simply call out to Alexa to contact the HA or emergency services. Now that may be a little way in the future, but I thought that would be something that would make a real difference to someone’s life.

Addi: yep, that parallels really nicely with the tenant at our table was talking about one of the people she works with (She sits on a tenant panel and offers support to other tenants) has dementia and has got fixated on her laundry. This person will go out to the laundrette multiple times a day because she can’t remember if she has taken laundry there and left it. She will continuously go and check. And I said, well, if she had something like Alexa, and she could say in the morning “Alexa, I’m doing my laundry today”, then she could ask later. This stops her having to go out multiple times in a day… and we are talking about going out in bad weather as well. So, she would get a taxi or take a walk down to the laundrette because she has no-one to remind her.

Chris: Wow, that’s tough!

Addi: She doesn’t want to give up living in her home. She doesn’t want to go into a care home. Technology would enable her the ability to stay in her social housing home and not have to move into a care home…. And actually live better.

Chris: That’s very awesome. That really improves someone’s quality of life. I genuinely think that leveraging tech in that way makes a difference.

Chris. So in your opinion, realistically, how close do you think housing associations are getting to actually implementing this type of tech?

Addi: I think again, it comes down to the fact that everybody is at a different point in the journey. Based on what was said in the room, it seems everybody knows that the capability is there, actually delivering that is another story… Simply because, the HA’s themselves aren’t necessarily there yet. It has to come down to; getting the fundamentals in and working and then acknowledging that you are not building the fundamentals just for today, you are building for tomorrow as well.

Chris: So it’s very much about future proofing?

Addi: Exactly!

Chris: During the event, you must have interacted with a load of different types of people. What was the general consensus on the event?

Addi: Everyone really enjoyed it! The team that Rob Fletcher (Green Square Group) was on came up with something almost like a Pokémon go type app where when you are walking around your property it could actually pickup that there was a potential problem. It could see the damp and see the boiler lights flashing. After chatting away to people like Rob and the other vendors, everyone was getting really fired up. Yes, we were competing against other teams, but it was friendly competition. We were genuinely working together to hack these issues.

Chris: I’m actually super jealous that I couldn’t attend! So, do you think that these types of events are beneficial to the housing industry? Do you think there should be more of them?

Addi: YES! YES I do! I think HA’s want to come and I think they want to talk about what ever part of their journey they are at! They want to talk about where to go next. I think regardless of the size of the HA, there will be interest and enthusiasm. I’d really like to be part of running one with Hitachi actually! Everyone seemed to be really bought in. It wasn’t like we were trying to design something that was never going to happen. In the near future, a lot of this stuff is seriously possible. I think it would definitely be worth having more of these types of events.

Chris: Fantastic! I’ll be at the next one!

Chris: LAST QUESTION, I promise…. Do you think the Microsoft product stack aligns nicely with the requirements being made by HA’s?

Addi: The Microsoft stack is offering HA’s the ability to get all their data and processes into one place. They are sitting on so many disparate systems. A lot of the HA’s are locked into systems that they can’t easily configure or change themselves. If they want to add a few fields they need to pay someone a lot of money to do that. The Microsoft stack is flexible and Dynamic. It’s growing because of the investment behind it, which is really important. We also would love people to stay with Hitachi as their provider, but if they decided to move on, it’s not as if they are locked into something so bespoke they can never get away from it. I’ve worked at housing associations where people have been depressed because they are stuck with a system that is so bespoke and they can’t move away from the supplier. HA’s are ready for a solution that allows for them to be put on one platform and where they can control their data and processes and can still do all this innovation. Because it’s Microsoft, the innovation will just keep coming.

Chris: Oh for sure, the innovation will never stop! NEVER!!

Addi: Well, in the keynote speech at the hackathon, Microsoft stated that they are constantly updating their vision, which will then reflect on the customers.

Thank you so much to Addi for this awesome chat! As you can see through the information Addi has shared with us, Housing Associations are clearly ready to be taken on the digital journey with Microsoft and the focus on innovation is hugely important. I love the fact that the tenant on Addis team gave so much information and I think that plugging tenants into the digital journey and showing that some of the processes and technologies can make life A LOT easier creates massive value all round.