Last Monday, I was supposed to have a meeting scheduled by the County Administrator. People who were also invited were the Deputy County Administrator, Treasurer, Finance Director, and IT Director. I was a bit excited and really looking forward to it. I even started working on a website on Sunday to help me dig through the general ledger data I had acquired from a FOIA request, since the spreadsheet I was using was getting very complicated with formulas and wasn’t very interactive. I had only just started laying the foundation for a database and connecting to it from a test page.

Mondays Meeting

I arrived a little early at the Government Center and saw that the scheduled room had the lights off, with only the television/monitor on.

I went to the county administrator’s office next door. I spoke with the deputy clerk, who informed me that the room was not reserved, that the County Administrator was working remotely, and that the treasurer and the deputy county administrator were out. As I read through the list of names on the meeting invitation, he said both the Finance Director and the IT Director were in. Given that I had recently been invited to the Finance Director’s office the prior week to discuss my findings, I made my way over to her office.

Upon arrival, she wasn’t there. One of the staff said she had just left to go to another part of the building. We suspected that I might have missed her, or that she was taking another path to the meeting room, so back I went to see if she was there. Nope. I checked my email, waited a few minutes, and then headed back. The staff tried calling her mobile phone, but it was left on her desk, so she would probably return soon.

Eventually, about 15 minutes after the initially scheduled meeting, I met the Finance Director. She informed me that the meeting had been canceled. I looked through the email thread I had and my spam folder, but saw nothing. We went back to her office to review the email chain she was on. The County Admin addressed me in the body of the message but didn’t add me as a recipient, so everyone assumed I was aware the meeting was canceled. The IT Director also came in and introduced himself as we all became aware of what had happened.

Telnet Financial System

After the IT Director left, the Finance Director and I spoke briefly, picking up where our last meeting left off. I explained that ClearGov was too broad, only displayed Year-over-Year metrics, and didn’t offer much for drilling down into details. I mentioned that we hadn’t gotten around to demonstrating the system she uses to manage the finances.

She opened a screen to log in, and I immediately saw a login screen that looked like a throwback to the days of Bulletin Board Systems (BBS). The login screen was an all-black background, no graphics, mostly green text. Much to my horror, I slowly realized she was telnetting into the system. Telnet is the Internet’s version of dial-up to talk to BBSs.

We didn’t really have to dig much into what she could do when interacting with it. Another county still uses the system, but they have two RPG programmers on staff to write custom reports. Our county does not have that luxury. She brought up a familiar saying: to get anything done with the system, they had to try to resurrect someone from the dead. From what I understand, the system might be from IBM called “Bright” – but that may be short for something, or an acronym.

There may be an RFQ soon to start replacing the system for both the Treasurer and Finance offices in the next fiscal year. The main thing is that both the treasurer’s and the finance systems need to interact. My two cents were to allow some kind of read-only interaction from the public via API calls. I also recommended some kind of automation to send reports to stakeholders when anomalies were detected, such as the one that I had found in the fuel reports. Honestly, I’d be happy if there were some way I could just pick up a CSV file of data each night somewhere. If I had more time, I would have given her more advice on how the systems should be upgraded – mainly to allow the old system to be operable, to allow a smoother transition if problems came up.

We also discussed the chart of accounts data map I had sent her. She wasn’t able to use the Excel file, but the Google Sheets link worked. She was questioning why I would need to know the specifics of what each number in the account number meant. I was trying to explain that it lets me filter the data and understand what it’s associated with, but I couldn’t explain it well.

IT Director

As I walked toward my car in the parking lot, the IT director was pulling out of his space and stopped to chat. He mentioned that he wasn’t too keen on adopting cutting-edge technology. I could understand his point, but the system they had… was a few decades old with the technology it used. Even Windows 95 would be cutting-edge technology at this point.

Clever Y2K Fix

I was a bit concerned about the backend database. With old systems, we often run into flat-file systems and Y2K bugs. A flat file system is complicated the change the length of an existing field without significant downtime or overwriting part of the data in the field next to it. The Y2K bug is the simplest example of this. If your flat file has a 6-character field for dates stored as YYMMDD, switching to a four-digit year pushes the DD into the next column, unless you rebuild the file. If you have hundreds of millions of records, this takes a lot of time (days or weeks), and your data entry centers will likely not be able to do anything. I told the IT director to remind me to tell him the story sometime about a fix we had made that bought us some time. The fix was to use other characters instead of digits for the decade. So December 31, 1999, would be 991231, and January 1, 2000, would be A00101. A means 200, B means 201, etc. Letters gave us 269 years to breathe easy. Lower-case letters went in the opposite direction back in the 1800s and 1700s, which was sometimes necessary in this system. The only thing needed to change was the front-end software to understand what that letter meant.

RPG

I looked at a few code examples of RPG Reports. There are a few versions, and without a platform to test on, the data schema I would be working with, or the platform version, I would be in the dark. There is also another problem. What use would it be to learn this skill other than as a one-off event? This is probably why most people don’t go running off to learn outdated languages. Too constricting and not valuable for most jobs.

Website

I started to work on the website, adding lookup lists and a chart. I mainly wanted to see a comparison of year-over-year (YoY) trends so I could see whether one year had more or less spending than another and whether there were seasonal patterns. It also helps to determine if there are outliers from standard patterns. Here is an early prototype that lets me filter the data.

I sent these screenshots to the Finance Director that evening to help show why I was interested in what the account numbers represented, since all four fields I can filter on are part of the account number.

Down With The Sickness

Wednesday came around, and I was sick. I ordered a test kit for the pandemic, but it came up negative. Over the next few days, three people reached out to drop off food and medicine. One of them was the same person who reached out two years ago when I was also sick on Thanksgiving. The number of people who reached out online was just incredible. Most of the time I spent in bed. But all throughout, I’d also do a little work on the website.

Big Updates

That brings us to Sunday Evening, where many updates have come along, making it pretty powerful for funding information.

• 15 different filters
• Filters can have multiple values
• Filter state preserved via query string (book markable, share links to same filters)
• Can type to search each filters lookup list by number/name
• Can control the series name (grouping) to include any one of the filters (Fiscal Year, Batch, Revenue/Expense, OL1, OL1Func, OL2, Department, Account, Vendor, Purchase Order, Check No. Invoice)
• Graph shows Fiscal Year starting in July
• Numbers on left of charts are formatted with “K” and “M” suffix when appropriate
• A separate chart shows the totals of each series
• The monthly chart data can be paged if more than 1,200 data points match
• The data that makes up the chart is listed as the original format in an infinite scrolling table
• The Net Amount is highlighted with a gradient based on the min (green), max (red), and median value (white) to help identify outliers

There are plenty more features, but this is generally best to show off for now. I’m using Vite, React, Typescript, MUI, Recharts, React Virtuoso, React Router Dom, and React Query. It’s a LAMP platform consisting of Linux, Apache, MariaDB, and PHP.

Source Code: https://github.com/lewismoten/wcfac-general-ledger

Test Site: https://wcf.regaldragondanceparty.com/

Into Monday

It’s 2:30 AM on a Monday. The worst part about being sick is that every time I lie down, I start coughing up a storm. My doctor’s appointment is at 11 AM over in Fairfax. I tried sending a message a few days ago to see if they still wanted me to come in, given my condition, but I hadn’t received a response. I took another test on Sunday and still remain negative.

I haven’t heard from the county to reschedule last Monday’s meeting. However, with Thanksgiving, the government was closed on two of those days, and I think the treasurer was out for the week.