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 1

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 2

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 3
 
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 4

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 5

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 6

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 7

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

Happy PowerApps developing, until next time.

 

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

Running SQL Queries in PowerApps 16

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 17

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 18

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 19

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 20

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 21

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

Running SQL Queries in PowerApps 22

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 23

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!