ODATA Queries in PowerAutomate made easy

Wow thats a lot of data. Never fear, ODATA is here!

Hi, and welcome to another tutorial from myself and the team here at NextStep Creations. Today we’re going to be talking about ODATA queries.

ODATA queries are a fantastic way to filter or organize your data in PowerAutomate. They can be used for many different sources, such as:

  • SharePoint
  • SQL
  • API calls
  • Dynamics

In this guide, we’ll run through how to use OData queries and some example use cases.

Basic ODATA queries

The general syntax of an ODATA query is:

fieldname operation value

The fieldname is the column or field that you wish to search (something like customerName, customerType, mobileNumber)/

The operation is one of the following:

  • eq: Equal
  • gt: Greater Than
  • lt: Less Than
  • ge: Greater Than or Equal To
  • le: Less Than or Equal To
  • ne Not Equal

You can also stack more than one operator/query, by using “AND” or “OR” to specify multiple conditions.

And finally, value is what you are comparing your column to. This can be a string, a date, an integer, a floating point number, or even a boolean (true/false). 

Note: Flow requires single quote (‘ ‘) around text in the value section – customer eq ‘Jacob’

Using an ODATA Query

Alright, lets get into it. First, create a brand new flow. Just use the “Manually trigger a flow” start point for now.

In this example, I’m going to add a “Get Items” step, that will pull all my items from a SharePoint list. I’ll run the Flow, so we can see what (unfiltered) data we get:

No ODATA query

There’s a fair few lines of data there. I don’t need all of them…..

Let’s say I only want the items from the list where the title is “odata”. Go back and edit your Flow, and you’ll see an option in the Get Items step called “Filter Query”. In that box, put the following query:

Title eq ‘odata’

Save it, and run your flow:

ODATA Simple Query

Voila! That’s a much smaller list, with only the matching items in it.

Think of the possibilities….no more having to loop across all of your data trying to find a certain value., you can just make sure you only have items that match your criteria in the first place.

Multiple ODATA Queries

The fun doesn’t stop there! Let’s say I need to find items with the title of “odata”, and I also need to find the one with the ID of 11.

Edit your Flow again, and change your filter query to:

Title eq ‘odata’ and ID eq 11

Time to run that flow! Let’s see what we get:

ODATA Double Filter

Awesome! One item and one item only. That’s a nice way to filter out data before we even operate on it.

Wrap up & thoughts

This is a basic intro to using filter queries in PowerAutomate. Stay tuned for a deeper dive blog into some more of the intricate functionality.

Reach out to us on Twitter at NextStep Aus, we love to see what you make out of our tutorials!

PowerAutomate and JSON extend PowerApps

Extending PowerApps with the use of PowerAutomate and JSON Responses, This is going to be a quick one for a Friday Night as I have been asked this question quite a few times in the past week so time to make a blog post.

As we discussed in our last blog post on JSON (See it here: PowerPlatform and NSC Love JSON) We know how to handle Arrays, objects and multiple data types but how do we get that useful data back to PowerApps!

Well many of your may know the connector. “Respond to PowerApps”

respond to PowerApps or PowerAutomate

Now with this guy, there is one glaring issue. Its TEXT ONLY! Now I don’t know about you… but we have made this work passing things other than text… Lets not go back there.. or mention it again….

However have no fear! We can use something called a HTTP Response. Now the beautiful thing about this response action is that we can define the expected output of data. If we need PowerApps to see an Array… Great! We can build a Schema to send that off.

Now this sounds scary once again… its adding code to your no to low code solution but hang in there with me.. PowerAutomate pretty much does it for you.

In this blog I will be using a SharePoint list as my data source…. But it could be anything.

The PowerAutomate Setup!

The First thing you are going to want to do is create a very basic flow. This is so that we can gather the expected output of your data. This flow launches from PowerApps.

(Using Test in the top right allows you to fire this off as if you were in PowerApps)

basic PowerAutomate Workflow

Running this flow will give us this lovely output that we need to generate our response!

PowerAutomate SharePoint Response

The bit we are after is everything under after the { “value”: make sure you include and copy the open and close square brackets! [ ]

Keep this copy aside somewhere… We will need it soon!

Now your response will be very different to mine so there is no point pasting mine here. However what you need to take away here is you can remove columns by removing their names. For example you see “@odata.etag”: I don’t want this column to appear so when I generate my schema I am going to leave it out.

Finally… The PowerAutomate Reponse!

We are going to add one step to our flow. 
PowerAutomate Response

Ensure you have chosen your body value from the step above. Essentially this is where the response will be getting its data from.

The last step. Click Generate from sample and paste in the entire JSON output from your Get SharePoint List items Connector (In my case)

What this will do is then generate a schema which will format the data and types that will be sent back to PowerApps as seen below:

PowerAutomate Resposne Schema

Now three things I would like to mention here to help some heart ache.

  1. Make sure you define types for everything in your response. PowerApps hates undefined types
  2. Make sure your required items in your Schema will always have data in them
  3. Finally if you change your schema after the PowerAutomate Workflow is added to PowerApps… You have to remove and re-add it… Otherwise it will always expect the original schema.

On PowerApps end your code on your button will look something like this:

ClearCollect(tableFromResponse, getData.run())

All this does is collects your output of the PowerAutomate Workflow into a collection to be used in PowerApps!

And with that. Have a fantastic Weekend!

Ben Out!

Reach out to the team on Twitter at NextStep Aus and let us know what you’ve built!

Check out some of our other posts here:
PowerAutomate QuickBits 2 – Handling APIs Efficiently
Running SQL Queries in PowerApps
PowerApps QuickBits – Radio Button Reset Solution

PowerPlatform and NSC Love JSON

Have you ever wanted to do more with your favourite PowerPlatform and hit a wall? Well today we are going to shed some light on one of those walls… JSON

Now. Some will tell you that you require quite a bit of knowledge to understand and use JSON.

I am here to tell you today. They are dumb. You be you. Sit back, relax and read

NextStep Creations Basics of JSON and the PowerPlatform

So we start by ignoring the PowerPlatform as we need to layout some basic concepts first.
In programming there are these things called type definitions. They do exactly as they say. Define types.

Some of the common types you will see as you start to Delve a bit deeper are as follows:

  • Integers (Also known as numbers or values) <– this little guy is a whole number so 123 is an integer and so is -123
  • Strings (Also known as text or str) <– this little guy is normally surrounded by “HI! I’m a string! “, it can also be surrounded by ‘yes these’
  • Booleans (Also known true/false, yes/no, on/off) <–This is as it states only two values. An ‘on’, ‘yes’, ‘1’ or ‘true’ and an ‘off’, ‘no’, ‘0’ or ‘false’
  • Objects (Also known as a record) <– Think of this guy as a row in a table. It will contain data for that entire row.
  • Arrays (Also known as a table) <– Think of this guy as the entire collection of items. You could say an Array is a collection of objects
  • NULL (Also known as blank) <– This one sometimes trips people up. As people assume that 0 or “Zero” is Null. However this is incorrect. Think of it this way:
    Zero: You have a toilet roll but there is no toilet paper left. You still know its a toilet roll though!
    Null: You have no toilet paper and no toilet roll.
  • Undefined <– this guy…. this bloody guy…. will ruin your day if you forget about him. Here is a nice image to assist me in defining what undefined is 🙂

JSON types - 0 null and undefined
Okay so now we have those little guys out of the way. Lets get into the SCARY code stuff.
Here is an example describing a person… We are going to break it down:

{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 27,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [],
  "spouse": null
}

Okay so every JSON file will contain { } these guys. They generally start and finish objects.
As you can see above prior to First Name there is an open tag { then finally after spouse there is a close tag }

Starting from the top! “firstName”: is the first name column! now pay close attention to the quotes and the colon. For a column name quotes ‘ ” ‘MUST start and close the name followed by a colon ‘ : ‘.

Now we have {“firstName”: “John”, 
Here John is defined as the value for the firstName column. Please also note its surrounded in quotes as its a string and the line is closed with a comma ‘ , ‘. Something to note here, these commas have to be placed on every line of an object except the last! Remember this!

Moving down. “lastName”: is another column definition with the value “Smith”, <– again notice the comma.

“isAlive”: true,  <– this one is slightly different as the words true and false without quotes surrounding them are defined as boolean values as described above.

“age”: 27, <– this value doesn’t require quotes either due to numbers being defined as integers if quotes are not surrounding the digits. So if you need a number type… Lose the quotes.

now “address”: { <– ooh! This one has another open tag. Meaning we have our first nested object. The contents of this one are clearly defined the same way. The biggest thing to note here is that the object MUST be closed so at the end you will notice }, meaning im finished defining this and move onto the next column

“phoneNumbers”: [ <– yay! learning! This one is the start of an array. Now remembering as above that an array is a collection of objects. Meaning you will generally see something like {object1}, {object2}, {object3} now these objects will be surrounded by [] as this dictates the array nature. Again ensure that you close this Array by using ], saying hey! I’m done here move onto the next column.

Okay now we have broken down how to read JSON. Lets move onto how our beautiful PowerPlatform reads JSON… SCHEMAS! 
We have performed some magic hiddin inside a JSON to JSON Schema converter to create the below:

{
"$schema": "http://json-schema.org/draft-04/schema#",
"type": "object",
"properties": {
"firstName": {
"type": "string"
},
"lastName": {
"type": "string"
},
"isAlive": {
"type": "boolean"
},
"age": {
"type": "integer"
},
"address": {
"type": "object",
"properties": {
"streetAddress": {
"type": "string"
},
"city": {
"type": "string"
},
"state": {
"type": "string"
},
"postalCode": {
"type": "string"
}
},
"required": [
"streetAddress",
"city",
"state",
"postalCode"
]
},
"phoneNumbers": {
"type": "array",
"items": [
{
"type": "object",
"properties": {
"type": {
"type": "string"
},
"number": {
"type": "string"
}
},
"required": [
"type",
"number"
]
},
{
"type": "object",
"properties": {
"type": {
"type": "string"
},
"number": {
"type": "string"
}
},
"required": [
"type",
"number"
]
}
]
},
"children": {
"type": "array",
"items": {}
},
"spouse": {
"type": "null"
}
},
"required": [
"firstName",
"lastName",
"isAlive",
"age",
"address",
"phoneNumbers",
"children",
"spouse"
]
}

Now please don’t get confused here. All you are seeing is the definition of what we have explained above.

These beautiful schemas are VERY important as they allow us to add predictability to our low code solution. We know what will be a string, Boolean, array or object.

In the above you will see the definition of each column and then its type defined just after. Now the reason I wanted to expand into Schemas in this post is these are where the headaches come from. 

In a perfect world the data we enter in the system will be perfect… but lets be real… users are dumb…

So if your isAlive field requires a yes or no and some how the user enters “hello world” your system will catch fire.

Now you may want it to break if this happens. However. Sometimes you may need you system to keep running. What you could do is remove the type definition from your defined column so instead of:
“isAlive”: {
“type”: “boolean”
}
it would be:
“isAlive”: {}

Something to note however. If you are passing data back to PowerApps… PowerApps hates undefined types. So don’t do this.. XD

Also something that might cause you headaches is this bit:
“required”: [
“firstName”,
“lastName”,
“isAlive”,
“age”,
“address”,
“phoneNumbers”,
“children”,
“spouse”
]
This states that all of these fields are REQUIRED for your JSON to parse the data correctly. Now if for some reason age doesn’t always have data. Your environment will cry tears of blood. Not to fear! You can remove the required column names with the simple backspace key along with your hours upon hours of time spent debugging this dumb issue.

So something to note is you can also remove all of them so your required portion of the schema could look like this:
“required”: [] <– just be careful as you MAY need to ensure data is in some fields.

Okay.. This is the walkthrough, breakdown, explanation, whatever you want to call it of something that took me much longer than I would like to admit to FULLY grasp. 

I have not explained everything. Nor will I. However I hope this little post of mine can help you in expanding your apps, workflows or automation! If you have any questions or ideas, don’t hesitate to reach out to myself and the team on Twitter: NextStep Aus

Automate on people.

Ben out!

Check out some of our other posts here:

Simple Inventory Management App – Part 1/3

“Hi, do you have any more of these in stock?”

Sure, let me check my inventory management app!

Hey PowerAddicts! Welcome back to NextStep Creations. This post marks the first of three instalments, in which we’ll guide you through creating a simple, but powerful inventory management application.

First off, let’s look at what you’ll be building over the coming tutorial!

Finished Inventory App

At the end of this series, you’ll have a nice, pretty inventory app, that will allow you to:

  • Scan barcodes and enter a product description and quantity
  • Save these barcodes,  descriptions and quantity to SharePoint or SQL
  • Display a list of all your inventory
  • Search your inventory and order the results by different criteria

Here’s a screenshot of the finished layout:

<———————–SCREENSHOT OF FINISHED HERE———————–>

Building the inventory front end

Let’s start by creating the basic layout of the application. We’ll have a home screen with 2 buttons, labeled “Enter Item” and “Search Items”.

Initial inventory home screen

Straightforward so far. In this part of the project, we’re going to be focusing on creating the Enter Items section.

Building the “Enter Items” screen.

Create a new screen in PowerApps, and set it up like so:

Enter items setup

There’s a few little things here that you’ll have to set up.

The “Scan” button is actually a barcode scanner object in PowerApps. You can add this by going to “Media -> Barcode scanner” in the insert menu at the top:

Add barcode scanner

In the “Product Description” text input, set the Mode value to:

TextMode.MultiLine

In the “Quantity” text input, set the Format value to:

TextFormat.Number

Also set the “VirtualKeyboardMode” to:

VirtualKeyboardMode.Numeric

Text Format quantity input

Perfect! The rest are just standard buttons and Labels, so lets move on.

Handling barcodes – pulling the scan data (the critical part of inventory management)

Time to get to the fun part. Reading the barcode numbers from your camera.

Note: You will have to use the application on your phone/tablet, as currently PowerApps does not support barcode scanning in the web version.

Alright! Select the “Scan” button we set up before, and find the “OnScan” action. Set the value of it to the following code:

Set(barcodeNum, barcode_enterItems.Value);

This will set a variable called barcodeNum to the value of the barcode scanner object we inserted before.

Now, the label you have that says “Barcode number: “. Time to append the barcode number to this. Set the “Text” property of this control to:

“Barcode number: ” & barcodeNum

Now go ahead, save your app and give it a test! Remember to add a Navigate() function to your home screen so you can get to the enter items page.

When you click the scan button, you should be presented with the camera on your phone, and a nice green square, like so:

Barcode camera scanner

When you hover over a barcode, you’ll hear a “BEEP” and then it’ll take you back to the app. Everything going to plan, you should see your screen with a barcode number on it:

Simple Inventory Management App - Part 1/3 1

You can fill out the description of the product, and add in the quantity of the product you have.

And that’s it for part 1! In the next part, we’ll look at setting up the back end so that we can save our scanned items to SharePoint or SQL. In part 3, we’ll look at searching the database and adding other cool functionality.

Hopefully by now you can start to see the power of this application, and your mind is racing with ideas. Leave a comment below or reach out to us on twitter at NextStep Aus, we’d love to see what you made!

Check out our other blog posts here:

PowerAutomate QuickBits 2 – Handling APIs Efficiently

You get an API! You get an API! Everyone gets an API!

Hey everyone! Welcome back to another installment of our QuickBit series. This time, we’re looking at how to easily utilize APIs in PowerAutomate.

What is an API?

API stands for “Application Programming Interface”. An API acts as a sort of middle man for requests you wish to make to an endpoint. For a deeper understanding, have a look at this link.

The main reason we want to use an API is so we don’t reinvent the wheel. If you can think of something, there’s usually an API for it. Some interesting examples include:

Today, we’re going to be picking one from the above list, and one of my personal favourite APIs.

“I’m a creative genius.”  – Kanye

Time to build our Flow.

You guessed it, we’re using the Kanye API!
This API will provide you a random Kanye quote when you query it.

HTTP Calls – how to tell the API what to do:

When you query an API, you will be using different methods depending on what you would like to do. The main ones you may use are GET, POST and DELETE.

  • GET is used to request data from a specified resource
  • POST is used to send data to a server, or to create a resource.
  • DELETE is used to delete the specified resource

More information can be found here: HTTP Methods
Can you guess which one we’re going to use?

Implementing the GET method in Flow.

Let’s get into it! What we’ll be doing in this tutorial is setting up a flow that, every day at 8am, will grab a random Kanye quote and post it to yourself in teams! What a way to get motivated for your day.

First, create a new scheduled Flow. Name the flow, and set it to run every morning at 8am:
Schedule Setup

Click create, and then add a HTTP Step:

Add HTTP Step

Perfect! Now, the “Method” section is where we pick our HTTP method, as described before. Set it to GET, and in the URI add in:

https://api.kanye.rest/

HTTP Kanye Setup

Awesome, well done so far! Lets grab the content that we need and send it to Teams. Add the “Post a message ass the Flow bot to a user” step, and fill it out as so:

Teams Kanye Setup

We’re almost done! Lets run a test and see what it looks like.

Teams message Kanye quote

Oooh, so close! That’s okay, I’ll show you how to fix this so it displays just the quote, none of that weird curly brace stuff (which is JSON by the way, check it out here……PowerPlatform and JSON).

Back into Flow, we only need to do one simple thing! In the message section of your “Post a message as the Flow bot to a user” step, set the “Message” to this (make sure you’re in the expressions tab when adding this!):

body(‘HTTP’)[‘quote’]
Run the test again, and lets see what we’ve got:
Genius text Kanye quote
 

Wow! Thanks Kanye, I couldn’t have put it better myself.?

Closing thoughts

Think of the possibilities now! You can use ANY API you want to like this, and pull data into PowerAutomate. And once it’s in PowerAutomate, your options are endless.

Check out some of our other content here:

Cheers
Jacob | Automation Specialist

PowerApps – Create Beautiful HTML/PDF Reports

Today we are back again…. Turning our boring HTML Table from PowerApps into a beautiful CSS Table with embedded images and with my limited HTML and CSS knowledge make it…. customer friendly

 

Do you know the initial setup?

If not please find my previous blog post below:
Create HTML/PDF Reports from PowerApps

Updating our previous flow:

The very first thing we are going to need to do is add a new string variable with a name we can remember!

PowerApps - initialise variable header content

Now we have our empty string… Lets do some HTML!

WAIT… But Ben… I dont know HTML….

Have no fear! W3 Schools is here! HTML Tutorial

< !DOCTYPE html >
< html >
< head >
< title > HTML Report < /title >
< meta charset=”UTF-8″ >
< meta name=”viewport” content=”width=device-width, initial-scale=1″ >

Now if I can say anything…. Please do NOT copy the code below and expect it to work… The reason for this…. Bloody Quotations!
This is a quotation that wont work
This is a quotation that will work "

Special thanks to Reddit User ‘u/Knovar’ for experiencing this little bug so we can add it to the blog steps 🙂

Just make sure your quotes, symbols and special characters are on point as sometimes HTML and Website text editors like to just change things!

Now you will notice I am not adding the < style > tag here. This is for two reasons:

1. I am going to add quite a bit of CSS here… (Don’t know CSS? W3 Schools to the rescue! CSS Tutorial) The one thing variables in PowerAutomate does not like is lines and lines of visible text.

2. I don’t want to have to scroll or search 17 times looking for the once piece of HTML or CSS that I am trying to edit or change!

Next we are going to create another empty string variable.. This time instead of header content… you guessed it… var_HTMLStyle.

< style >
/*Set the Page size*/
@page {
size: A4;
}

/* Style the body */
body {
font-family: Arial;
margin: 0;
}
#limit {
max-width: 550px;
word-wrap: break-word;
}

/* Page Content */
.content {padding:20px;}

#customers {
font-family: “Trebuchet MS”, Arial, Helvetica, sans-serif;
border-collapse: collapse;
width: 100%;
}

#customers td, #customers th {
border: 1px solid #ddd;
padding: 8px;
}

#customers tr:nth-child(even){background-color: #f2f2f2;}

#customers th {
padding-top: 12px;
padding-bottom: 12px;
text-align: left;
background-color: #264c79;
color: white;
}
< /style >
< /head >

The above code sets some CSS Styles for my tables and my body content as well as a very important step…

It sets the page size as ‘A4’
If you intend on exporting this to PDF… You will love this little gem..

Okay home stretch we are going to replicate the above two steps..

But we are going to create a var_HTMLBody this will include
thetag as well as some text if you desire

for us it will just be < body >

Then we are going to create a var_HTMLTable

This is the fun bit… The below piece of code (which will be the content of our var_HTMLTable String variable) Defines the tables Header, the tables CSS Style and the table header columns. Notice we do not yet close the table HTML

< h2 > Ben’s Money Breakdown < /h2 >
< table id=”customers” >
< tr >
< th > Name < /th >
< th > Cost < /th >
< /tr >

We are now going to create an apply to each loop from our previous ParseJSON and do a little Append to string magic 🙂

At this stage you can delete the previous Create HTML Table control as we are about to be replacing this.

First Create the apply to each and give it the ‘body’ content from the ParseJSON Response from earlier.

apply to each & append

As you can see above we have added an Append to string variable action… Now the magic…

< tr >
< td > @{items(‘Apply_to_each’)[‘description’]} < /td >
< td > @{items(‘Apply_to_each’)[‘purchase’]} < /td >
< /tr >

This builds our table row… One at a time… in the correct order…. 🙂

It should look like this:

apply to each filled out

Now after our apply to each we have to add some important steps.
It joins all our string variables together to make our finished HTML

The steps are as follows:
1. Close our Table with < /table >
2. Close our body with var_HTMLTable < /body >
3. Close our HTML Header with var_HTMLStyle var_HTMLBody < /HTML >

It should look something like below:
finish HTML

Finally use the var_HTMLHeader as the file content for your Create File action from the first Blog

create file

Now lets run this with content from before:

new table with css

Old Table for comparison:

old table without css

Hope this helps!

Taking your PowerApps and PowerAutomate Skills the NextStep

Ben Signing off!

PowerAutomate – Transforming Lat/Long to an Address

Welcome to -33.796141, 151.178358! Wait, that doesn’t look right.

Hey everyone! Welcome to another tutorial from myself and the team at NextStep Creations. Today we’re using PowerAutomate to convert a latitude and longitude value to a readable address. In a previous blog we looked at Google Maps in PowerApps.

In this, we cover the ability to use a devices GPS coordinates to provide a location. But, if you were ever to display these, or print them or send them to a report (see Creating a HTML Report), they would come out as just a string of numbers, which isn’t ideal.

See Google Maps might know what to do with those numbers, but we don’t. So I’m going to show you how to convert those into something a bit more “human”.

Step 1 – Set up your Flow (PowerAutomate)

Make sure you have a Google Maps Developer account. See the Google Maps in PowerApps tutorial for info on how to do this. 

For this section, we’ll need to get an API Key for the Geocoding segment. Here’s a tutorial on how to do this: Get Geocoding API Key

We’re going to be working with PowerAutomate (Microsoft Flow) and PowerApps today.  Create a new flow, and name it “convertAddress”, Add the PowerApps trigger, and then a HTTP step after that:

PowerAutomate Setup

Perfect! Set the Method to “GET” and set the URI to the following code:

https://maps.googleapis.com/maps/api/geocode/json?latlng=@{triggerBody()[‘HTTP_URI’]}&key=YOUR API KEY HERE

Make sure to replace the key value with your API Key. You should now have a HTTP step that looks like this:

PowerAutomate - Transforming Lat/Long to an Address 2

Sweet! Let’s move on. In this next section, I’ve done a lot of the heavy lifting for you. In another blog post we’ll talk about how to correctly set up a Parse JSON step, but for now, here’s one I prepared earlier.

Add a Parse JSON step, and set the “Content” to the Body of the HTTP step previous.
In the schema, click this link and copy all of the text. Your Parse JSON step should look like so:

PowerAutomate - Transforming Lat/Long to an Address 3

Lets create a variable to store the address in. Add an Initialize Variable step, name it formattedAddress, set the type to String and the value to:

body(‘Parse_JSON’)?[‘results’][0][‘formatted_address’]
More on the above in the blog we’re writing about JSON. 
Now add a “Respond to a PowerApp or flow step, and set the output variable to the formattedAddress variable we made:
PowerAutomate - Transforming Lat/Long to an Address 4
 
Brilliant! Time to go to PowerApps.

Step 2 – Create PowerApps & tag the Flow

Alright, nice and easy. Create a new PowerApp, and add a button. We’re going to create a proof of concept app, to show that your latitude and longitude can be successfully converted to an address.

You can expand on this if you wish by adding the Google Maps ability like we’ve shown: Google Maps in PowerApps

Here’s my app layout:

PowerAutomate - Transforming Lat/Long to an Address 5

To get the text label to show my location, I’ve set the text property as follows:

“My location: ” & Location.Latitude & “, ” & Location.Longitude

This is using the in-built Location functionality in PowerApps.
Read more about that function here: Acceleration, App, Compass, Connection, and Location signals in Power Apps

Alright, let’s add some functionality! Add the flow to the button:

PowerAutomate - Transforming Lat/Long to an Address 6

In the OnSelect of the button, set a variable to the response value we defined (formatteddAddress) to a variable using the below code:

Set(convertedAddress, convertAddress.Run(Location.Latitude & “,” & Location.Longitude).formattedaddress)

PowerAutomate - Transforming Lat/Long to an Address 7

So, we’re just passing the latitude and longitude of the device, separated by a comma.

Now, set the bottom text label (“My address: “) to the following:

“My address:

” & convertedAddress

This will add a few blank lines, and then when you click the button will display your address.

Give it a try! You should see a result like so (I’ve blanked out some of my address for security reasons):

PowerAutomate - Transforming Lat/Long to an Address 8

And that’s it! Congratulations, you’ve got yourself an actual address.

Happy PowerApps developing, until next time.

 

Google Maps in PowerApps – Fast & Efficient

Come one come all, to an undisclosed location on the Internet!

Oh wait, we have Google Maps now…..here’s the address. Hey everyone, Jacob here again. Today we’re going to be discussing using the Google Maps API inside of your PowerApps to give easy to use, easy to visualize locations, either by using the physical device’s latitude and longitude, or by manually typing in a street address. First off, here’s a little teaser of what you can expect to make at the end of this….

PowerApps Demo Final PowerApps Demo Final

As you can see, we have a fully functioning location setup! We can either use a manually entered address (first photo), or use the devices inbuilt GPS functionality to figure out the coordinates of the user!

Step 1 – Design the PowerApps Layout

Let’s get started. Fairly simple PowerApp layout, I’ve added notes on this image to make it easier to understand:

Google Maps in PowerApps - Fast & Efficient 9

Okay! Nice and easy so far. You’ll see that in that photo I mentioned I’d elaborate on the latitude/longitude text label, so here goes:

  • This label is set up to show either the current Latitude and Longitude of your devices if the address input is blank.
  • If it isn’t blank, it will display whatever address has been manually entered.

To achieve this, set the Text property of the label to the following:

If(IsBlank(ti_addressEntry.Text), Location.Latitude & “, ” & Location.Longitude, ti_addressEntry.Text)

Step 2 – Getting the static map setup in Google

So this part requires a few moving parts. Let’s break it all down.

Getting a Google Maps Static API Key

To get images from Google we need to have a key. A key is tagged to a Google account, and verifies that it’s you making the request.

To get this, we need to sign up to a Google Developers account. You can use your existing Google/GMail account for this. Follow this link and click “Get Started” to sign into your account: Google Developer – Maps

Create your first project and agree to the terms:

Google Maps in PowerApps - Fast & Efficient 10

You’ll have to set up a billing account in the next stage. I won’t walk you through this section as it’s fairly straightforward.

You’ll need to get an API key next, Here is a great guide from Google on how to do so: Get an API Key

Okay! Once you’ve completed all that and got your Google Maps Static API Key, it’s back to PowerApps!

Step 3 – Configuring the image

What we’re going to do is use the Google Maps API to return us a static map image that we can display. There are a lot of parameters that you can pass to this API (full list here). The ones we care about are:

  • Zoom
  • Size
  • Markers
  • Key

We’ll get more in-depth as to what an API is in other blogs, so for now here’s the code:

“https://maps.googleapis.com/maps/api/staticmap?&zoom=” & slider_mapZoom.Value & “&size=” & img_map.Width & “x” & img_map.Height & “&markers=color:red%7Csize:mid%7C” & EncodeUrl(If(IsBlank(ti_addressEntry.Text), Location.Latitude & “,” & Location.Longitude, ti_addressEntry.Text))& “&key=YOUR API KEY HERE”

Woah! That’s a lot! Okay, let’s break it down, and show you what to do with it all.

Breaking down the code

https://maps.googleapis.com/maps/api/staticmap?&zoom=” & slider_mapZoom.Value 

This component is the API URL. The final section is the “Zoom” paremeter, which you can see is the value of the Slider we’ve put in PowerApps.

& “&size=” & img_map.Width & “x” & img_map.Height & “&markers=color:red%7Csize:mid%7C”

Woah that’s a lot of ampersands. We use these to concatenate strings togethter in PowerApps. This section of code defines the map height and width, based off the size of our image control in PowerApps. 
The end section of this defines the markers as red, and size of medium.

& EncodeUrl(If(IsBlank(ti_addressEntry.Text), Location.Latitude & “,” & Location.Longitude, ti_addressEntry.Text))& “&key=YOUR API KEY HERE”

Okay final section! This part will pass the location value to the API. You can see it checks to see whether the address entry text input is empty or not.

  • If it’s empty, pass the latitude and longitude of the devices GPS
  • If it isn’t empty, pass the address entered in the text input

Also, replace the section at the end with your API key (right where it says YOUR API KEY HERE). And you’re done!

Okay, throw all of that into the Image property of the media we added before, like so:

Google Maps in PowerApps - Fast & Efficient 11

Almost done!

Final thing, set the OnSelect property of the button to the following code:

Concurrent(Reset(ti_addressEntry), Reset(slider_mapZoom))

And that’s it! Test it out, and congratulations to you!

Google Maps in PowerApps - Fast & Efficient 12

Now, here’s another tutorial on how to convert that unsightly latitude and longitude to an actual address: PowerAutomate – Transforming Lat/Long to an Address

Catch you in the next blog, happy PowerApps Deving!

Running SQL Queries in PowerApps

Attention class! Jacob here, and today we’re going to be talking about running SQL queries in Flow.

But why would we do that when PowerApps already has LookUp, Search, and Filter?
Because PowerApps doesn’t like doing the heavy lifting. And PowerApps will also use your device resources to handle these. So if you have a slow device…….you’re going to have slow functionality.

Now it’s no good making a claim like that without supporting it… Here’s a link for some Performance Comparisons in PowerApps: PowerApps Performance

That’s where SQL comes in. In other blogs, we talk about using SQL as a database for your app. Think of this like the backbone, and PowerApps is the pretty front end. So, why not use SQL for what it was designed for?

What is SQL good for?

    • Querying existing data based on changing conditions
    • Providing quick results from dynamic queries in PowerApps

What is PowerApps good for?

    • Providing the “good looks” of the app
    • Manipulating data already loaded inside the app

So, putting 2 + 2 together, we end up with A PowerApps front-end with an SQL Backbone.

 

SQL Query Structure

First of all, this isn’t a masterclass on SQL. I’m no SQL wizard by any means, but I will show you some entry level SQL that will help you manipulate your existing data without a massive performance hit.

For some more in-depth reading on SQL, see this link: SQL Tutorial – W3 Schools

An SQL Query looks similar to this:
“SELECT * FROM Customers WHERE Name LIKE ‘NextStep Creations'”

Let’s break this down a bit.

  • SELECT: The SQL statement used to select data from the database
  • *: All items. This can be changed to specific column names (customerName, customerID) if you only require certain data
  • FROM: Which table/source we are going to be selecting data from
  • Customers: The name of the table we’re pulling data from
  • WHERE: Used to start defining a set of condtions
  • Name LIKE ‘NextStep Creations’: Finds records where the Name column is LIKE the text in quotes

Here’s what that query looks like in PowerApps (with a custom added flow, which we’ll get to soon!):
Running SQL Queries in PowerApps 13

As you can see, we’re passing the entire SQL query from PowerApps, and using a text input as the dynamic content for our search.
Now, let’s create our own from scratch!

Step 1 – Creating the Flow

Create a new flow, and lets start with the PowerApps trigger. Add a step, find “Execute a SQL query (V2)” and add it.

Running SQL Queries in PowerApps 14

Fill in the blanks with your server name. Once you add your server name, it will let you pick from the databases you have (provided you’ve authenticated correctly):

Running SQL Queries in PowerApps 15

Once you’ve selected your database, add the “Ask in PowerApps” option into the “query” section.

After this, add a “Response” step, and set the “Body” to the following expression:

body(‘Execute_a_SQL_query_(V2)’)?[‘resultsets’][‘Table1’]
Leave the “Response Body JSON Schema” section blank for now.
Running SQL Queries in PowerApps 16

Name this RunSQL and save your flow, we’re almost there!

Step 2 – Adding to PowerApps & getting data

Create a new PowerApp, and create a text input (this will be our search field). Add a button below that, and then finally a gallery underneath the button. You should have a layout like so:

Running SQL Queries in PowerApps 17

Great work! Time to start setting the functionality up.

Button

Add the PowerAutomate flow we just created to this button:

Running SQL Queries in PowerApps 18

Set the OnSelect property of the button to the following:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Replace “company” with the name of the table you wish to search against, and change “companyName” to the field in that table you want to search by.

Now, set the Gallery’s items to the collection we defined before, “sqlSearchData”.

Running SQL Queries in PowerApps 19

Awesome! Now you can test your app. Type something in the search box, and click “Search SQL!”.

Oh no! I got an error! Lets have a look why:

Running SQL Queries in PowerApps 20

Running SQL Queries in PowerApps 21

This may look confusing, but never fear! All this is telling us is that no data was returned from the search we made.

(in another blog we will cover error handling and how to deal with this. For the purpose of this tutorial, lets move on.)

Step 3 – Displaying the Search Results

Okay, time to do a test search with a company that I know exists, my own!

Running SQL Queries in PowerApps 22

Hmm…..no error, but we still can’t see anything in the gallery. Let’s check our collection and see if it’s empty!

Running SQL Queries in PowerApps 23

Huzza-WAIT. This isn’t the data we wanted……

Remember that field we left blank in our Flow earlier? Time to fill it out.

Step 4 – Providing the correct data back to PowerApps

Go to your flow, and open up the successful run. Expand the “Response” section, and you will see the data:

Running SQL Queries in PowerApps 24

This looks like exactly what we want! Grab a copy of the text in the “Body” section.

The data you see is in a format known as JSON. More information on JSON can be found in other blogs, or by visitng this link: W3Schools JSON.

Go back into your flow and edit it, and select the “Generate from sample” button in the Response step. You’ll be greeted with a lovely box. Go ahead and paste your body content we copied earlier into here:

Running SQL Queries in PowerApps 25

Select “Done” and your Response will now be filled with a JSON schema. Ensure that there is no keys that don’t have a data type. This will cause registering issues. More can be found about these issues in this blog post: Creating a HTML Report

Save your flow, and head back over to PowerApps.

Final Steps: Displaying data in PowerApps

First of all, we’ve made some changes to our Flow, so we need to remove and re-add it to our button.

Select the button, and then delete the OnChange section (this will remove the flow from being associate with this button).

Add the flow back in again, with the same value for the OnSelect:

ClearCollect(sqlSearchData, RunSQL.Run(“SELECT * FROM company WHERE companyName LIKE ‘” & TextInput1.Text & “‘”))

Running SQL Queries in PowerApps 26

Let’s run that search again and check our collection!

Running SQL Queries in PowerApps 27

VOILA! Great Success!

Now, go ahead and add some labels to your gallery, and set their properties to ThisItem.companyName (or whatever columns you have in your collection).

Running SQL Queries in PowerApps 28

Congratulations! You’ve successfully searched data in SQL using PowerApps, with minimal workload on the client-side required!

PowerApps – Create HTML/PDF Reports

Do you get shivers every time your client or boss asks for a report of that beautiful PowerApps Gallery you built for them?
This post will give you the tools you need to create beautiful PDF reports with and without images using our wonderful PowerPlatform and a little code magic.

The very first thing you are going to want to do is setup a Gallery or Collection in PowerApps <– This is what we will be using as our base for our report.
Lets take a look at my very basic app that tells some real truths:

Items

As you can see we have three columns – Description, Purchase and Notes
We are going to export the 4 rows of a snapshot of my life (dont tell my wife) to PowerAutomate where we will formulate our amazing HTML.
The code to do this is quite simple – but first we need to create our flow.

Items

Above you see two things:
1. The first is our PowerApps trigger. This is what allows us to call and send data to the PowerAutomate Workflows (Flows).
2. The second is an Initailise Variable step with Type Array and a nice name. We at NSC use var_ to start our variables so they are all the same scheme.

The beautiful thing about the PowerApps Connector is that it also does something else for us. As you can see it lets us Ask in PowerApps as a dynamic value option. This creates a PowerApps Variable with the name of the control:

Items

Notice the name of the variable varitemsFromPA_Value and look at the name of the initialise variable step: var_itemsFromPA_ <– take note of this.. when you are dealing with 100’s of these, your naming scheme will save your headache!

So quickly recapping… We have a PowerApp with Data and a Workflow with a setup ready to receive it… Lets join them.

Items

I have added a button to my PowerApp > I have clicked on my button > I have clicked actions in the top header > I have clicked Power Automate and Now I simply have to select my requested flow.

Items

I have now selected createReportDemo (My Workflow’s name and its adding to my button)

Items

Oh No! It failed to register… This is something you WILL run into when you are playing with Flow and PowerApps… The trick here. PowerApps can accept and receive only one thing… Text (strings, words, characters, bits) it only deals with Text…

So when we created our variable as type array that puts a request to PowerApps to send an Array reponse. It cannot complete this action so it FAILS! Don’t fear though. This isn’t a stopping point; we just have to change our functions slightly

To Remove a PowerApps Variable you have to remove the Trigger from the Flow:

Items

ItemsItems

Okay we have cleared that Variable… Now lets change the variable type:

Items

Now lets re-request our PowerApps Variable:

Items

Okay now we are ready to head back to the PowerApp.


BOOM!

Items

We are now ready to start adding our code to the no code solution! 🙂

In the function bar at the top of the PowerApp if you select this…

Items

It shows you your beautiful NAMED variable that it is asking for

These are mandatory. If you ask for two you must provide both responses. Hence why I showed you how to delete incorrect variables above… cause god dammit man we are only human!

The function layout is as follows:

`createReportDemo.Run(JSON(expenses, JSONFormat.IgnoreUnsupportedTypes))`

My collection here is called expenses (which contains all the columns and data)

We are using Ignore Unsupported Types because I dont want to deal with NULL value checking today you can read more about this here:
JSON Functions
We will cover JSON in more depth in blog posts to come!

Items

So we now have a button that will send all our data from PowerApps to PowerAutomate in a JSON format and a PowerAutomate Workflow that will grab that response and put it into a string variable for us… GREAT!

Time for the HTML…. WRONG we have to do some dumb stuff first…

Your beautiful button you created earlier… Press it.. Press it now!

Hopefully it will run your flow and you will get a succeeded response like below:

Items

We are now going to open that response:

See that beautiful Value there… right now it contains [{BLAH BLAH BLAH

This is our JSON code and what we need to copy for this step:

[
{“Notes”:””,”URI”:””,”description”:”Cat”,”editValue”:”false”,”purchase”:133},
{“Notes”:””,”URI”:””,”description”:”Wife”,”editValue”:”false”,”purchase”:999},
{“Notes”:””,”URI”:””,”description”:”Baby”,”editValue”:”false”,”purchase”:200},
{“Notes”:””,”URI”:””,”description”:”Ben”,”editValue”:”false”,”purchase”:1}
]

Okay now we have our sample code lets jump into the Workflow’s edit mode:

Insert a _Parse JSON_ action and provide the content as the var_ variable we defined earlier

Items

Now see that Schema is required … SMASH Generate from Sample and dump our copied notes from our succesful run in there

Items

Then click done!

Items

Now our JSON is entered we can continue

Add a new step – Create HTML Table

The Content for this is the Dynamic Body of our Parse JSON

I have added a comment so you can see the real expression (if you care)

Items

Okay home stretch…

This Create OneDrive for Business connector might cause you some grief… its not you… its Power Automate…

There are two solutions to resolve this error:
onedrive error

  1. Export your flow… then simply re-import your flow….. Yup…
    OR
  2. Delete your flow…. then delete your OneDrive for Business Connector from the connectors screen on the left…. Create the OneDrive for Business Connector again and finally recreate your flow

Add a Create OneDrive for Business File:

Items

The Filename MUST contain .HTML here

The File Content is the body response of our Create HTML Table

Next Add a Convert File (Preview) Action:

Here our *File: is the ID of the above created File and Type is as you request. However as the title suggests this is to PDF!

Items

Finally…

Add a Create File action… 

Here we are going to just play match the names and use the File Name and File Content of our Convert File action:

Items

And Thats it…

This final step will create our PDF and save it to the defined location. You can fancy this up but that will be part two of this post 🙂

Here is the output of this basic PowerApp and PowerAutomate Workflow:

Items
Items

Hope this helps! Read the next step here: PowerApps – Create Beautiful HTML/PDF Reports

Taking your PowerApps and PowerAutomate Skills the NextStep

Ben is now in trouble… Signing off!