I’m rippin’ through code these days, doing things I couldn’t figure out just a few months ago.
Don’t get me wrong. I give all the Glory to God Most High, Who has guided me every step of the way. I’m not bragging about my abilities. I’m just documenting the outcome.
I had some issues with the spreadsheets created from my intranet site.
But first, some background.
When my predecessor built our intranet system, one thing he did was find and use a piece of code from the Internet which enabled him to download an Excel spreadsheet to the local user. It’s not really an Excel spreadsheet; it’s actually an HTML page, which Excel is told is an Excel spreadsheet, and which comes down to the user as a read-only document. To manipulate it, you have to save it as an Excel (editable) workbook first. It’s a small sacrifice to get good data and provide stuff our internal customers can use.
But, when I had to replace or update some of those pages, it became orders of magnitude more difficult to retain the same ol’ pages and just tweak them. There were now three filter criteria to apply, and to do that, I have to acquire the data for each filter before getting the filter(s) for the NEXT step.
That is, my boss wants to allow our users to be able to select a name from a drop down list of customer service reps (CSRs), and then have a second drop down list populated with the names of the customers specific to that CSR, and THEN a drop down box of divisions associated with orders for that customer in a third drop down list. Got it? Three cascading drop down boxes. The first is CSR, the second is customer, and finally division.
The page then generates a spreadsheet based on those selections. Choosing a CSR narrows the scope to customers for that CSR, and divisions associated with the orders for those customers. Choosing a customer from the list then filters the divisions to those with orders for that customer. Choosing a division from there narrows the orders to just those from that specific division, for that specific customer, for that specific CSR.
Well, it turns out cascading drop down lists like this are common and very tricky to implement. The more you have, the more “if-this-then-that” (or IF-THEN) statements you need to account for them all. (More accurately, the more ELSE-IF clauses you need in an IF-THEN-ELSE statement.) So just ONE additional layer of complexity adds orders of magnitude of code, as the developer (me) sorts through required output for each selection made.
So, while my predecessor built a decent system and it worked to filter by customer and division, updating his code to account for the CSR filter wasn’t feasible. The pages in question are spaghetti code as it is, tangled messes of HTML and Visual Basic in an orgiastic and headache-inducing cluster. UGH. No thanks.
So, I chose to rebuild the pages from scratch, using modern techniques and controls in a compiled web application which doesn’t have spaghetti code, and allows for easier data presentation.
What it doesn’t ease, though, is getting the information into an Excel spreadsheet.
In itself, this is a conundrum. Why does Microsoft make interaction with and creation of Excel (or any other Microsoft Office Suite program for that matter) documents impossible without a third party software engine, or an overly-complicated and completely user-unfriendly Software Development Kit (or, as we in the biz like to say, “SDK”)? Why do they not love each other and work together easily? No one knows.
So, I have a page which takes a list of all the orders we currently have open in our business group. (Logically enough, we call these “open orders”.) We receive this information every night. I take that stuff out of a nice, safe database, put into an in-memory database representation called a “data table”, and I add a couple of columns. One has the name of the CSR for each customer on the nearly-5000 line list of orders (the other is a numeric ID stored in another database). This enables filtering by CSR.
Next, I get a unique list of customers on the report, which allows filtering by the customer name/number (number, really, but I show the names). Finally, I extract the list of divisions (which is what we call internal departments or business groups which manufacture and drop ship parts to customers) from the list of orders, and add that unique list to another drop down list. This enables a third level of filtering, by division. See how simple?
Now, I take that table of information, whether filtered or not, and pass it to my awesome and amazing third party Excel library to create a new spreadsheet. And voila! – a new spreadsheet, showing the requested/filtered data.
So, what’s the problem? Well, the filtering is done using a special in-line query syntax, integrated into my programming language of choice. It’s called LINQ, which is a sort of acronym for “Language INtegrated Query.” But, I found out the hard, painful way, the LINQ queries I created to pull the various records transformed the date fields to a date-time value, which Excel sees as a string.
My first fix was to ask the database to spit out a formatted field, which it does…as a string (A “string”, in this context, is a “string” of alpha-numeric characters; you would, in human speak, call them “text”) instead of dates, like they oughta be. *Sigh*
Regrettably, I discovered this date-to-string conversion AFTER the page was deployed to production and live. So Excel creates the page so beautifully, but when you drop down the list of dates, instead of grouping them by month, they’re not grouped at all; they’re listed individually. All 5,000 of them.
So, back to the drawing board. I tried to coerce my spreadsheet program to change the data type on those columns to dates. It did…but they still showed the same way, and still didn’t group. What the…? Okay, then I asked the database to STOP formatting dates when it spit them out, and it did…and they still showed up the same. How the…? Close to stumped, I dug deeper and found the problem must be, has to be, the LINQ query used to get all the elements together.
This led to more Internet searching. Most-of-my-free-time-at-work-spent-doing-research later, I finally found a couple of ways to cast the query fields to the data type I want (date-time). I test, and BANG! Works like a charm. As they say in the UK, it works a treat.
I’d high-five myself but I can’t take credit for the code. Only the dogged pursuit of a problem I should have ignored with other deadlines looming.
But hey. My page works like it should and makes my predecessor’s page look bad. I hope that makes me look good, but you never know. Well…*I* never do.
Hope you had a good weekend, all. And Happy Monday.