Trevor Gwin's Portfolio

In late 2020 the software vendor who provides the Point-of-Sale database software for my Families Scooter Repair business decided it was time to migrate everyone from their on-site hosted product to their new and shiny cloud- based product. The issue however was that my family had nearly 15 years worth of service records that were supposedly going to be transferred over to the cloud product, but disappointingly were simply not. My dad had just about accepted that his data would vanish into thin air, when I decided to see what I could do with my then limited skill-set stemming from a Basic one-semester course in Microsoft’s aging “Access” Database software.

Learning a lot along the way, I managed to finesse the somewhat petulant POS software into surrendering to me all of the data in a convenient CSV format, and got to work bringing the data into some sort of database. Although my course was in Microsoft Access I’m under the impression that Access is somewhat “old news” and inferior to more modern solutions; I approached the course from a perspective of a Database Science parallel to the way one learns to play a recorder as an intermediate step before moving on to play a more substantial musical instrument. In addition, my families business had done without a Microsoft Office license for this long and I wanted to see if I could let them keep this streak going. I settled on LibreOffice’s answer to Access, LibreOffice Base.

The workflow I came up with consists of two primary steps and goes as follows:

Later, I decided to bake the unchanging Legacy Data into the template Database, as while my Python Script took less than one second to run, the import process froze up LibreOffice Base for a good minute or two as ~160,000 Line Items and ~20,000 Work Orders were imported in:

The result is a convenient and portable .ODB database file that runs incredibly quick and is easy to use. Opening the file shows a variety of helpful reports that can be generated with one-click, we can give it a try using this lovely pre-owned Buddy Scooter for sale:

I designed the license plate lookup to be case-insensitive and to also be a somewhat “fuzzy” search. So even if the license plate isn’t recorded quite perfectly, any of the following records would still match:

“21H2686 CA Plate”, ” 21H2686″, “21h2686”, etc

In just a couple of seconds the Firebird Embedded database searches through our ~25,000 Work Orders and gives us a 17 page report which we can view, print, e-Mail as a PDF, or do anything else the powerful LibreOffice Writer software is capable of.

Pursuing this document shows that although the Scooter has been in use for nearly seven years, it has an extensive Service History, had one small kink with a turn-signal switch ironed out, and was well cared for and can be expected to provide lovely reliable transportation for years to come!

The screenshot above reminds me of one of the challenges in building the database, figuring out a way to have multi-line text remain as such in the database and appear as such in the final report. The exported data from Lightspeed had newline characters that would show up and work in Libreoffice Calc(The Excel equivalent), but when imported into the database were removed. Initially I just replaced them with spaces, but this resulted in a cluttered view. Eventually I ended up coming up with my own newline “character” consisting of the string ” {nl} ” and then in the Report having a filter to replace it with a Unicode Newline character that is honored by LibreOffice Writer in rendering the report. This could very well be a case of having a very simple solution under my nose that I just don’t know about yet, however my approach is working flawlessly thus far.