Using Power Apps Location Services – (Part 1)

Introduction

I recently developed an Inventory app for a client. The whole app (built in Power Apps), is quite a big app, so I have decided to break this blog up into 3 parts.

  1. Using location services (This blog)
  2. Using the Barcode Scanner to scan items
  3. Storing Scanned Items

Scenario

The scenario for this app was that is was going to be used by reps to do an Inventory at nearby factories, i.e. There are several factories in a certain geographical location and reps (using their phones GPS) can see which location is nearest to them.

Using Location Services in Power Apps

  1. Drag the Image Control onto your Power App and assign the following code to the Image Property, as below. (You will have to get your own API Key. I used the following link :
    https://developers.google.com/maps/documentation/places/web-service/get-api-key
"http://maps.googleapis.com/maps/api/staticmap?center=" & LocationTable.Selected.Customer_Latitude& "," & LocationTable.Selected.Customer_Longitude & "&zoom=17&size=700x900&key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&markers=color:red%7Clabel:A%7C"& LocationTable.Selected.Customer_Latitude & "," & LocationTable.Selected.Customer_Longitude

As you can see, you have to specify a Latitude and Longitude (and the api key) in order to get the Map to work.

The reason I have developed it like this is because the app should allow a rep to select which location they wish to go to. So there will be a list of locations, and the rep and choose which one he or she wants.

Adding Locations

  1. I create a new page on my app called ‘Config’ which is not visible to the end user. The location should be filled in prior to the reps accessing the app.
    Its a very basic page which can you customize to your needs as you wish. For the purposes of this blog, i am using Microsoft Office as factories.
Add Location

This page simply allows you to Add an Location and then store that location inside of a Collection. The Longitude and Latitude is from Google Maps.

To add the address to the Collection, the Add Location has the following code on the OnSelect Property,

Collect(Locations, 
{
    Customer_Title: txtCustomerName.Text,
    Customer_Address: txtAddress.Text,
    Customer_Latitude: txtLatitude_1.Text,
    Customer_Longitude: txtLongitude_1.Text
}
)

The value entered is then saved to the Collection, e.g. below

Locations

You add as many locations as you want to the Collection and then display the collection directly below the map, as below (You will have to go into Data Sources, Add Data and choose the Data Collection you want to add) – You will have to ensure that your Data Table is called LocationTable as the api key above references it.

1 Location

The aim here is to add several Locations, then the rep can select each location and the map should change accordingly,

2 Location
3 Locations

Conclusion

This blog was part 1 / 3 where we added location services to the app, so a rep can select a nearby factory. Watch out Part 2 of the app will be using the phone camera to scan barcodes.


Send a Summary Email to every user with several SharePoint list items.

Problem

Asset information containing information about office equipment borrowed by employees was stored inside of an SharePoint List. (For the purposes of this blog, I will use fictional sample data).

We needed to automate a way to send emails to remind employees of the items they borrowed and when they were due back.

The problem was that one person borrowed several items and we did not want to send that one person separate emails for every item they borrowed. We needed a solution that send a summary email of all the items borrowed. So if Joe Bloggs borrowed 10 items, he should receive one email listing the 10 items.

I developed a solution using Power Automate, that I hope will help someone.

Sample Data

Firstly, I will be using sample fictional data to demo how I built this solution, see sample below

Fictional Sample Data

You can see from above jbloggs@domain.com has borrowed 4 items, but has returned one item, so he should receive an email listing only the 3 items he has borrowed – (Not the returned item)
jsloan@domain.com has borrowed 3 items, but has also returned 2, so she should only receive an email listing the one outstanding borrowed item – and so fourth.

Solution

For the purposes of this demo, I am using the Manual Trigger Action to initiate the flow; however, you can use this email to send on a recurring basis if you need to.

Using Power Automate, I connected to the SharePoint List using the Get Items Action, and then using the ODATA Filter, I filtered out assets that had already been returned, as below

Get Items Action

This only returned items from that SharePoint list where the Returned Value was set to No. (Note : I use JSON Viewer to view output of results, as below)

Directly below, I added a Select Statement to Get all the Asset borrowers from that SharePoint List and map it to a variable called ‘Email’, as below

The returned the following output :

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  }
]

(Remember, we are returning all the Emails who have borrowed items Only, and assigning the variable Email to their Email Address.)

Next step, I added a Compose Action and added the following Expression to get a unique list of Emails (or a distinct list of emails)

This returned the following JSON,

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  }
]

You can see now we have a unique or distinct list of email address.

I then added an Apply to Each (Which i have renamed to ‘ForEachEmail), and added in a Filter Array inside of the Apply to Each, as below

This Apply to Each loops through each of the Unique Emails and lists all the items for that Email

For Each Unique Email

Now that we have have all of our Unique Emails and all the items they have borrowed, the hardest part is done.

Next is the Clean up and to make it look presentable.

Cleaning up the Output

I added a Create HTML table just below the Filter Array, and instead of selecting Automatic Columns, I selected Custom and defined my own columns, as below.
The reason for this is because if you select Automatic, the HTML will show every single column inside of SharePoint, such as the Created Date, Modified Date etc

Custom Columns in HTML Table

This produces the following Output for each Unique Email,

Now, for each Email, we have an output presented as a HTML Table 🙂

I didn’t quite like the output design of this HTML, so I did a quick search and came across this blog which gave fantastic examples of HTML Styling.
I created a Compose Action, which I renamed Style and copied in the HTML Style found on Ryan’s Blog, as below

HTML Style

The Next step is to put all of this into an Email.

Composing the Email

I added the Send an Email (V2). Ensure the email is sent to the right person, by inserting the Expression as body(‘Filter_array’]?[0]?[‘EmailAddress’]

An important step is to add the Styling to the body of the email. There are two ways to do this :

  1. You can edit the email in Code View and insert the styling.
  2. You can add the Styling Action directly into the email, as I have done below

Once the Styling as been added, you can compose the rest of the email as I have highlighted below

Composing Email

Now to give it a try, see below for the results of the email received and how the Email Looks

Emails Received

I hope this has helped someone, please feel free to comment below for any questions.


laser_tape

Portable Laser Measuring Tape

Please read my disclaimer before making any purchases.

Earlier this year, I started a project of renovating my kitchen.
Before even looking at new kitchen designs, my first action was to get the exact measurements of my existing kitchen.

Traditionally, I always used the ‘normal’ tape measure where you measure the distance between A and B, however, the biggest disadvantage using this was its difficult to get an exact reading.

DTAPE Review

I came across a Digital measuring tape, and I purchased the below tape from Amazon. Its not the best measuring tape, but for what I required it for – it was perfect.

DTAPE DT50 Laser Measure Meter 100m


The main advantage of this laser tape is the speed at which is works. You simply face the laser towards a hard surface of another area of where you want to measure, (e.g. wall) and it correctly gauges the distance via a laser.

Whilst the measuring tape is at the lower end of the spectrum compared to the some of the named tapes, I could not fault it for what I required it for.
There probably wont be a time I need to measure more than 100m.

The backlight was bright enough for me (even outside), the accuracy was spot on (I have only measured no more than 10metres) and a fantastic feature is that the screen records the past 20 measurements which allows you to take several measurements in one go and cut all my pieces at once, opposed to having to measure each piece of wood individually and then cutting.
The tape also measures the area and volume and the measurements can be adjusted from metres, feet and inches

My overall verdict is that you don’t have to spend hundreds of pounds on a measuring tape, when this one looks fine is affordable and does an excellent job,

You can purchase this tape using this link : https://amzn.to/3KEtWvZ


Initiating a Flow from a Microsoft Teams Form

Problem

I had a scenario last week where I created a MS Form inside of my MS Team and wanted to capture the responses and do some validation on them.

Flow can easily connect to a MS Form and carry out some actions once the form is submitted, right?

The problem was that when I created my Forms inside Teams, I was unable to select the form from within the Flow.

Solution

  • First step, within my Microsoft Team, I created a Form as below
Create MS Form inside Teams
Form Name
Example From created inside of MS Teams
  • I navigated to Power Automate and created a new flow for when a form is submitted, as below
Power Automate Flow

However, when you expand the ‘When a new response is submitted’, you do not see the option to select the Form you just created inside of your Team.  I can see all the other forms I created from directly within MS forms, but not the form created from within MS Teams.

I was baffled by this for quite a while. Until I thought of trying something!.

MS Form ID from URL
  • I pasted the Form ID manually into the ‘When a new response is submitted’ section, as below
Paste in Form ID
  • Added the Get Response Details action and again, manually entered the Form ID as below
Get Response Details

And the Flow Worked 😊  – Hope this helps someone and avoids them wasting time like I did .


SharePoint and SSL

This blog post was originally written in December 2014 – The post was copied from my old blog (Which is no longer active) site to here

Before reading any further, I strongly recommend you test, test and test this before implementing in a Production Environment as per my Disclaimer

Back in 2014, I was tasked with installing an Intranet / Extranet for a customer.  Installing and configuring the SharePoint 2013 (on-premise) was all done, the customer however required secure communication over the extranet so external users could communicate securely over http.
This blog article will detail how I set up SSL (Secure Socket Layer) and shall furthermore describe how SSL works.

Extend the Web Application Zone

Firstly, I extended the Intranet URL to the Extranet Zone.  This ensured that the external users could access the same information as Internal Users.
(When you extend a zone, SharePoint automatically creates an Alternate Access Mapping (AAM), this basically tells SharePoint how to map the request to a URL)

1. Highlight the web application you wish to extend and select the extend tab from the ribbon

Extend Web Application

2. From the Next screen I then populated the following fields

Ensure the Extended zones is on Port Is 443

Extended Web App Name

Ensure the Extended Zones is configured to run on SSL

Check the URL and the zone is correct

Extended Zone on SSL

Once you Click OK – SharePoint will create an additional zone for you in IIS (as below)

Extended Zone in IIS

Create your IIS Certificate

3. To create the Certificate, in IIS click ‘Create Certificate Request’

Create Certificate

Populate the Distinguished Name Properties (as below) and Click Next

Distinguished Name

Save the .txt file. The txt should be used to request the certificate from a verified issuer such as VeriSign or GoDaddy.

Install Certificate

3. Once your certificate .cer file is on the web server, right click it and select Install Certificate. (If you have multiple WFE servers, do this step on all of them)

Install Certificate

4. Ensure the certificate is imported onto the Local Machine and placed in the Personal Store.

Install Certificate
Place in Personal Store

5. Head back into IIS and double click Server Certificates.

IIS – Server certificates

Your Certificate will be visible.

Edit SharePoint Bindings

6. Go back to the extended SharePoint Site, Right Click and select Edit Bindings

Edit Bindings

select Edit

Edit Zone

Select your certificate from the SSL dropdown and Select OK.

Select Certificate

7. Your Certificate is now attached to your Web Application.

Move the certificate into the Trusted Authority Store

8. Navigate to your Extended SharePoint site and assuming you have your DNS records set up; it should load with problems with the green padlock (as below)

SSL – Security Padlock

How to SSL Work?

Now having set all that up; what does attaching a certificate to the SharePoint server actually do?

1. Your computer makes a request to access the http://extranet.domain.com site

2. The server where the certificate is installed for http://extranet.domain.com issues a Public Key to the requesting computer.

3.Your Computer then encrypts the data with the Public Key (that was sent from the server) and sends data back to the server.

4. The Server then decrypts the data using the Private Key – and only the server with the Private Key can decrypt the data.

If anyone intercepts the data in between, the data will be ‘Jibberish’.  The Private Key is the only way the data can be decrypted.


Excel Web Access and OData Connections: Data Refresh

This blog post was originally written in May 2016 – The post was copied from my old blog (Which is no longer active) site to here

Have you ever tried to use a SharePoint list to populate a Power Pivot Graph in excel; and then render the graph in SharePoint using Excel Web Apps? – Sounds pretty cool eh!?

Not Quite.

I created a SharePoint list, for e.g., Profit Loss, which details a financial summary of a project, e.g.  (Profit is a calculated column)

Having created the list, I opened up Excel and created a OData Data Feed connection (below) and generated a graph based on the data within the list.

Create OData Data Feed connection

Graph below

Graph showing data from SharePoint list

Seems relative.

However, if you make a change to the SharePoint list, the excel web access web part in SharePoint does not dynamically update to show an updated chart to reflect the change made on the list.  You must manually open the excel sheet, refresh data connections and then save the excel sheet again to update the excel web access web part.

If you manually refresh the Excel web part by clicking Data, Refresh Selected Data Connection, the web part loads the new updated data;

Screenshot below showing new data

New SharePoint list data

Screenshot below showing a manual refresh

Graph Refresh

Result after doing a Manual Refresh (notice the profit)

If the entire page is refreshed, the webpart reverts back to the original, see image below.

I noticed that if you have the excel sheet open in real time and make a change to the SharePoint list – the Excel will in fact update – but it does not save the sheet. (You can check the modified date on the library).

Having done further tests – I also noticed the Pivot Chart had a ‘Refresh data when opening the file’ – I checked this and saved the excel file to my SharePoint document library. 

Refresh data when opening file

The Excel Web Access web part now showed a Warning when the page was loaded, as below

Excel Warning

The user must click Yes to load the worksheet – which as a result refreshes the latest data. Not, really a solution though.

For On-Premise users – we can resolve the warning message by adding the Excel file as a trusted location in the Excel Services Application; however, the Excel Service Service Application is not available in SharePoint on-line 

I guess the only viable solution is to for a user to manually open the Excel sheet(s) and do a refresh all and then save the worksheet back into SharePoint; thus, updating all the Power Pivot Charts.

I will let you know if there are any updates on when or if the Excel Service Application is available so we could add worksheets into the trusted locations.

There is a good blog article here which could be of help should you run into a similar issue:


Target Audiences Column appearing as GUID

This blog post was originally written in November 2013 – The post was copied from my old blog (Which is no longer active) site to here

So, I have been working on a project for a customer who wanted to Target Audience individual list items in SharePoint…. Simple task, right?

I allowed the management of content types on my list and added in the ‘Target Audience’ site column which is available to us out of the box.  This bit all works fine.

The actual problem arises when you start to audience target the list items.

When you audience target list items, the audience appears as a GUID in the list view (as below)

Basically, if the first item in the list has an audience, then ALL the items appear as GUIDS.
As soon as you remove the audience from the first list item, the audience’s name loads correctly i.e., doesn’t display the GUID.

I did a bit of further reading on this, and a lot of people have said this issue is by design, but surely it can’t be a design issue. No one wants to see a GUID in the view.
I found this issue is evident on all on-premise versions of SharePoint (2007, 2010 and 2013) and SharePoint on-line.

Having contacted Microsoft about the issue; they confirmed that this issue is a Bug in SharePoint and there is currently no fix for it 
The current workaround is to remove the audience from the first item :S

Awaiting a response as to when it will be fixed and why it hasn’t been fixed since 07. Until then, I guess we have to live with it.

**UPDATE – Microsoft responded to my reply as to why this issue had not been fixed since 07 & when they plan to have a fix for it.  Below is the response.

This bug will not be fixed. SharePoint uses XSLT stylesheets to render list views. A list is rendered as an HTML table and the value of a field is rendered into the appropriate cell of the table by a simple XSLT template from the fldtypes.xsl file located in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\LAYOUTS\XSL. And this issue relates to the .xsl file. 

So, there you have its folks, an unfixable bug in SharePoint  –

I will have a play around to see if we can have another workaround to this issue – where we don’t have to remove the audience from the first list item. Will keep you posted!

 **UPDATE – Temporary Fix to Audiences appearing as GUID

Hi all, finally got around to finding a temporary fix to this problem.

As the GUID on Target Audiences only appears on the first item in the list, we need to find a way of hiding the first item… Having tried to use filters on the view to hide the first ID – the problem still remained, (because the first item in the list will be item 2 If you hide item 1)

A Solution that worked for me was adding the snippet web part to the actual list and then adding a bit of code to it. See below.

  1. Create a bogus item as the first item in the list. Make sure that this item has an ID of 1. (So, it has to be the first item in the list)
  • Edit the list by selecting the cog in the corner and Edit Page
  • Insert the snippet web part on top of the list and insert the following code:

 <style>
.ms-listviewtable > tbody > tr:first-child{
   display: none;
}
</style>

     4.Save the page

     5. Voila

The snippet is just basically just hiding the first item in the list.

*I strongly recommend you try and test this before you insert any code onto a PROD environment.