Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : Invoice Numbering and ARAccount selection when inserting invoicesSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 Invoice Numbering and ARAccount selection when inserting invoices 
 Author   Message 
  Dmitry S 
  
 Group: Members 
 Posts: 13 
 Joined: 2008-09-18 
 Profile
 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. 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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.

 

  Top 
  Dmitry S 
  
 Group: Members 
 Posts: 13 
 Joined: 2008-09-18 
 Profile
 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? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 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.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to