Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
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. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
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? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
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. |
|
|
|
|