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.
Existing expenses
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.
Conclusion
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!
