Goal

To determine the difference in distance between 2 postcodes via Power Automate using a Power Apps trigger, and sort a list of Contacts based on their distance to a given post code in a Canvas App.

Assumptions

  • This article assumes you have access to an instance of Power Automate and a Dataverse instance with a database and Power Apps
  • You have a valid Google Maps API Key: Get a Key
  • Reader is familiar with the basic principals for Canvas Apps and Power Automate
  • Contacts used in the demo all have a post code, if this is not the case you will need to add appropriate error handling

Steps

First, we need to make sure we have some valid data for our Power Automate flow and Canvas App to use. So firstly go into Power Apps and create 3 Contact records with different postcodes.

With that done, create new a Canvas App and add a gallery control to it. The gallery control should contain your list of Contacts, for example, I started by creating a collection with the following formula:

ClearCollect(localContacts, Filter(Contacts, 'First Name' = "Test"))

This is triggered when a ‘refresh’ button is pressed on the screen.

Then, in my gallery, I set the Items value to localContacts:

localContacts

In the gallery, create a couple of labels and set their values to the following:

Label 1: ThisItem.‘Full Name’

Label 2: ThisItem.‘Address 1: ZIP/Postal Code’

NOTE: Values above may be different depending on your environment, the above values are the OOTB values.

This resulted in a gallery as below:

Simple Gallery

Save you Canvas App and navigate over to Power Automate so we can create our flow that will determine the difference between 2 provided post codes.

Create a new flow and Give it a Power Apps trigger and initilaize 2 variables called PostCodeOrigin and PostcodeDestination, the Value for the variables should then both be set to Ask in Power Apps and of type String:

Trigger & Variables

Next, we’re going to initialize another variable to hold our Google API key, however, you should keep in mind that this is sensitive data and while relatively secure (as long as your security is properly configured) it might be better to store this somewhere like Key Vault. The Value should also be of type String:

GCP Key Init

Next, create an HTTP Request action as below:

HTTP Request

Method: GET URI:

https://maps.googleapis.com/maps/api/distancematrix/json?origins="@{variables('PostcodeOrigin')}"&destinations="@{variables('PostcodeDestination')}"&mode=driving&language=en-GB&sensor=false&units=imperial&key=@{variables('GCPAPIKey')}

NOTE: Replace @{variables(‘PostcodeOrigin’)} with the value of your PostcodeOrigin variable, @{variables(‘PostcodeDestination’)} with your PostcodeDestination variable and @{variables(‘GCPAPIKey’)} with your GCPAPIKey variable.

Next, add a Parse JSON action as below:

Parse JSON

Content: The Body of the HTTP Request action Schema:

{
    "type": "object",
    "properties": {
        "destination_addresses": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "origin_addresses": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "rows": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "elements": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "distance": {
                                    "type": "object",
                                    "properties": {
                                        "text": {
                                            "type": "string"
                                        },
                                        "value": {
                                            "type": "integer"
                                        }
                                    }
                                },
                                "duration": {
                                    "type": "object",
                                    "properties": {
                                        "text": {
                                            "type": "string"
                                        },
                                        "value": {
                                            "type": "integer"
                                        }
                                    }
                                },
                                "status": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "distance",
                                "duration",
                                "status"
                            ]
                        }
                    }
                },
                "required": [
                    "elements"
                ]
            }
        },
        "status": {
            "type": "string"
        }
    }
}

Next, add a Compose action as below, this will select the distance value from the response:

Compose

Inputs:

body('Parse_JSON_Response')?['rows']?[0]?['elements']?[0]?['distance']?['value']

Finally, we need to add a Respond to Power Apps action:

Respond to Power Apps

Distance:

outputs('Select_Integer_Distance_Value')

Now, test your flow to ensure it works, save it and return to the Canvas App you created earlier as we need to make a few changes to sort our Contacts by a given Postcode.

Firstly, add your newly created flow to the Canvas App.

Where I created my collection, I changed this to:

ClearCollect(localContacts, AddColumns(Filter(Contacts, 'First Name' = "Test"), "Distance", 'Ndxc.Blog.PA.DistanceBetweenPostCodesGoogle'.Run("BN8 5FT", 'Address 1: ZIP/Postal Code')

For the purposes of the demonstration I am simply filtering for Contacts with a first name of ‘Test’, I am then using the Add Columns function to add a column called Distance to my dataset and I am hard-coding the PostcodeOrigin value – while in reality you will likely be getting this dynamically from somewhere else.

In my label where I was previously displaying the post code, I changed the formula to the below:

ThisItem.'Address 1: ZIP/Postal Code' & " (" & ThisItem.Distance & ")"

Finally, I changed my Items property on the gallery to the following:

Sort(localContacts, Distance)

Refreshing the collection then produces the following result in the gallery:

Sorted Gallery

The value shown for Distance is in metres, so you ma wish to convert this if this is being displayed to the user (e.g. divide by 1000 for KM).

0 Comments

Leave a reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

©2021 TDG - We Are Back Baby!

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending
or

Log in with your credentials

or    

Forgot your details?

or

Create Account