The purpose of this article is to show you how easily you can create an intelligent SharePoint App with a little bit of customization with PowerApps. Many times, as an independent consultant, I’ve been struggling with creation of invoices. It’s complicated as there is no room for error and there should be a follow up. Just sending the invoice is in most cases not enough. As I was tired to repeat all that steps each month I decided to create an Intelligent App that calculates my invoices. This was also shown as a POC to my customer to showcase the power of a combination between SharePoint and PowerApps.
_DISCLAIMER_ Please be aware and careful with the kind of information that you are storing in SharePoint Online. Confidential information such as invoice Data Attributes should be stored in secure environments as Dynamics365.
Create a SharePoint List with a few columns. I did choose for the following columns. Whenever finished, create a few entries as play data.
When you’ve finished adding data, on the ribbon, hit PowerApps and Customize Forms
First thing first, adding and deleting columns should not be difficult. If you never did that before, hit on the Form properties and under Layout/Data you can add or remove SharePoint columns (fields) to your Form.
The most important part here is to use the Location (Bing Maps) of our customer and show it in the SharePoint Form.
Before doing anything, you need to create a Bing MAPS API Key by visiting the following URL: https://www.microsoft.com/en-us/maps/create-a-bing-maps-key. Ask for a Basic Key, authenticate yourself and under My Account generate a new specific key. Save the key as we’ll need for on a later stage. Here is a brief preview on what you should get.
Then, we’ll create a configuration screen to store some information which can be used by other screens in the app. This is a common practice as you don’t want to show every information to your end user. Create your screen and rename it to ConfigSCR
On the Configuration Screen Insert a Text input control from the Insert Tab > Text > Text input. Rename the control from TextInput1 to txtBingMapsKey. Change HintText to “Enter your BING Maps Key here” and the Default to the actual key value from the Bing Maps.
Return to your main screen where all the fields (SharePoint columns) are and hit Insert > Media > Image control to add a new Image to the screen. Rename the control from Image1 to imgLocation.
We are all set now for rendering your customer location map. Use the following formula in the Image property of the imgLocation:
“https://dev.virtualearth.net/REST/V1/Imagery/Map/Road/” & EncodeUrl(DataCardValue4.Text) & “?mapSize=800,400&key=”&txtBingMapsKey.Text
What you need to understand from this little code is the following:
- DataCardValue4.Text = The field (SharePoint Column) name where the customer location is stored.
- mapSize=800,400= the actual size of your location field. This can also be stored on the configuration screen in textboxes as well. If you choose to store it in textboxes– which I definitively recommend – you will only have 1 screen to manage the whole app – then simply replace the 800 and 400 by the name of the fields.
- txtBingMapsKey.Text= the field name on the configuration screen where BING MAP API KEY is stored.
Save your application and publish it to your list. When you edit your List Item, and hit a customer address the BING Location will automatically and in real time show you the location.
You can find more information about location and how to adapt, enable, disable, zoom-in, zoom-out (playing with controls) on the following article: https://powerapps.microsoft.com/en-us/blog/image-control-static-maps-api/
When you are an independent consultant like me, you always need to know what your daily price is for your different customers. In my case as I have different customers, my price is always different as the price heavily depends on projects, scale, customer… For the second part of the application, create another list and add a few columns. In my case I added a column for my customer’s name and a lookup to the guys who I’m invoicing. Strange, isn’t it? As many big organizations, don’t like to work with small companies they ask you to work through another company. So, I can have a customer XYZ but the invoice goes through ABC. And I can have multiple customers passing by ABC, so that’s the reason why there is a look up to the customer’s name.
One column for my daily rate (100%) and 4 other Calculated fields to calculate the 125 (overtime), 150 (Saturday), 200 (Sunday) and 300 (Sunday + Public Holiday).
- The calculated field 125 – has the following formula: = [TJM 100] + ([TJM 100] * 0,25)
- The calculated field 150 – has the following formula: = [TJM 100] + ([TJM 100] * 0,5)
- The calculated field 200 – has the following formula: = [TJM 100] + ([TJM 100] * 1)
- The calculated field 300 – has the following formula: = [TJM 100] + ([TJM 100] * 2)
Until here nothing special as all those things are/were from SharePoint. The fun part begins when you edit your SharePoint Edit Form and add a slider as you can see on the following picture.
By adjusting your slider, you can edit your daily rate and in real time / or after saving your modifications, SharePoint will automatically update the calculated fields so you can see daily rates.
Click Insert > Controls and select Slider, and set the minimum and maximum values into the properties pane.
One simple operation left, define the TJM 100 (column) text input default value to the value of the Slider.
Save, Publish and enjoy.
Now that I have my customer and my daily rate, it’s time to calculate my invoice, add the VAT, send it to the customer and create a task in my Outlook. Again, create a new list, or continue the existing one. Edit the form with PowerApps. Here is how mine is looking. I created a few SharePoint columns (fields) for my hours and a few lookups from different lists such as Customer, Contact information and TJM.
When you create a new list item you simply chose the customer [lookup], insert the hours that you’ve worked and PowerApps is going to calculate the whole invoice for you.
This screen doesn’t do anything expect showing the results. Everything else, is done on the second screen. Therefore, create a new screen as you did before with the configuration screen. Add a button on the first screen and call the function navigate
Basically, if anyone clicks on the button, PowerApps will open the second screen.
And vice-versa, you can add another button on the second screen and apply the following formula
On the second screen, I’m applying the same formula as I applied to my calculated fields. I’m reading the information from my labelTJM100 (which contains my normal working hours) and calculate my overtime price. The same for Saturday but multiplied by 0.5 and 1 for Sunday.
Whenever I know my price, I read from the first screen how many hours I’ve been working as overtime (DataCardValue4 corresponds to my overtime) and multiply it with the value of TJM125. Optionally with the Round functionally you can around it to 2 decimals.
Repeat the same for Saturday work and Sunday work
- Saturday: Round(((DataCardValue5/8) * LBL_TJM150),2)
- Where DataCardValue5 corresponds to my Saturday working hours from screen1
- Where LBL_TJM150 corresponds to my Saturday daily price
- Sunday: Round(((DataCardValue6/8) * LBL_TJM200),2)
- Where DataCardValue6 corresponds to my Sunday working hours from screen1
- Where LBL_TJM200 corresponds to my Saturday daily price
Whenever you’re done with the total hour’s calculation it’s time to wrap-up and add the VAT.
The subtotal is the sum of all prices
- Fx: Round((Label3+Label3_1+Label3_2+Label3_3),2)
The total is the sum of Subtotal multiplied by 21 (Belgian VAT)
- Fx: Round(Label3_4*1.21,2)
The VAT is Total – Subtotal
- Fx: Label3_5-Label3_4
You can leave it as it is, or just like I did copy every value to the first screen. It’s all up to your creativity. For the final part of this blogpost, let’s add 2 more button on the second screen.
Whenever my calculation is done I’d like to send it to my customer and create a reminder. You could get it done in one button – but sometimes no invoice is needed to get paid (PayPal, Gifts…) but you still need a reminder or vice-versa.
Each button that you create on your form can be attached to a Microsoft Flow.
Hit on Flows, and on your right side, you will get the possibility to create a new flow. Give your flow a name and add a new step Send an email. One of the biggest main benefits of PowerApps is that you can use Dynamic Content. So, for every data (coming from SharePoint, PowerApps, or any other Data Source …) that you want to add into your mail can be done by Ask in PowerApps. Hereunder a simple example of my Email body content. As you can see, I have a lot of Ask in PowerApps as I want to give all the hours, vat and total price. Even the email from the customer is read by Ask in PowerApps Dynamic Content. etc…
Save your flow and go back to your Form. Hit on the button and associate your button to the newly created flow. PowerApps is going to create a function OnSelect and will require your parameters. All those inputs are the “Ask in Parameters” that you defined in your body.
Save, publish and test, here is the result:
The last but not the least is to create another flow attached to the third button which will create an outlook task for you. Add a new action called “Create a Task” and:
- set the Reminder date as a date function to: adddays(utcnow(‘yyyy-MM-dd’),28)
- set the Due date as a date function to: adddays(utcnow(‘yyyy-MM-dd’),30)
Save, publish and click on the button. A new task will appear in Outlook.
My conclusion is: PowerApps + Data = Intelligent SharePoint as within a few hours, you’ll be able to create intelligent, intuitive and powerful SharePoint Forms with the help of PowerApps. Almost no code, easy to understand and flexible to maintain.