BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 26 June 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Power Apps - Trainer Expenses
Here at Wise Owl we really like Power BI (as can be seen by our multitude of courses). It is only natural then to have a look at other programmes in the Power Platform suite.
Power BI is great and we already have licences for Apps and Automate (hints of the future?). Sadly Virtual Agents is $1000 a month ...
Thinking of the problem
With that in mind I cast my eyes around the office to think of a problem that one of these could solve and saw this:
As trainers we end up with a lot of receipts - from anything from milk for courses to large bills for delegate lunches. These can be hard to keep track of!
Rachel tries her best to decipher the crinkled and often partially rubbed away prices, but sometimes the receipts vanish all together! With that in mind I decided to create a solution, starting with a mock database:
The mock database would let me test the app before taking it live and potentially causing chaos!
Now SQL is great, but it's not the kind of UI that I want on my phone for taking photos of receipts. Power Apps on the other hand lets the user read and write data back to a database.
Apps are broken down into pages. Here some are for viewing, some for editing and some for adding new data.
When creating an application it is possible to start from a template, data source or blank canvas:
When starting out choosing from a data source is a great option. Choose a single table source and if there is an ID column, Power Apps will create list, detail and edit pages.
Overall view (Galleries)
Galleries are used to show all of the rows in a given table with flexibility regarding the columns that are presented. Any edits done to the first row are reflected in all the other rows.
For the landing page I went for a list of the expenses made in the last full month (more would slow things down).
Creating new entries
Clicking the plus icon allows the user to add new rows, which will be written back to the database. This creates an edit form which can present data but has an update setting.
Some things are automated, like the expense date (which defaults to start of the financial year), the financial year (based on the expense date) and our number (defaults to 1 more than the current maximum).
Since the data is split into different tables in the data source some clever look ups are needed to populate the dropdowns with text, rather than meaningless numbers.
While the list displays the names, the update passes the number of the selected value back to be stored.
There is also the option of adding new suppliers directly into the application on the fly. Doing so will refresh the dropdown in real time allowing expenses to immediately be assigned.
Any fields that are compulsory will have the * next to them (sometimes enforcing entry isn't a great idea).
This could have been done for every setting. but the rest of the options I wanted centrally controlled.
Saving the photos
The major reason for creating this app (apart from to learn Power Apps) was to make life easier on us poor trainers. To that end we have the Edit photo button:
Perhaps making an icon would have been more ascetically pleasing but I prefer function over form (no, no the word is efficient, not lazy).
When taking a photo one of the things to take into account is the storage location. For something temporary consider Dropbox or Google Drive, while for e long term you could consider SharePoint.
Next consider how the user will add their photo: will it be direct from the camera, or will they upload existing photos?
In terms of difficulty to set up the upload is universal and required much less work. The camera had a multitude of problems including:
Rather frustratingly the number for the camera on a phone is different to that on a computer. Don't get me started on front, back or multi camera types!
To make this easier I separated out the use of phone cameras from that of other devices with a tick box:
Clicking on the photo will take the image and present it in a temporary gallery. The user can then take another picture or hit Done.
To prevent the save location being clogged with loads of failed photos, the photo is only submitted when the form itself is submitted.
From the front page gallery the trainer can click on any of the rows in the gallery, which will filter a summary page to show only details of that record.
These preview screens can hold more details than the gallery but are less confusing to read than the edit page. Even the photos can be retrieved for viewing.
All the information is provided using a gateway on the server, meaning that the app is always up to date on the go! Just be careful as the default link doesn't lock rows that are already being edited.
Hitting the cross will exit the edit form without submitting changes made, while the tick will submit them. The default for each box will be the chosen record's values.
Clicking Edit photo will show the existing photo and give the user the option to change this to a different one:
Since the photo is only stored in a temporary gallery it won't truly overwrite the existing photo until the edit form is submitted.
Power Apps in Power BI
Hopefully the above has proven that Power Apps can read and write data, which is a facility Power BI has lacked until recently...
Power Apps is now a visual in Power BI, and can be used to create or reference existing apps.
When creating a new app via this visual, this option will appear at the top of your sources list:
This is crucial as it gives you access to the PowerBIIntegration.Refresh() function which tells Power BI to refresh it's source.
Once you've created your app make sure to choose File -> Save and then Publish or it won't update in Power BI!
From there pass in the columns from Power BI into the visual that are needed.
This could be used to update sales records, edit mistakes on the fly or just present data in a different way. Since the data is from the same source Power BI visuals can filter the Power App.
Here for example is a Power App which updates a table. The values in that table are then passed to a stored procedure. Effectively this allows users to choose their own data in a simple manner.
Power Apps fills in a hole that Power BI has: namely, being able to write back to the data source. It also lives up to its claim of allowing you to "develop apps with no code" for the most part.
Finally, it does what I hoped it would do: lets me take photos and update expense claims on the go!