COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Writing an App in Power Apps
Sam was tired of getting told off for crinkled or missing expense receipts. So he had a look at Power Apps and this is what he found…

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 Apps Wise Owl

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:

Power Apps Wise Owl Expenses System

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:

Power Apps Wise Owl 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.

Power Apps Wise Owl Expenses

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:

Power Apps Wise Owl

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.

Power Apps Wise Owl Expenses

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.

Wise Owl Power Apps

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.

Power Apps Wise Owl

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.

Power Apps Wise Owl Expenses

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:

Power Apps Wise Owl

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.

Wise Owl Power Apps

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:

Power apps camera Wise Owl

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:

Power Apps Wise Owl

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.

Power Apps Wise Owl Existing expenses

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.

Power Apps Wise Owl

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:

Power Apps Wise Owl

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 Power BI

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:

Power Apps Power BI Integration

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!

Power Apps 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.

Power Apps Power BI

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.

Power Apps

Finally, it does what I hoped it would do: lets me take photos and update expense claims on the go!

 

This blog has 0 threads Add post