Data entry. The bane of most of our existence can usually be summarized with an eye-roll. More than likely, crying.
As a MarTech developer, I do my best to hunt down process’ that include data entry or other keyboard hypnosis inducing steps and automate the heck out of them. It’s actually my favorite part of the job! But sometimes it’s just not practical to automate tasks, or we have to delay automation until the concept has been proven out a bit more.
So is the case of a certain analytics project I had been working on. This tool gave us a pile of data that had to be manually collected from lead activity records within Marketo. Let me tell you, clicking through hundreds of leads, with dozens of activities per lead was, how should I put it, horrifying and borderline torturous.
This is when I decided that PowerApps would be my half step towards automation. But let me start from the beginning.
The Beginning
We had this tool. It writes data to a lead record in Marketo. The data entry person (myself or a colleague) would open the lead, and copy the relevant data into an excel document where we would then build pivot tables to try and make sense of the thousands of rows of data that we had collected.
Unfortunately this led to several issues — mainly data cleanliness and problems with tracking lead records, all without writing down PII (Personally Identifiable Information ) #GDPR. And while Excel is Gods-gift-to-the-office-world, it’s no database, so with 12k rows of data, our file was getting a little sleepy.
Because of these issues, I decided we needed help automating our data entry process so I began with identifying the pain points within our current process:
- Data entry is sketchy, and going row after row in Excel increases the likelihood of user input errors.
- Excel files get sleepy when they get too big.
- Manually creating pivot tables is time-consuming.
To address these pain points I designed a PowerApps to collect the data, which gets written to a SQL Database hosted on Azure SQL Server. From there I wanted to automatically update the reports so I put a PowerBI dashboard on top of the database and voila, semi-automation!
Since the biggest piece of this is PowerApps, let’s focus on that part.
Designing with PowerApps
Screens
Since each lead record has multiple activities within Marketo, I wanted to break out the pieces of data I was collecting into what PowerApps calls Screens which is essentially just pages of a website.
Multi-page forms
And since PowerApps is at its most basic state, a form, I was looking at creating a multi-page form. The reason I did this is because I can then collect the one-off information like the lead identifier or job titles without having to copy information. It also allows me to silo questions needed for later, so I didn’t have to have a million questions on one page.
Collections
Once I created the Screens and figured out how to add labels, input, buttons and such, I needed a way to stage the ‘1:many’ data fields (one lead, many activities) before writing it to my database. To do this, I utilized a feature called Collections.
Collections allow me to create a temporary table of data that is staged before I submit the data and move on to the next lead record.
After battling through a sea of bugs, documentation, and online tutorials, I finally managed to create an application that does what I want!
Here’s how it works.
- The user will select a document they want to input data on, from a dropdown on the first screen.
- On the next screen the user inputs general information about the lead.
- Once the lead information and document has been identified, we move on to a rapid activity collection screen that will allow you to quickly input activity details while staying with a particular lead record.
- Each time an activity is filled out — which includes information about what page the lead visited, the timestamp, or buttons they clicked, the user can then click a button that rounds up the value of each field (including lead information and document name) which writes it to the Collection.
- Of course, when we’re ready to move on to a new lead record, the user hits the ‘submit button’, which writes the Collection to the database. Once submitted the user has the option to start with a new lead record or a new document.
Now that the basic functionality was present, I addressed some of my own stumbling areas when it comes to repetitive tasks which is, of course, losing your spot and subsequently doing duplicate work.
Gallery
To keep my place, I added what is called a Gallery which lists out the data entries of my collection. I then added a trash icon to each entry in the gallery to make sure if something was entered wrong, I could delete it.
So just like that, we have a PowerApps that allows its users to:
- Input activities on your selected document
- Identify the lead and their firmographics.
- Rapidly cycle through multiple activities without duplicate data entry fields.
- Check on progress.
- Submit the lead, and the compiled activities.
PowerApps Fun Facts
A fun little fact with PowerApps is that anything you want to do, you need to intentionally write it in the function that is triggered when the button is clicked. For instance, if you want a field to be written to a Collection so the user can start over with a new activity, you need to use a ‘collect’ function to add your fields, then a ‘reset’ function for each and every field you want removed.
The great part about this is since you have to individually remove field values with the reset function, you can pick and choose which ones you maintain vs. reset.
For instance, since we have a many to one relationship of activities to leads, when an activity is added, I wanted to clear out the fields and start over with a new activity. The way I did this is by only calling the reset function for the activity fields, but not the other lead and document fields. This allows me to create a new collection record with each and every field, from the entire PowerApps screen collection, without needing the user to re-input the same data multiple times.
Hopefully you have found my work with PowerApps inspiring, and you feel ready to embark on your own PowerApps project!
If you have any questions or have an idea for a PowerApps that you would like help implementing, let me know!
