Working at a large public university exposes me to some very interesting variations in process management. This is in high contrast to the small and medium size businesses within which I have served for most of my career. I should qualify that the university does not have centralized process governance in place. Very little is centralized. It is not in the nature of a university to create such a homogeneous environment. In this regard, one university can look similar to many small businesses.
What this means is that process automation solutions vary widely from paper systems to integrated “enterprise” solutions. Unlike a small business, a university has countless processes. Each department has a different mission and with all the research taking place, it's like having a thousand business models. One way that some have chosen to cope with this variation is to use tools like Microsoft Access and FileMaker Pro to automate their processes.
First, The Tools
To be sure, there are many tools like these two available, but they are by far the most popular. There are many products that offer the ability to maintain lists. You could use Excel or SharePoint in the Microsoft realm or Google's Sheets is an online option. However, these tools have limited ability to build sophisticated applications.
On the other hand, database tools like Microsoft Access and Apple's FileMaker Pro are very flexible in their ability to craft a final product. To accomplish this, they have four distinct sets of integrated capabilities:
- Data Modeling
- Interface Design
- Report Builder
- Programming Logic
Many basic applications can be built without the fourth capability, but It can be particularly useful when there is special business logic required. Let's take a quick look at each of these.
I struggled with whether to use the term “database” here because indeed both systems have an integrated database. However, you are not restricted to using it in either case as both systems support Open Database Connection (ODBC) protocol to interface directly with traditional SQL databases like MySQL and Microsoft SQL.
The tools start with the ability to design tables (or lists) of data. As you can see from the screenshot above, there are many options to control the data even at this level. Putting constraints on the data at this level ensures consistency throughout the application.
The tools also allow users to define relationships between the tables. These relationships can also constrain how the system behaves. A parent -> child relationship can be established between two tables. Lookup values in a dropdown list can also be put into a table so that the user can maintain the values easily. The screens above are from Microsoft Access. Those below show the same thing in FileMaker Pro.
FileMaker Pro offers up similar functionality to design and customize data tables as well as the relationships between them.
The similarities between these two products makes selecting one mostly a matter of your office environment. The main difference between them is that Microsoft Access is only available for Windows. It is included with Office Professional edition, so many users already have it available.
If you are using Apple's Mac OS X, then FileMaker is the better choice. It operates in both a Windows and Mac environment. Both Products can be deployed to a web based environment. However, additional purchases will be necessary in both cases. Access can deploy to a SharePoint server, but you cannot use the free version of SharePoint for this and the functionality for web based applications is severely limited. FileMaker has a server product as well and its applications can be deployed through a browser much more intact. You will need to buy concurrent (simultaneous) user licenses for this product.
For the end user of an application, the screens by which they interact with it determine whether the application is useful or not. In this regard, both products offer a robust set of interface elements to make an application appear familiar to users.
This screen shows an example of a screen layout designed FileMaker Pro. A very similar screen could be just as easily be designed in Access. Notice that there are buttons (in blue), a tabbed section at the bottom and parent table fields at the top of the screen (with each tab representing a different child table).
Many applications do not need sophisticated rules to manage data. The basic functions are often referred to by software developers as CRUD, which stands for Create, Read, Update and Delete — the four basic things you can do with data. If you were building an application from scratch with a programming language, you would need to write code for each of these functions.
However, with tools like this, CRUD functionality is built in. Notice at the top of the screen above, there are a series of controls. These allow you to search and navigate records as well as add and delete them.
On the screen above, the custom blue buttons are used for navigation, but both Access and FileMaker allow designers to easily create menus to navigate more complex applications. This ability to expand applications is both a blessing and a curse, as we will discuss in due course.
I'm always amazed at the number of corporate reports that are generated with spreadsheets. This seems to prove a point that I've been postulating for years that “user familiarity” is far more important that “user friendly.” Building reports with either of these database tools could not be much easier. Furthermore, since reports are dynamically generated on demand, the timely process of extracting data and formatting it is eliminated.
A report “wizard” guides the user through the report creation process starting with selecting the data to be included in the report. Queries of multiple tables of data can be built in advance that allow users to combine data from different tables into a single report using the relationships that were set up with the data.
Different default layouts are available which can be modified to suit specific needs. Summary functions are also available for averages, counts and totals. Multiple reports can even be designed together to create multi-section reports that represent different views of the data.
Building applications to automate processes will eventually lead to the need to add some business rules into the application. Both FileMaker Pro and Microsoft Access offer two levels of functionality here.
First, there is a macro builder. This allows you to string a series of built-in commands together so that you can put a series of steps into a single action like clicking a button. These macros can also be triggered by events such as when data gets added or updated. You can also have conditional events trigger a macro like when a value exceeds a certain amount. This may seem a bit nebulous of an explanation because macros are highly flexible so they are difficult to explain generically. Let's look at a specific example. Here's a commonly used one in business process workflow situations: let's say you have built an expense tracking system. Users can spend up to $1,000 without approval. So, we set up a trigger on the amount field on the form that automatically starts a macro if the amount entered exceeds $1,000. This macro checks the approver and approved date fields to make sure they are not blank. If they are blank, the user is prompted to fill these in before saving the record. If they have been properly filled out, the user will not even notice that the macro has run in the background.
These features are easy to add if you are able to imagine the steps you want to perform. A list of available options is provided in dropdown lists at each step and basic options are presented in a very visual fashion, so unlike regular programming, you don't need to remember the available options for each function you're using.
Of course there are times when a simple macro is insufficient. For these cases, both tools offer the ability to program logic much as a traditional software developer would do. Programmers in this environment have the benefit of being able to call powerful data management functions that are built into the application rather than create them from scratch, but there is little difference at this level from traditional programming. For this reason, it is best to avoid using this capability as it begins to defeat the purpose of these tools.
Ramifications for Process Automation
Many processes consist of nothing more than gathering, validating and using data. Of course as usual, the devil is in the details. The exact data to be captured can vary from one process to the next. If you buy a commercial off the shelf (COTS) solution for your data management needs, you will likely be forced to work with the data elements the solution is built to collect and in the format it collects it. If your process does not conform, you will need to alter your process.
COTS solution designers will argue that because they work with many organizations they are able to build “best practices” into their tool. This term is often confused with “common practices.” In reality, “best” practices are by their nature not common. If you think you have a better way of doing something, chances are good that it will be different. You may need to build a customized tool to support it.
As business process management professionals, we may be tempted to believe that we should be using a Business Process Management System (BPMS) software product for this purpose. A closer look reveals that most of these tools provide the same four elements as mentioned above with the added ability to overlay them with some sort of flowchart (typically BPMN).
If your organization is going to practice full-blown process governance and truly manage your processes via a “process of processes,” then BPMS tools probably make more sense. However, if you are doing one-off process automation and your organization lacks strategic process governance, these tools may be just the answer.
A Word of Caution
While Access and FileMaker are great for rapid prototyping, they are generally not very scalable. Over the years, I have seen many an application developed with these tools run afoul of an expanding user base. They are not enterprise level application environments. If you have hundreds or even a dozen users, you can start to run into issues with both performance and data integrity, especially as applications become more complex.
If you are automating a process that only involves a few users, they can work great for a long time. If the application needs to scale, you may want to consider experimenting with and piloting new process variations with these tools and then utilize a more robust application development environment for your permanent solution.
I have written in the past about modern agile software development techniques (you can find these Columns on the BPTrends website. These techniques rely on many iterations and continual refactoring of work already performed in order to optimize the design of the application for fit to the process. Traditional programming requires more time and more expensive expertise. If these iterations can be performed with tools like those discussed here, iterations can be performed much more quickly and at substantially reduced cost.
The finished prototype can then be used as a model for a more robust enterprise grade application. This also has the effect of empowering people closest to the process to make and try out their own variations until they are satisfied with the supporting process automation.
Whether you are using database tools for prototyping or for actual process automation, they can provide a rich set of capabilities to personalize the data management associated with your business processes. This is not to say that anyone can build good applications with these tools. One must still learn the basics of relational algebra and user interface design. Good business analysts should have these skills, so it is an incremental step for them to learn one of these tools and use it to build working prototypes.
The possibilities are endless. That's really what you need to put “best” into your process automation. Obviously, most processes cannot be completely automated and it is often the human components of a process that bring the best practices to it. However, sometimes the supporting data management must have its own unique set of characteristics. In these cases, you may find tools like Microsoft Access and FileMaker Pro to be just the answer.