MultiValue and QuickBooks, Part 1: Importing with IIF files

While the MultiValue database can hold its own against the databases that support contemporary looking (and feeling) applications, there are situations whereby we must yield portions of our entire application suite to more modern packaged software.

This is not a dissertation on being able to reprogram existing modules into a more modern (read: GUI) form. We all have been there and know that the cost far exceeds what the price of packaged software is.

One fortunate aspect of having a combined system (MultiValue and non-MultiValue) is that the non-MultiValue modules tend to be the lesser of the two forms of the company's systems. Meaning, if one form is production/revenue producing and the other is accounting, the production modules are the ones that have received the greater investment in improvements over the years whereas the accounting modules are simply passive and don't evolve as much.

In all my years of MultiValue consulting and programming, changes to my client's accounting modules are the least of my efforts. Once they have been installed, they tend to be good enough as they are and my efforts are spent advancing all the other modules.

In the last year, I've had two situations where my clients have converted portions of their MultiValue accounting modules into QuickBooks (QB). Whether QB, Peachtree, One Write Plus, or Great Plains (other favorites), they kept their MultiValue production/revenue producing modules and use these packaged systems for the mundane accounting functions.

QB seems to be preferred either based on price, support, ease of use, number of installations, or available pool of trained personnel. All have import functions to convert spreadsheets from other systems or manual entries.

QB has an additional file format called IIF that is very helpful for us MultiValue programmers when exporting data from the remaining MultiValue production modules into QB. IIF files are for importing into QB only.

I will use a typical MultiValue Sales Invoice Update function as an example of creating an IIF file. This assumes that the MultiValue system has an order file and a customer file. We will be exporting the Accounts Receivable (AR) portion into QB.

Basically an IIF file is a column-labeled format like a CSV with a header row but with an additional twist. Instead of the column headings being defined once on the first row, you can have multiple file definitions within a single IIF file and QB will handle them accordingly.

Thus, starting with the first row, you will define that first section of data. Assuming nothing, you would export everything that is pertinent to the managing of these AR records, meaning the customer info and the specific AR transaction.

QB has tables called CUST, INVITEM and TRNS. CUST is the obvious Customer file, INVITEM is similar to a Product file and TRNS is for Transactions. TRNS files have additional definitions depending on what kind of transaction they are: INVOICE, PAYABLE, PAYROLL etc. We will be using INVOICE.

QB documents all the fields pertinent to each table. There are plenty of fields per table and some are required. Besides the obvious, there is no indication of being required.

A QB Invoice transaction requires something to be "sold." A typical MultiValue A/R record would only contain the customer, dates and dollars, and the primary key may refer back to the Order or Production system for the contents of the sale. QB does not need to know how many wingnuts were sold. But "something" needs to be sold as a line item in QB as the INVOICE table looks to its lines for the actual data.

The INVITEM file would be used sparingly to represent the kinds of A/R imports, typically sales and perhaps credits. This entire function would be subordinate to the regular MultiValue Invoice/Credit updates. Thus, you would have one or two INVITEMs, perhaps called INVOICE and CREDIT.

A blessing with importing IIF files into QB is if the record already exists, you won't get a warning message nor will it overwrite. Thus the second and consequent sessions will receive these INVITEMs but not complain if they are already there. Likewise for the CUST file.

Coming from a MultiValue mentality, QB does have an issue. It uses the CUST.NAME field as its unique primary key. This runs contrary to a separate customer number/code. Thus, in both cases, the CUST.NAME field exported is "123456-Chicago Gasket Company". QB does have a separate Name and Address area but many of the customer views simply show this CUST.NAME field so the code alone is not helpful nor is the name that guaranteed to be unique.

Sidebar — should the customer name change on the MultiValue system, you could end up with two customer records in QB for the same account. QB does have a 'merge customer' facility that easily fixes this. It is probably easier to import the redundant customer record instead of maintaining what you sent to QB from MultiValue. The most recent name is probably the one they will merge into anyway.

For brevity, I will show the left-hand portion of an IIF file that exports an INVITEM, a CUST record and an invoice TRANSaction (fig. 1). It is classic CSV format with spaces added for clarity. I tend to use double quotes for all CSV rows whether necessary or not.

001 !INVITEM, NAME, INVITEMTYPE, DESC, ACCNT, PRICE
002 INVITEM, CHARGES, SERV, Invoiced Sale, 100.00
003 ENDINVITEM
004 !CUST, NAME, BADDR1, BPHONE1, COMPANYNAME
005 CUST,"1234-GASKET CO", "123 MAIN ST", "555-5555", "GASKET CO"
006 CUST,"4545-JONES CO","456 ELM ST","666-6666","JONES CO"
007 CUST,"1111-SMITH CO","999 OAK ST","888-8888","SMITH CO"
008 ENDCUST
009 !TRNS, TRNSTYPE,DATE,ACCNT, NAME, AMOUNT, DOCNUM
010 !SPL, TRNSTYPE, DATE, ACCNT, NAME, AMOUNT, DOCNUM
011 "ENDTRNS"
012 "TRNS","INVOICE","04/01/2010","AR","1234-GASKET CO" ", "200.00", "B123"
013 "SPL","INVOICE","04/01/2010","Sales Income","","-200","B123"
014 ENDTRNS

Fig. 1

Lines that start with an exclamation mark define the format of those records starting with the table name. !TRNS defines the TRNS table fields being imported. TRNS is the data.

Line 1 defines the INVITEM records. Line 2 is a data record, line 3 ends the INVITEMS.

Line 4 defines the CUST records, lines 5, 6 & 7 are data records, line 8 ends the CUST.

Line 9 defines the generic TRNS rows with the TRNSTYPE column.

Line 10 defines each data line in each transaction. It starts out looking similar to the !TRNS row but it contains the quantity of each of the INVITEMs "sold." For regular A/R usage, it could be just one line stating "Invoiced Sale". You could replicate the entire contents of the existing sales order.

Line 11 ends the TRANS header.

Line 12 matches the definitions on line 9. Line 13 matches the definitions on line 10.

Line 14 ends the TRANS detail row.

If you note, this IIF example has the INVITEM defined first, then the inventory item itself. Then the CUST table is defined followed by all the customers. Finally the TRANS table is defined then followed by all the transactions.

You could put all of the definitions in the first few rows. Then the following rows would be the data. Each data row follows the definition of the first cell in each row. This sequence would more logically match the READNEXT flow of processing MultiValue records instead of accumulating the INVITEM, CUST and TRANS tables separately to append to the larger IIF file.

When importing, use test companies in QB. Otherwise you will have to go through the existing company and delete any errant records. This cannot be emphasized enough.

While we may not be consulted about the company's decision to move the Accounting from MultiValue to QB, we should embrace the opportunity to bridge between the two. As mentioned earlier, accounting software borders on being a commodity. The real intellectual portion of the company's system is in the production and sales/order modules which will probably remain as MultiValue for quite a long time.