Formula result view is coming to Preview environment!!

Today (3rd, Apr), one of the BIG highlight in April 2019 updates of Power Platform is released to our preview environment, called “View results of formulas and subformulas in canvas apps”.

In this post, I briefly summarise its update, How to enable/What we can/Limitation etc..

*  As of 3rd, April, this experimental feature is delivered to ONLY Preview environment.

Enable experimental feature

To use this feature, go to “Advanced settings” and turn on a toggle “Enable formula bar result view”.

 

 

 

 

 

 

 

 

 

 

 

No need to save/reload app to use feature.

View collection records

At present, to view collection records in canvas for testing purpose, we usually add tmp gallery/data table control into app, and then see a result of our formula, such as Filter, AddColumn, LookUp…

However, as compositions get more complex, it can become difficult to understand the impact of each function on the result.  This feature helps us to understand what’s happening.

To view a formula results, you will simply select some formulas/collection/variables.  Below is quite simple example, if you select collection in formula bar (colTest), you will see summary of table records.

 

 

For more practical use,  if we want to preview a result of multiple filter condition to some collection, select formula Filter(xxx, condition1, condition2…), then we can get result of operation.

This feature is not only for viewing collections/tables, but also getting result of other formula, such as Concatenate(text1, text2…):

We can now easily and quickly understand and debug our apps with this feature.

Limitation

As far as I confirmed, there are some limitation for this feature;

  • Not support data source (unable to view records of some data source directly)
  • Not support nested collection (suppressed in view)

I think each of them is not critical for usual app making.

If I will find any further limitations/updates, will update this post.

Thank you!

Hiro

 

 

 

 

How to Start Your PowerApps Journey

I have been ask this question very often – I want to learn Powerapps but HOW? Where do I get started? I am not familiar with Excel so how do I get started? Well, we all know there are lots and lots of resources out there but sometimes it gets overwhelming .

Here few simple steps to get started:

1.Initiate a PowerApps Project

The easiest way to learn is by getting your hands dirty and built something of your interest. It does not need to be a massive full fledged app, It can be something simple and functional like a game or a . The satisfaction of the getting an app up and running will motivate you to do more.

2. Try-Modify-Expand other Apps

There is a lot of complete/partial built app in the TDG PowerPlatform Bank and PowerApps App Gallery.I find you learn a lot by playing, modifying and expanding an already built App Not only it help pushes your imagination, it gives you a different perspective in terms of build and design

3. Take the EDX Powerapps course 

If you like a more structured learning, I would highly recommend to take the EDX course. It has cool tutorial by Powerapps Guru- Shane Young and hands on tutorials which covers Powerapps, CDS, and Microsoft Flow. Best part is it is free but you can also choose to pay for a verified certification.

4. Attend App-In-a-Day Training

Or even better, reach out to Microsoft to check if there are any App-In-a-Day Training in your area or convince your manager to organise one. It is always fun to do it with your colleagues and friends.

5. Ask-Ask-Ask

The Power Platform community is awesome and generous. So don’t be shy to ask if you hit a roadblock. You can either post your questions on the following platforms:

a. TDG “Ask a Question”
b. Powerapps Forum

Or simply post a question to any Powerappers on Linkedin or Twitter. 🙂

6. Collaborate

One thing I learn being part of the TDG community is sharing is caring :). Being selfish does not bring you anywhere. Do not hesitate to collaborate with anyone in the community. I have learned heaps by collaborating with the Japanese PowerApps community as well as the TDG community.

7. Powerapps Bible

There are few good resources if you want to learn more bout Powerapps Functions:

  1. The Powerapps Microsoft Doc is your go-to bible when you need a function reference.
  2. Vlogs and Blogs – You can find very useful PowerApps vid and blogs in TDG Media
  3. Twitter – Follow Powerappers like Brian Dang, Audrie Gordon, Hiro and Rory Neary and many many more

8. Practice – Practice – Practice

Yes. It takes time, patient and determination to learn something new. But it also takes lots of practice. I am not an Excel expert but I came from a programming background ( I am talking about 13 years ago so I am extremely outdated and rusty).and this is a huge learning curve for me. But anything is possible as long as you approach it with the correct mentality. So, all you need to do is approach it with the right mindset and you will get there.

Happy Power-apping! 🙂

How to create resource in azure: Cognitive Services & PowerApps Part two

This is a carry on from How to create resource in azure: Cognitive Services & PowerApps Part one.

So now we have the “Text Analytics” resource setup let’s create the CanvasApp PowerApp, head over to web.powerplatform.com and select apps- then import package, import the Text Analytics Canvas app from our TDG PowerPlatform Bank by clicking here!!!

Once the app has imported and you have opened it  go to – “View”>”Data Sources”:

Select, at the top left of the pane that opens, “+ Add Data Source” then “+ New Connection”,  then search for “Text Analytics”:

Then select “Text Analytics” then it will ask you for the “Account Key” & “Site URL” (which is actually the Endpoint URL), this can be found in Azure. Go to Azure, All Resources, then Select the “Text Analytics” resource you create in step one .

Then select either “keys” from the ‘Grab my keys’ main grouping or “Keys” from the Navigation pane “Resource Management” groupings:

Then copy your first key:

Then paste that back into the PowerApp Data Source under “Account Key”:

Now to grab the URL Endpoint, head back to Azure – go to “Overview”:

Then copy the “Endpoint”And paste that back into PowerApps in the “Site URL” field:

Then hit create.

Now you’re ready to use the app – explore the formulas and controls used, you’ll find it’s easy to replicate. If you have a question comment on this blog or reach out to William Dorrington directly via LinkedIn.

 

 

 

How to create resource in azure: Cognitive Services & PowerApps Part one

So you’ve seen all this discussion around Cognitive Services and now want a piece for yourself – you open your computer, you down a coffee and now you’re thinking “what now”?

Well don’t worry, I’ve got you!

Let’s start with Text Analytics, crack open Azure (https://portal.azure.com/#home) and select “Create a resource”:

Then in the search bar search for “Text” and then select “Text Analytics”:

Then select “Create”:

Then enter the following; Name, Subscription type, Location (locations of where the resource will be held), pricing tier and resource group allocation (for filtering and searching properties). Once happy select “Create” and you’ll be notified when the resource is ready.

Once it is completed it will be available via the notification of completion message (via link) or in “All Resource” (from the navigation pane to the left hand side of the screen), then look for “TextAnalytics” or whatever you named your resource. From here you can view your “Keys” or if you need the endpoint URL select “Overview” and then you’ll see a URL under the category of “End point” e.g. https://uksouth.api.cognitive.microsoft.com/text/analytics/v2.0 (the URL only changes dependant on the location you selected e.g. https://[location].api.cognitive.microsoft.com/text/analytics/v2.0).

Now to create the PowerApp data source, find part two here..

PowerApps Export Collection Data as CSV

This came in handy when I needed the user to be able to export data in a collection for  to be used in Excel. Sample App available in the PowerApps bank.

As an example, I have a collection called Contacts with 3 records, this is the collection I will be exporting.

ClearCollect(Contacts,{Firstname:”Charles”,LastName:”Osei”,Number:”123456″ } ,{Firstname:”Charlie”,LastName:”Bradbury”,Number:”8765432″ },{Firstname:”Joh”,LastName:”Smith”,Number:”123456″ } )

On a button. Create a collection called ExportCSV with the same columns as  the contacts collection, but pre populate the first row with the column names , this will be the CSV`s column headers.

ClearCollect(ExportCSV,{Firstname:”First Name”,LastName:”Last Name”,Number:”Number”})

 

Next step is to copy all the Contacts into the ExportCSV collection

ForAll(Contacts,Collect(ExportCSV,{Firstname:Firstname ,LastName:LastName, Number:Number}))

 

Create the CSV string into a variable by concatenating all the columns with a comma. You can add in your extra columns by  adding &”,”&

but the concat  must end with “& Char(10)”  as this separates the next line.

Set(MyString,Concat(ExportCSV,Firstname&”,”&LastName&”,”&Number& Char(10)))

 

Output the MyString variable to a multi-line text box by setting its default value to MyString. Which should look like the below.

First Name,Last Name,Number
Charles,Osei,123456
Charlie,Bradbury,8765432
Joh,Smith,123456

You can now copy and paste that text into notepad, save it as something.csv. The file can now be opened in excel,you can use the column headers as filters or to create a table.

 

 

PowerApps – Running Functions in Parallel using Timers

In a previous post I used PowerApps to query Dynamics to check if a list of email addresses exists in my instance as contacts. I used the ForAll function to lookup Dynamics for each email in my collection sequentially. This worked  fine until I had to check if 500  emails exist in  my instance containing just over 300,000 contacts.

After doing some performance bench-marking ( Beginning the search and starting a stop watch on my phone) this query took 12 mins 53 seconds to run. Would be great if multiple lookups could run in parallel. I used timers to run the exact same ForAll statements three extra times , similar to separate threads which greatly improved the speed. Although I had to make sure each email wasn’t processed multiple times.

My original statement was:

ForAll(SearchEmails,Collect(Matches,LookUp(Contacts,emailaddress1 = Result))))

SearchEmails has a column called Results which contains the emails I want to find. For every email in SearchEmails , check if it matches the email address 1 field of a contact record in Dynamics. If one is found add it to a collection called Matches.

ClearCollect(ProcessedEmails,{email:” “});

I created a collection ,ProcessedEmails, this will store emails that have already been searched for.

Edited the original ForAll statement to only lookup emails that do not exist in the processed emails collection. Before the email is looked up, it is added to the processed list so another thread/Timer doesn’t pick it up but moves onto the next email.

ForAll(SearchEmails,If(!(Result in ProcessedEmails.email), Collect(ProcessedEmails,{email:Result}); Collect(Matches,LookUp(Contacts,emailaddress1 = Result))))

The above ForAll runs  on visible of my search page, I also then created  3 timer controls  each with the same for all statement in their on timer end functions.

Timer Properties

Auto-start : true , Repeat = false.

Durations

Timer 1 4000

Timer 2 8000

Timer 3  12000

When the search page is visible,

  • The original search will run on the OnVisible function
  • Timer 1 will start searching  four seconds later
  • Timer 2 four seconds after timer 1
  • Timer 3 four  seconds after  timer 2

This was to stop all my timers starting the search at the exact same time and processing the same emails.

With the For All lookup running 4 times the search went down from 12:53 mins to 3:21

Im sure additional timers could be added to possibly improve performance even further

Full formulas used
Search Page on visible

(can be applied to a button instead but make sure you start the timers)

Clear(Matches);ClearCollect(ProcessedEmails,{email:”something”}); Set(vSearching,true);ForAll(SearchEmails,If(!(Result in ProcessedEmails.email), Collect(ProcessedEmails,{email:Result}); Collect(Matches,LookUp(Contacts,emailaddress1 = Result)))); Set(vSearching,false)

Timers OnTimerEnd

ForAll(SearchEmails,If(!(Result in ProcessedEmails.email), Collect(ProcessedEmails,{email:Result}); Collect(Matches,LookUp(Contacts,emailaddress1 = Result))))

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 February, 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!

 

 

 

PowerApps Quick Tip: Use Alt+Spacebar to test your apps in the canvas

App makers like us often need to test apps in an incremental fashion to help progress the app design by validating formulas, expressions, data manipulation, control settings etc. Knowing the shortcuts to test apps in the canvas/web studio will save us time!

Here are two Windows keyboard shortcuts to improve your app testing experience:

  1. Alt key (Ctrl+Shift key on IE)
  2. Alt+Spacebar

Most of us know holding the alt key (or Ctrl+Shift on IE) while clicking will activate buttons or other controls. To change the text in input boxes, you can double click on them.

But wouldn’t it be ideal to switch the entire app to test mode within the web studio while you are activating buttons/controls, filling in input boxes and navigating between screens to validate changes as they are being worked on without having to hold shortcut keys for every click?

That’s what Alt+Spacebar does for you. Please note that this keyboard shortcut does not work on IE and Ctrl+Shift is the way to test controls & buttons on IE. Also, using Alt+Spacebar can pop up the shortcut drop down menu for the current window. You can click anywhere on the gray studio area to ignore it and then continue testing your app.

Press the Alt or Shift key to escape test mode and you are back on design mode.

NOTE: This is not a Microsoft supported shortcut but it works.

Happy PowerApp’in & Testing!

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!

New Admin Center – PREVIEW – System Settings Walk through

PLEASE NOTE : THIS POST IS BASED ON A PUBLIC PREVIEW FEATURE.

The video will give you a good idea of what is currently available within the new Admin Center preview system settings within the PowerApps user interface. Microsoft are always adding, updating and improving. This video is no holds barred. If a screen doesn’t work, it doesn’t work… But better to know than not to know. Check it out here…

You can download the PDF version of the presentation HERE. This will give users / admins the ability map the paths in the new UI to the current navigation paths in the classic UI.

I really hope this helps someone out in the quest for PowerApps knowledge and appreciation.

As always, please contact me or leave a comment if you need more information.

SharePoint capped at 500 records- Data Row Limit for non-delegable queries

So I was busy creating a nice Canvas App using the brilliant Patch command when my records stopped transferring across to SharePoint at exactly 500 records.

Knowing that SharePoint can hold many more entries than this I was slightly confused – on deeper investigation I found the little gremlin blocking my patch commands (File>App Settings>Advanced Settings):

When increasing this limit it allowed my records through. I hope this helps someone out there!!!