|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
|
| Invoice Numbering and ARAccount selection when inserting invoices |
| Author |
Message |
|
|
| Posted : 2008-11-10 14:04:31 |
Hi.
I'm trying to solve the invoice numbering issue - QuickBooks numbers invoices in a sequence ignoring whatever RefNumber I include in the query.
When I discovered that invoice numbering is driven by the AR Account (i.e. every AR account keeps its own sequence) I tried to use a new AR for imported invoices but for some reason my invoices are assigned to the old AR...
An example query:
INSERT INTO InvoiceLine (InvoiceLineLinkToTxnTxnID,InvoiceLineLinkToTxnTxnLineID,FQSaveToCache) Values ('22213-1226283615','22215-1226283615',1)
INSERT INTO InvoiceLine (InvoiceLineLinkToTxnTxnID,InvoiceLineLinkToTxnTxnLineID,FQSaveToCache) Values ('22213-1226283615','22216-1226283615',1)
INSERT INTO InvoiceLine (InvoiceLineLinkToTxnTxnID,InvoiceLineLinkToTxnTxnLineID,FQSaveToCache) Values ('22213-1226283615','22217-1226283615',1)
INSERT INTO Invoice (CustomerRefListID,CustomerRefFullName,ARAccountRefListID,TemplateRefListID,TxnDate,RefNumber,BillAddressAddr1,
BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressCountry,ShipDate,Memo,IsToBePrinted) Values ('8000048C-1225656620','ZealdWebFlo2Cash0811','800000DD-1226014601','80000021-1221085528',{d'2008-11-10'},'ORD0012775W',
'V. Kelly','L14','175 The Terrace','Wellington 6011','04 4967209',{d'2008-11-13'},'0811100215239288 (Flo2Cash)',0)
The result of the above using SELECT * FROM Invoice WHERE RefNumber = '10312' in VBDemo (RefNumber found through QBs UI)
1 22219-1226283624 2008-11-10 03:20:24.000 2008-11-10 03:20:24.000 1226283624 16454 8000048C-1225656620 ZealdWebFlo2Cash0811 80000-1154528786 Accounts Receivable 80000021-1221085528 Custom S.O. Tax Invoice 2008-11-10 All 10312 V. Kelly L14 175 The Terrace Wellington 6011 04 4967209 ANZ NationalBank Ltd; V. Kelly L14 175 The Terrace Wellington 6011 04 4967209 0 0 40000-1154528851 Due on receipt 2008-11-10 8000001B-1224725676 Z 2008-11-10 373.33 12.5 46.67 ORD0012775 Delivery date - 2008-11-13. Flo2Cash reference=0811100215239288 0 0 80000002-1216860795 GST% 80000002-1216860795 GST%
Can someone please help out with this one?.. I'm running out of ideas. |
|
|
|
|
|
| Posted : 2008-11-10 14:16:04 |
Sorry, when you create a linked Sales Order to Invoice, INVOICE, the RefNumber is overridden by QuickBooks using its auto numbering system which is based on the last RefNumber created plus one. If you want to keep your RefNumber then you will need to update the RefNumber afterwards like this:
UPDATE Invoice SET RefNumber = 'ORD0012775' where TxnID = '22219-1226283624'
Note: Your RefNumber should always end with a number. |
|
|
|
|
|
| Posted : 2008-11-10 14:19:05 |
Thanks Tom, that will work, I should have thought of that :)
Impressed by the speedy reply too.
But what about the AR account? Why doesn't that get through correctly? |
|
|
|
|
|
| Posted : 2008-11-10 14:44:50 |
Oh, well when you create a linked Sales Order to Invoice, INVOICE, everything from the Sales Order gets transferred and QuickBooks uses the default Account Receivable account. The trick would be to also update the default ARAccountRefListID used by QuickBooks if its different (or needs to be different) too:-
UPDATE Invoice SET RefNumber = 'ORD0012775', ARAccountRefListID = '800000DD-1226014601' where TxnID = '22219-1226283624'
See: How do I convert a Sales Order into an Invoice? for more. |
|
|
|
|
Copyright (c) 2006-2009
Data Access Worldwide,
Australia. This FREE self help forum website
was copied by FLEXquarters.com Limited, Hong Kong to: http://archive.qodbc.com
/forum50f5.html
without our
permission. |
The QDeveloper Forum is
dedicated to the FREE professional exchange of
information. QODBC and FlexODBC users can post support
queries, report product bugs, or simply search topics
for help. Data Access Worldwide - Asia Pacific normal office
business hours are 9am to 5pm
week
days
Australian EST with the exception of Public Holidays.
ABN: 86 544 223 459
PO Box 873,
Boronia VIC 3155 Australia
Tel: 03 9761 3644 Intl: +613 9761 3644 |
