The Longitudinal Evolution of Self-Taught Application Development
In my work with Charleston County Public Works Department, my job title has been simple. Throughout my two-year summer internship pre-grad, and as a temporary employee full-time post-grad, I have been tasked with one thing: automate paperwork. With no restrictions or guidance outside of to make the best I am capable of, this process has evolved over the years and lead to a number of lessons learned. Looking back, this experience has taught a number of extremely valuable lessons which school simply could not; and so, I’d like to cover a few of them. This blog post will summarize the various, occasionally foolish, ways in which I’ve tried to make applications over the years, and end with what I think is finally approaching a professional product, my current implementation of the Finance App in Electron.
MS Excel and VBA
Summer 1 saw the development of a well-intentioned but doomed implementation of automated forms.
Handed a spreadsheet that contained the existing prototype for a series of forms, including a stormwater billing calculation spreadsheet, a travel request form, and a county-issued-work-credit-card (PCARD) purchase recap form, the natural response was to improve and retool the existing forms. Excel is tolerable for design of forms, as it allows for quite a few features that are surprisingly useful; cells can be formatted, locked, or turned into comboboxes; sheets can be hidden, allowing for lists of options to be stored in a ‘secret’ data sheet, which could then be referenced by named range; and Excel has excellent integration with Outlook, allowing for a simple framework in which a button could be associated with VBA logic which would send a copy of the current worksheet via email to a target recipient. Thus, the original PCARD recap form (done primarily by Anthony Morrell, a friend and fellow intern), travel request form, and stormwater calculation form (both done by myself) were created.
Their structure was thus:
- VBA to handle calculations, lookups, and other code-based functionality
- Hidden sheets to provide additional form stages (as their visibility could be toggled to switch which form segment a user was on) and to store persistent data in an always-hidden sheet
- Saving copies to targeted locations on networked share drives for backup management and storage
- Appending lines to log files to track activity, again in the network drive and with a mirrored file to allow viewing of the logs
- Automatic sending of copies of the sheet via email to automate the form submission and review processes.
However, the pitfalls were significant.
Firstly, every major operation relied on file IO with a networked drive. Submissions sent copies via email, or wrote new files to the share drive with an algorithmically generated ‘unique’ id that was susceptible to collisions. Logfile write operations were subject to race conditions, could be locked out by other users, and would crash if the target location was modified or removed. Files were duplicated excessively, leading to every stage of every submission being an entire standalone duplicate of the excel sheet, with all its code self-contained. Versioning was impossible to manage, because anyone could use any version of the form and all bugs in an older version would continue to plague the system if users failed to replace their base sheet with the updated version.
Secondly, VBA is a severely limited programming language. Significant hours were spent bug fixing issues which were frequently caused by a required file reaching an unexpected state. Since VBA code must be manually enabled on a spreadsheet, even ‘on-open’ code could not be relied upon to trigger immediately, and worse, some client-side excel settings would override VBA-side required settings, making many operations behave unreliably.
There are more issues, but ultimately, the best lesson learned here was that one’s choice of framework at the beginning fundamentally determines the potential of a project; in other words, if your foundation is poor, your building will be shoddy as well.
Python and Tkinter
Summer 2 began, and with it a paradigm shift. A few more classes meant a greater confidence in Python, and a determination to create something a little more ‘legitimate’. The fundamental design flaws of emailing copies was addressed and solved with the discovery, and self-teaching, of SQL. A DBMS solves the issue of (nearly all) file-based operations, by allowing one to simply store the data in an environment that is perfectly designed to allow for things like concurrent users, implicit data validation, and logging to be done easily and quickly. Gone was the need to distribute a copy of the software embedded in every excel sheet form sent around; Now, users could simply open their copy of the software, which would query the database to see what forms they had pending, allow form completion, and use SQL again to push the results back up to the server.
Switching to python was an exponential leap in design and quality due to its fundamental strengths in the scripting department; any function that the software required could be easily and efficiently thrown together, and the resulting code was far faster and more structurally sound than the raw and unreliable VBA.
However, while Python solved many fundamental issues of core logic, code stability, and data handling, it did not solve every problem.
Raw python is problematic for interface-based executable application design for a few significant reasons. The first is tkinter. When determining how to make a GUI, tkinter appeared to be the best option; it’s as old as Python itself, but it’s well integrated, and technically provides every feature one would require of it. But this choice would lead to significant losses in the long run; tkinter simply put does not hold up under modern scrutiny. Tkinter is void of many modern element design features, with things such as a calendar picker only extant if one makes it themselves, leading to a number of core features to be hacked together and ugly, despite the fundamental customizability that tkinter provides; if you have to write every ui element you use from the ground up to make it look and act the way you want it to, you’re going to lose a lot of time. Worse, tkinter’s limitations can only be coded around within a narrow window of limitations before it becomes a ridiculously slow experience. Making a custom calendar popup to select the ‘departure date’ for a travel request? Fine; but don’t try to display two calendars at the same time; tkinter will struggle to render such complexity, and your application interface will deadlock. Want to make the ui elements auto-resize win the window is dragged? Hope you don’t suffer from epilepsy. Want an auto-complete combobox? Don’t type too quickly, then, because tkinter will chug through the calculations trying to guess what you’re trying to complete, and your final result will be a jumbled mess of half the letters you’ve typed, and half letters that it expected you to want to type mixed in seemingly at random. Am I just not that good at designing UI elements, and too unskilled at working around tkinter’s limitations to see its strengths? Perhaps; but not all graphical interface frameworks are created equally, and like Excel, Tkinter has some things which it is simply not well suited to doing.
The second major pitfall of python was on the application packaging and deployment side. I made one decision early on, that sealed my fate; this project would use python, it would be packaged into a standalone application, and it had to be an .exe. This last requirement meant using another framework, the extremely helpful, but nonetheless imperfect, auto-py-to-exe, which simply provided a convenient interface to the actual bundling software of pyinstaller. Thus, executables were possible; but still limited. To release a new version of the software, I would generate the new exe… then zip it, and email the zip to the users. To prevent people using an out of date software, a version number would be hidden inside the code and compared against a database query; if the versions did not match upon connection to the database, the software would warn the user to update their software, then close itself. Similar such logic controlled the access of users to restricted functionalities; a master list in the database would track all users based on their username (the database itself only being accessible via intranet), allowing only recognized users to use certain features within the software, and only specifically authorized users to use others. But despite all this, the release process was still “copy this file from your email, manually extract it, and please delete the older version”. So much progress in some places, so little in others.
Electron
In my final term at school, having taken classes which finally covered the proper usage of SQL, and another which created a very basic familiarity with Node.js, an idea came about; web-styled software design. Interfaces created in css and html are by far the most intuitive and high-quality with the least wasted effort; they are pillars of web-design for a reason, and are capable of seriously advanced interfaces. And with ES6, javascript had finally evolved into a language that was not only usable, but has some genuine advantages over more heavyweight languages. But how to make a software that utilizes the best of these modern tools? Electron. After some google searching, it showed up in the results; and I was shocked to see that my own favorite IDE, Visual Studio Code, is written in it. A software which has everything that I wanted mine to have; an installer, an auto-updater, a responsive and modern interface, and ui design experience from this decade.
Now, as a recent graduate, I am converting all my former Python code to Node.js, all my Tkinter interfaces to html, and the horrific version management, manual deployment, and drag-and-drop installation process of zip files and exes with an integrated fuction of Github’s and Node’s deployment processes, all further improved by the utilization of the electron-updater extension for automated self-updating of the software upon release on a new version.
The sql tables have been redesigned to have proper normalization (A spcialized variant of 3NF with deliberate redundant canditate keys to allow for more dynamic search and report generation queries). The software unifies the interface design of a website with the surprisingly good coding capabilities of modern javascript. And it’s all contained within a self-updating software with a proper installer, a convenient deployment process through github’s release management system, and what I hope is fundamentally sound design within. While I am confident that the future will hold even more significant leaps in design skill and understanding, I am also proud of how far things have come from that original Excel form.
A toast to the future, then; where I hope to be just as embarrassed by this code, as I am now of the code that came before. To progress! To programming! To (currently) electron!