Microsoft Power Apps – A more efficient solution for Business
It goes without saying that technology is advancing at a considerable rate. Improvements are being made for increased productivity and efficiency is at an all-time high therefore, making it easier for businesses to get more done and reducing the mundane and repetitive tasks. Recently, we completed a piece of work for a client who were using Microsoft Access to control their data capture of client information, generation of documents and reports. The customer information was gathered using a pen and paper which was then passed onto an admin assistant to process into the Access forms and tables. The information would then be manually archived each year and reports could be run from this information within Access.
As the company were looking for a more efficient way of working and capturing data it was suggested to look at the advantages of using Microsoft 365 and the Power Platforms. Here is a brief breakdown of the key elements of the solution offered to our client and some insight into some of the more technical aspects of the build.
Requirement Gather
The first initial step of any build is getting to know what is needed and required for a solution. In this instance we would look at the following areas:
- Come up with a better way to capture customer information from initial consultancy with a customer.
- Provide the ability to edit the customer information afterwards to add more supporting information at a later date which would then subsequently update other areas.
- Ability to Generate Estimates, Invoices and Satisfactory Notes in Microsoft Word to enable printing and Email.
- Generate Reports to show data between date ranges and from certain data sources coming from SharePoint.
- Easy to use.
- Mobile friendly.
- Easily upgradable.
Tools
It was quickly identified that the tools on offer from within Microsoft 365 would allow the customer to get what they needed from the solution. Microsoft SharePoint would be used to store all the Customer Information, Invoices, Satisfactory Notes, Estimates, Document Templates and Images. Microsoft PowerApps would be used to build the app which would give the key front-end functionality to the users by having forms for data capture, galleries for viewing list data and buttons which would trigger Flows in Power Automate. Finally, the use of Microsoft Power Automate for the automatic creation of files, updating of list items, updating of file properties and automatic ID Generation.
Using SharePoint Lists and Libraries
The information capture using Access is traditionally stored in tables which is no different to SQL Tables or in our case SharePoint Lists. The main customer information capture would be built into a SharePoint Custom List which enabled us to replicate the current customer information on Access by creating columns, views and more. This was probably the easiest part of the solution as it is like for like where the back end information would be stored. We would also use a SharePoint List for the Document References which we will talk about in a moment. Finally, we had a Document Library for each of the different Files that were required to be generated and these would be for invoices, Estimates and Satisfactory Notes. There would be one more library created to store and hold the document templates.
SharePoint Content Types and Document Properties
One of the more complex elements to the build is the requirement to have printable forms which were currently in Microsoft Word. We were able to replicate this behavior using Content Types in SharePoint and have Document Library Metadata then be populated into the Word Documents automatically. We would create a Document Content Type for Estimates, Invoices and Satisfactory Notes, each having their own unique site columns applied to them. We would not apply the template to the content type at this point as we would now need to upload the templates into the Templates Library as shown below.
We could then add the new Document Content Types to the Templates Library.
Finally, we could associate the Documents in the template library with the matching Content Types so each would only be associated with the correct content type giving the correct columns for that type.
The final action, before adding the template onto the actual content type itself, is to add in the document properties via quick parts onto each file. Below is an example of the invoice document and some of the properties needed to be added via the quick parts insert command. These columns are available due to the connection to the content type and the site columns we associated with it. These will later be filled in via the PowerApp and Power Automate Process.
At this point, it is worth noting that the client did not need to complete every field. For example, the customer address 2 may not always be filled in but this placeholder would still be visible. There are two methods we found could work for this including using the Code View in Word ALT+F9 and creating an IF Rule on the column to not show if the label is blank however, this had its own issues with data refresh in tables. The simpler solution was to change the style of placeholders to white text, this way they would simply not be visible to users on word and PDF conversions.
The final step to the Content Types would be to take the URL of the Template, in this example the Invoice template in the templates library and put that into the content type for invoice. That way, in SharePoint, whenever you click invoice it would use our template but also use the document properties for use automatically from the metadata. This is a very clever and easy method of getting document properties from SharePoint. Note at this point we will be adding the metadata automatically from the PowerApps and Power Automate which we shall also discuss in this brief.
This would then be done on all content types and the final step is to add the content types for each document template on the respective libraries in the SharePoint Site making each one unique.
The Power of the Power Platform
In this solution the main goal was to keep it simple and as out the box as possible. We decided that a low code no code solution would be the way forward. Using Microsoft PowerApps for front end would enable the client to use the App on the go via mobile or tablet, embed onto SharePoint pages for admin assistants but also allowing us to update and modify the app at any time by future proofing it.
Screens
As you can see from the screenshot below, the app was built up of various screens to provide navigation to key areas like adding New Clients, Viewing Existing Clients, Accessing Reports and Generating Invoices.
Galleries
Having the ability to view data quickly and easily on the move using galleries in PowerApps allows us to give a nice to use searching ability. As they can connect to over 250 data connection this means we can get our data from SharePoint Lists and Libraries, SQL, Excel and more. We were able to harness galleries for a customer information search, as you can see below, from the test data which also has a Filter, Search and Sort functionality added into it to make it easier to search for the records.
Example of the Gallery Formula is below.
SortByColumns(
If(
Dropdown2.Selected.Value = “Customer Name”,
Filter(
‘Customer Information’,
TextInput1.Text in ‘Customer Name’
),
If(
Dropdown2.Selected.Value = “Invoice No”,
Filter(
‘Customer Information’,
TextInput1.Text in ‘Invoice Number’
),
If(
Dropdown2.Selected.Value = “Reg No”,
Filter(
‘Customer Information’,
TextInput1.Text in ‘Reg No’
),
If(
Dropdown2.Selected.Value = “Est No”,
Filter(
‘Customer Information’,
TextInput1.Text in ‘Estimate No’
)
)
)
)
),
“Created”,
Descending
)
GroupBy Galleries
The next example of galleries is for reporting purposes. We won’t go into as much detail in this post as these can be a bit harder to build and requires more knowledge of collections, group by and on select PowerApp Behaviours. The example below allows the user to input two data ranges and the button then creates a collection of data from the invoice libraries. This collection can then be grouped into months, based on the date range filter, and was probably one of the harder aspects to get right, particularly the sizing of each of the templates and areas on the group by as they would have to be flexible.
Forms and Data capture
The basic principle and goal for the app is to capture customer information which is done by using the PowerApps Forms. We can connect these to a SharePoint list which will display the columns in a Form so the users can fill them out. A submit button is then added to send the data to the SharePoint list which triggers some workflows for the estimate Word Document creation and to push any changes to the estimate file after it is created in the App.
These forms can be rather complex with validation rules, visibility rules and more but they are simple and easy to construct with a data connection to SharePoint.
Auto Numbering and Reference Numbers
There are many ways to accomplish this feature in SharePoint and Power Apps but with this solution we are looking for simplistic methods to manage and harness. The best way of doing this, after submission, is to let Power Automate do the hard work to ensure each new entry gets given a unique reference number. You could just use the standard List and Library ID but this would not be unique to the clients requirements. Power Automate will look to a list in SharePoint for the previous Reference Number, add 1 to that value and then simply update the Customer Information with this number. While doing this method, after submission, ensure that each entry is unique because if you do this during the new form stage, while filling in the information, there is always a chance someone is filling in the form at the exact same time. This eliminates that issue in a low code to no code method which is easier to build and manage later down the line.
PowerApp Buttons & Power Automate
One of the key abilities was to raise invoices and Satisfactory Notification as and when they are needed and to also do it from the app. This is where we can take advantage of buttons in PowerApps and specific OnSelect behaviours with Integration with Power Automate.
You can add a button to a screen in PowerApps and using the top ribbon click on Actions. This shows you a list of actions including the standard on select behaviours but more importantly the Power Automate feature.
This allows you to build a PowerApp button which triggers an automated process directly from within the app itself. On this occasion, we have the button creating an invoice document in a library and taking the parameters from the gallery of the customer selected as metadata to be used in the content type.
Conclusion
The solution itself is one of the many ways we can use some Low Code No Code techniques to create more efficient ways of working therefore, reducing that burden on users with mundane and repetitive tasks by having technology do the hard work for us. This is the reason we offer Training, Consultancy, Support and more for our clients to ensure they are getting the most from Microsoft 365 and the tools available.
Follow us on social media for latest courses and news – Facebook: facebook.com/BlackbirdCorporate Twitter: @Blackbirdcorp