MultiValue and QuickBooks, Part 2: Exporting to MultiValue
In my first article, I explored how we can export multiple MultiValue data files as transactions into QuickBooks. While QB allows the importing of standard CSV or tabbed files or Excel spreadsheets, it has a private format called IIF whereby a text file can contain rows with multiple table definitions as well as actual data.
In the other direction, it will become evident that QB will appear "short" in the eyes of a seasoned MultiValue programmer. Meaning, while the stock reports and modified reports (memorized) within QB may satisfy a QB-oriented user, your client's requests for more advanced reporting may not be possible within QB.
While there certainly is some form of database within QB, there really is no direct access to it. Their reports drop down and the ability to take stock reports and modify the columns and selection criteria is better than simply stock reports alone. But we MultiValue professionals are used to having full access to our data to manipulate it in any fashion that is necessary to satisfy the requests of our users.
On other systems, you may be able to use Crystal Reports or MS Access to hit the same database that the application is running. You would be careful to not update anything unless you fully understand the application itself. That goes without saying with any "back door" access to the underlying database.
For retrieval purposes only, you don't have to worry about "rocking the boat" with the application. You can cherry-pick whatever fields that you need.
In my decades of slicing and dicing a raw Open Accounts Receivable file within a MultiValue system, I find the QB stock reports adequate and their modifiable parameters to be average. Obviously QB isn't perfect otherwise I wouldn't be writing this article.
There are two separate reports, Summary and Detail, but since we cannot modify the sort sequence, we are stuck with the sequences pre-programmed into QB. Coming from a full-access database (MultiValue), I get frustrated when trying to create custom reports using this pretty popular file.
In all QB reports there are prompts regarding single or all customers as well as date ranges. You may be able to filter in or out specific transactions. But that's about it, database-wise.
One request in particular was to produce an open AR report by customer and have each invoice within each customer be in date order. The stock Detail report from QB would be in date order only and reviewing each customer from this report is nearly impossible. The Summary report is in customer order but doesn't show any detail.
Virtually all QB reports can be directed to a printer for obvious printing or to a spreadsheet for external review. This is our connection to get the data from QB into regular MultiValue fields for our use.
When you open a stock report, you can see a "modify" button. Stock reports will have a fraction of available output fields pre-checked. You would check all the fields that you would need. The sort sequence is of no concern with this exercise, although you should insure that the user make the date range as wide as possible to accommodate their request.
Then the user would run the report and send it to an Excel spreadsheet. Now we take over with any popular MultiValue tool to import a spreadsheet into a standard MultiValue data file.
Once imported, now the world is our oyster. We can sort and break-on with standard MultiValue commands or create highly specialized reports using MultiValue Basic. In either case, the user gets the best of both worlds. The only issue would be the timing between the two systems.
There are inherent reasons that accounting departments use QB or other external systems. My two cases have a few reasons of their own, and it's hard to debate from our perspective.
First — The incoming pool of clerical talent is much younger and certainly would not have experience with a traditional green screen environment. Their experience with anything GUI, especially the Microsoft experience, will naturally cause them to type certain expected commands, <ctrl-S> for Save, <ctrl-P> to Print etc. Even if you have converted many of your long-term MultiValue apps to GUI, there may be the chance that it doesn't follow the Microsoft model.
Second — Along that Microsoft experience, virtually every command and function has a keyboard shortcut. As a novice MS GUI programmer and experienced Accuterm GED programmer, I barely add them as I usually don't have time for them until moving to the next project.
Third — Companies like QuickBooks have invested zillions of dollars and man-hours into perfecting the user interface and application, despite being somewhat limited in complex database reporting. You can't argue with the extent that QuickBooks is installed.
Fourth — For most companies that use MultiValue, the accounting department is not as important as the departments that align with the company's business function. Whether manufacturing, production, inventory, distribution, logistics, or other business functions, most companies that use MultiValue are not accounting companies, ie. CPAs, banks, brokerages etc. Thus, their programming dollars over the years has gone to core business improvements and not helping those in the accounting department. This leaves the door open for QB or other larger networked accounting environments to come in.
Sidebar — From a recent conversion away from a 20+ year highly evolved MultiValue full company system to Great Plains, the accounting portions (AP, AR, GL) were outstanding and made the bean counters happy. The manufacturing and inventory modules of Great Plains were very "plain" (pardon the pun) and when the full conversion was complete, 80% of the company felt it was a step backwards from their highly tuned MultiValue application. C'est la vie.
No one asks us whether to retire part or all of our beloved MultiValue applications in lieu of a shiny new (albeit more plain) system. We are usually informed when it is too late to make a counter offer.
Thus, we should embrace the opportunity to manage both environments, knowing that we still have plenty of work on both systems.
