IIF import - invalid transaction

[Alban Gerome]

Hi all,

First the background information, we sell cars and for every car we
sell, there are 3 transactions we need to import into QuickBooks 2003
Professional, 2 invoices and 1 bill.

So far, I have managed to generate IIF files for each of these 3
transactions. For testing purposes, I have created 3 companies for
each transaction I want to import, they all import fine but I want to
import all transactions in one single company and that's where the
trouble starts.

If I group only 1 sale with 2 invoices and 1 bill in one single IIF
file, it imports fine but with a second sale in that IIF file, 4
invoices and 2 bills, I have the pesky "invalid transaction" error.

So far the only way to import the sales transactions is to have 1 sale
per IIF file, while I want to be able to import at least 50 sales per
IIF file, any clues ?

Cheers,

Alban

[Alban Gerome]

Hi all,

Am I really the only one who has had this problem ?

Invoice and Bill transactions import fine when imported separately but
if combined into one single file, the import fails with the invalid
transaction errors.

Thanks,

Alban

[Karl]

alban...@yahoo.fr (Alban Gerome) wrote in message news:<c925e7e3.0405...@posting.google.com>...

Hi Alban,

I'm having similar problems, if not the same. I can't even get my
invoices to import so maybe you can help me while I try to help you.
If you post your .iif file samples of what is working and what is not
working, I'll see what I can see for you. An extra set of eyes almost
always helps.:) And a look at your working sample will probably help
me solve the problem I am having.

Here is what I am using with no success, in case you have any insight
for me:

!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM
!SPL,SPLID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM
!ENDTRNS
TRNS,92839,INVOICE,5/18/2004,"Mall Purchase","Commission Monster:Easy
Flowers",10,1364-92839
SPL,92839,INVOICE,5/18/2004,"Online Item","Commission Monster:Easy
Flowers",10,1364-92839
!ENDTRNS

I've tried it with ENDTRNS(as per the QB documentation) after the SPL
line and it tells me "INVALID TRANSACTION", so I removed it. In the
format as posted above, it says that the data is imported
successfully, but I can't find the invoice in QuickBooks. Also, so you
know if the SPL line is necessary even if there is only one item on
the invoice?

Thanks,
Karl Kohrt
The DHS Club, Inc.
IT Services
k.k...@dhs-club.com

[John]

You need the ENDTRNS because that triggers QuickBooks to record the
transaction.
Below, both of your Amounts are the same. They need to add up to 0.00.

Try using this excel add-in to create a valid IIF file:
http://www.bigredconsulting.com/AboutCreator.htm

At the least, you can use it's output as an example.

"Karl" <k.k...@dhs-club.com> wrote in message
news:1dc5af84.04052...@posting.google.com...

[Karl]

John,

I gave your suggestions a try and I still get "Error on Line 6: Can't
record invalid transaction". Line 6 corresponds to the ENDTRNS line of
the file as shown below.

!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,TERMS

!SPL,SPLID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM
!ENDTRNS
TRNS,92839,INVOICE,5/18/2004,"Mall Purchase","Commission Monster:Easy

Flowers",10,1364-92839,"Monthly"

SPL,92839,INVOICE,5/18/2004,"Online Item","Commission Monster:Easy

Flowers",-10,1364-92839
ENDTRNS
!ENDTRNS

After I click through the error dialog box, I see an "importing" box,
as if the importing is taking place even with the error. But then I
don't find the invoice in QB.

Maybe I have to have something set up in QB prior to importing the
transaction? I don't really believe this though, because I think QB
will create accounts, names, and terms if they don't already exist,
right? I'm creating these files using a VBA script in MSAccess and am
then editing them in Notepad during these tests. Is it possible that
I've got some bizarre control character in there? I dont' believe this
either because I do thousands of "CHECKS" transactions every month
along with "VEND" type address files using the same methods. These are
the first INVOICE transactions however. Maybe a reserved word is
different than the actual documentation?

Grasping for straws here.:>)

Thanks,
Karl

"John" <jo...@john.com> wrote in message news:<fdorc.70786$wg2....@newssvr25.news.prodigy.com>...

[Karl]

I've found the solution thanks to the help file that came with
www.bigredconsulting.com 's IIF Creator tool. ( A BIG thanks to them
and John). To quote the help file, "...the account on the first line
of an invoice cannot be a bank account, but must be an accounts
receivable account." I was using an expense account, but have now
changed the "Mall Purchase" category to an "Accounts Receivable"
category. This solves the problem that I was having, though I'll have
to check with our accountant to see if this will give them what they
want. And in the end, that's all that matters, right?

Here's the working file with the only change being that mentioned
above.

!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,TERMS
!SPL,SPLID,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM
!ENDTRNS
TRNS,92839,INVOICE,5/18/2004,"Accounts Receivable:Mall
Purchase","Commission Monster:Easy Flowers",10,1364-92839,"Monthly"

SPL,92839,INVOICE,5/18/2004,"Online Item","Commission Monster:Easy

Flowers",-10,1364-92839
ENDTRNS
!ENDTRNS

"John" <jo...@john.com> wrote in message news:<fdorc.70786$wg2....@newssvr25.news.prodigy.com>...

[Alban Gerome]

Hi all,

First of all, thanks for your feedback
Maybe we would all talk about the same thing if I gave you an IIF
sample with 2 vehicle sales, it's in tab-delimited format

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR TOPRINT ADDR1 ADDR2 ADDR3 ADDR4 ADDR5 DUEDATE TERMS PAID PAYMETH SHIPVIA SHIPDATE OTHER1 REP FOB PONUM INVTITLE INVMEMO SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 VATCODE VATRATE VATAMOUNT PAYITEM YEARTODATE EARNBASE EXTRA
!SPL SPLID TRNSTYPE DATE ACCNT NAME CLASS AMOUNT DOCNUM MEMO CLEAR QNTY PRICE INVITEM PAYMETH VALADJ REIMPEXP VATCODE VATRATE VATAMOUNT SERVICEDATE OTHER2 OTHER3 PAYITEM YEARTODATE EARNBASE EXTRA
!ENDTRNS
TRNS 2 INVOICE 05/11/2002 Accounts Receivable Client
A 4811.62 1 Purchase Invoice N Y Client A Addr1 Client A Addr2 Client
A Addr3 ClientA Addr4 Client A Addr5 05/11/2002 Due on
Receipt N Sale 13/13 0 0
SPL 3 INVOICE 05/11/2002 Vehicle sales -4000 Sale info for Vehicle
A N -1 4000 NOTHING S 17.50% -700 0 0
SPL 4 INVOICE 05/11/2002 Buyers Fees -95 Buyers Charge
: N -1 95 Buyers Charge NOTHING S 17.50% -16.62 0 0
ENDTRNS
TRNS 7 INVOICE 05/11/2002 Accounts Receivable Our
company 117.5 2 Vendor Invoice N Y Supplier A Addr1 Supplier A
Addr2 Supplier A Addr3 Supplier A Addr4 Supplier A
Addr5 05/11/2002 Due on Receipt Sale 13/13 0 0
SPL 8 INVOICE 05/11/2002 Sales commissions -100 Sale info for
Vehicle A N -1 100 NOTHING S 17.50% -17.5 0 0
ENDTRNS
TRNS 11 BILL 05/11/2002 Accounts Payable Our company -4000 3 Self
Invoice N Y Our Addr1 Our Addr2 Our Addr3 Our Addr4 Our
Addr5 05/11/2002 Due on Receipt Sale 13/13
SPL 12 BILL 05/11/2002 Vehicle purchases 4000 Sale info for Vehicle
A N NOTHING 0/0/0
ENDTRNS
TRNS 15 INVOICE 06/11/2002 Accounts Receivable Client
B 8011.62 4 Purchase Invoice N Y Client B Addr1 ClientB Addr2 Client
B Addr3 Client B Addr4 Client B Addr5 06/11/2002 Due on
Receipt N Sale 58/107 0 0
SPL 16 INVOICE 06/11/2002 Vehicle sales -6723.4 Sale info for
Vehicle B N -1 6723.4 NOTHING S 17.50% -1176.6 0 0
SPL 17 INVOICE 06/11/2002 Buyers Fees -95 Buyers Charge
: N -1 95 Buyers Charge NOTHING S 17.50% -16.62 0 0
ENDTRNS
TRNS 20 INVOICE 06/11/2002 Accounts Receivable Our
company 117.5 5 Vendor Invoice N Y Supplier B Addr1 Supplier B
Addr2 Supplier B Addr3 Supplier B Addr4 Supplier B
Addr5 06/11/2002 Due on Receipt Sale 58/107 0 0
SPL 21 INVOICE 06/11/2002 Sales commissions -100 Sale info for
Vehicle B N -1 100 NOTHING S 17.50% -17.5 0 0
ENDTRNS
TRNS 24 BILL 06/11/2002 Accounts Payable Our company -6723.4 6 Self
Invoice N Y Our Addr1 Our Addr2 Our Addr3 Our Addr4 Our
Addr5 06/11/2002 Due on Receipt Sale 58/107
SPL 25 BILL 06/11/2002 Vehicle purchases 6723.4 Sale info for
Vehicle B N NOTHING 0/0/0
ENDTRNS

It's probably more readable if you paste and copy it in the Notepad,
save and open it in Excel.

The first sale made of 2 invoices and 1 bill imports fine but not any
subsequent sale for as long as they are in the same file. I can import
sales but 1 sale at a time because QB doesn't let me import more than
1 sale per file.

Does anyone have that problem ? Any fix or ideas ?

Cheers,

Alban

[John]

I've no idea why subsequent sales will not import. I use a third party tool
to create IIF files fro import. You may be able to use it, or at least to
use it's output to figure out how your file is different. See:
http://www.bigredconsulting.com/AboutCreator.htm

"Alban Gerome" <alban...@yahoo.fr> wrote in message
news:c925e7e3.04052...@posting.google.com...

[Alban Gerome]

Hi Karl,

I have sent you my sample IIF file with 2 sales

Thanks,

Alban

[John Baker]

Since this is an Invloice, you need to make certain taht the accoutn you put the TRNS
record is a Account Receivable accoount, and is unique.