My principles of automation
- Let people do what adds value by automating tasks that do not.
- Reduce user error by automating quality checks.
- When appropriate, make formulas and rules reviewable and editable. Don't bury them in code.
- Re-purpose information for efficiency and competitive advantage.
- Keep the next developer in mind: write self-documenting code with lots of comments.
My background as an intelligence analyst gives me a perspective on automation few programmers have. In one of my very early assignments, I would spend up to 50 hours in a week doing data entry and double-checking for the data I needed for intelligence analysis. The data I was entering had to be perfect, so the 50 hours were necessary --- but those hours were time not spent on analysis.
That experience and others have shaped how I approach MS Office automation: I do not simply automate a task, I make the end-user's life easier. I build quality assurance throughout. Where possible, I have it add value to the information it processes. My aesthetic sensibility and eye for design help me create user interfaces that are intuitive and attractive (as my slideshows on this website will attest), setting me apart from other developers.
Below are a few case studies on non-sensitive projects that illustrate my principles of automation and user interface (UI) design. Also see the case study for "CANINE" on the "Creative problem-solving" case study page for an application that was also a major process improvement.
|MTS Systems: Pricing tool for thousands of products
|Minnesota Astronomical Society: Membership management system||
|GTT: Highly-customizable reports through friendly interface||
A team of six product managers sets prices every year for as many as 1,000 products per manager. These products range from washers and screws to large product assemblies. Because after-market parts are a significant revenue source for the company, the pricing of even washers and screws requires attention.
Besides the large number of prices to be set, the task of pricing was complicated by the large amount of information needed for informed pricing decisions: cost, cost change from last year, last year's price, last year's price in Europe, sales volume, how the part or finished product is used and packaged, and more.
- The volume of supporting information needed could obscure something important.
- The enormity of the task poses a risk of over- and under-pricing.
- Many products have similar characteristics and so could be priced in a similar way.
- Recording the reason for a price was important, but unreasonable to do by hand for 1,000 products.
My solution would therefore need to address four areas:
- Maximum flexibility in how to filter and sort things.
- Permit things to be viewed and priced as a group.
- Automatic recording of pricing reasons.
- Use of large symbols and conditional formatting to bring attention to important things at their appropriate point in the pricing process.
I created a distributed MS SQL Server/Access/VBA application called PACRT: Price And Cost Review Tool (pronounced "pack-rat"). With PACRT, product managers can easily set prices as they like. With filters on the back-end, each manager sees information on their products and no one else's. A management version of the app let the supervisor see progress reports and review any product manager's work using the same quality assurance tools the product managers had.
The primary features of PACRT are:
- Users create product categories and assign products to them.
- Users can view cost, price, and sales info by category or individual product. They can sort and filter by a large set of characteristics, including wild card searches of product descriptions.
- Users can set prices for an entire category either by a fixed price or a target margin (with some customizable exception rules if desired).
- Users can still review and set prices for individual products if they wish.
- A quality assurance screen lets users identify problems: they select a problem type from a menu, then see which of their prices have that problem.
With PACRT, a product manager can now make informed pricing decisions on 1,000+ products in a few days. PACRT was so successful that I was asked to create something similar for the pricing of services, which I did.
Click the graphic at the top of this case study to view a slideshow about PACRT.
The Minnesota Astronomical Society (MAS) is a non-profit organization promoting public knowledge and enjoyment of astronomy. MAS maintains an observatory and several observation sites around Minnesota and has a growing membership of almost 500 individuals and households.
Managing membership dues, renewal reminders, rosters of key holders to the observation sites and other membership responsibilities is done by one volunteer. The Access database that was used to manage this had been created almost 15 years earlier by a non-programmer. Most of the database's limited features had ceased to work years ago. As a result, the Membership Coordinator had to spend almost a day each month manipulating the data with Excel to produce renewal letters, reports, and newsletter distribution lists.
- Almost all of the Coordinator's tasks could be automated.
- Mail merges and emailing required that data be perfect.
- Adding functionality meant practice would be needed, especially when features were only used monthly.
- The organization could not afford to hire a programmer whenever a change was needed, so the new app had to be easy to maintain and to transfer to a new membership manager.
My solution would therefore need to address four areas:
- Automate tasks wherever possible.
- Let the automation be maintainable by the end-user as much as possible.
- Verify data quality before mass communication.
- Let user practice using the new app using a refreshed copy of production data.
The MAS Membership Database I created is a wholly MS Access client/server application built with MS Access 2013. It consists of four separate Access files: the user interface, the LIVE data file, a TEST data file, and an Admin (administrative settings) file. The most prominent features are:
- The opening screen of the app is a to-do list of what still needs to be done that month and what data quality issues there are. A click of a button takes the user to an interactive list of members affected by the to-do list or data quality issues.
- The Admin (administrative settings) file stores information on the default location for importing and saving, the parameters for automated tasks, and more. This lets the current user and new users customize those defaults themselves.
- The user interface can switch between using LIVE and TEST data with the flick of a switch. Visual cues and warning messages remind the user they are working with TEST or LIVE data.
- The SQL, parameters, exclusion lists, and other aspects of automated mail merges and mass emailing are all customizable by the user through the user interface.
The ability to switch easily between TEST and LIVE mode lets the user practice a task and see what the counts and output are like using a copy of the latest data. Then, while the knowledge is fresh, the user can switch to LIVE mode and repeat the task for real.
Click the two graphics at the top of this case study to view slideshows about the MAS Membership app.
The client's CRM application, NetSuite, did not provide the level of customized reporting needed. For example, it could not provide aggregate (summarized) data for customers by quarter, product class, and other categories.
- The CRM application used a SQL Server back-end.
- Users needed to search by numeric range, multiple criteria for a given field, date, look-up value, and wild-card criteria.
- Simplicity in the user interface was challenged by the amount of information the client wanted to have available to filter, group, and display.
- The information the client wanted included was located in many tables. Multi-join SQL strings would have to be composed on-the-fly when the user conducted searches.
My solution would need to address three areas:
- Keeping the interface simply by displaying things only as needed.
- Finding a flexible way of producing complex SQL.
- Providing summary (aggregate) data regardless of the results of the search
I produced an MS Access/SQL Server/VBA application called aSSRT (a Self-Serve Reporting Tool). Key to its operation was an administrative table that recorded how one table joined to another and what type of Access form control should be displayed on the form based on the criteria method the user selected. For example, if the user selected "Opportunity Title (lookup)", a list box would appear on the form displaying all possible Opportunity Titles. If the user selected "Opportunity Title (wildcard)", a text box would appear and would accept wildcard characters.
An additional search option was a drag-and-drop pop-up form I designed. This would appear if the user selected a "multi" search option.
Shown below is a portion of this admin table:
- Unless needed by the user's search criteria, many controls remained hidden. Only those that reminded the user what was available were left visible.
- Aggregate data was provided through static queries that were joined to the results table according to the rules I defined in the admin table.
- The user could specify which fields to have in their results and what their order and sort order should be.
- The results were output to Excel to permit further manipulation, such as with pivot tables and filters.
aSSRT proved so popular that I was later contracted again to add new functionality.
Click the graphic at the top of this case study to view a slideshow about aSSRT.