|
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
|
|
| Can't insert invoiceLines, then invoice using QuickBooks Online Edition? |
| Author |
Message |
|
|
| Posted : 2006-04-14 09:11:40 |
I am having a problem creating an invoice. I am using the most recent version of QODBC and connecting to QBOnline. The following are the steps I am taking:
1. Run invoiceLine insert statement:
INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, FQSaveToCache)
VALUES ('2','testCustomer', '1', 'test 1', 1)
= Successful
2. Run next invoiceLine, which should afterwards create a new invoice:
INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, FQSaveToCache)
VALUES ('2','testCustomer, '1', 'test 2', 0)
= [QODBC] Error: 3180 - An error occurred when attempting to save the Invoice in InvoiceAddRq. Quickbooks message: Something you're trying to use has been deleted. Check the fields with accounts, customers, items, vendors or employees.
I have also tried the following:
1. INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc, FQSaveToCache)
VALUES ('2','testCustomer', '1', 'test 1', 1)
= Successful
2. INSERT INTO Invoice (CustomerRefListID, CustomerRefFullName)
VALUES ('2','testCustomer')
= Same error as above
My last resort was to try to create an invoice with a single invoiceLine:
1. INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, InvoiceLineItemRefListID, InvoiceLineDesc)
VALUES ('2','testCustomer', '1', 'test 1')
= [QODBC] Error: 3180 - An error occurred when attempting to save the Invoice in InvoiceAddRq. QuickBooks message: You must specify a date.
Any help would be much appreciated.
-Noah |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-14 09:35:32 |
When using QODBC with the QuickBooks Online Edition (QBOE), Insert, Query and Update operations are supported for the following "list" tables:-
• Account
• Class
• Customer
• Employee
• ItemService
• PaymentMethod
• Terms
• Vendor
Insert and Query operations are only supported for the following "transaction" tables:-
• Bill, BillExpenseLine
• Check, CheckExpenseLine
• CreditCardCharge, CreditCardChargeExpenseLine
• CreditCardCredit, CreditCardCreditExpenseLine
• CreditMemo, CreditMemoLine
• Invoice, InvoiceLine
• JournalEntry, JournalEntryCreditLine, JournalEntryDebitLine
• ReceivePayment, ReceivePaymentLine
• SalesReceipt, SalesReceiptLine
• TimeTracking
• VendorCredit, VendorCreditExpenseLine
TxnDeleted is also supported for the transactions that were created by QODBC.
Try doing a one line invoice like this:
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "FQSaveToCache")
VALUES ('2', 'QBOE1', '1', 'test 1',
100.00000, 100.00, 0)
Where:
2 is the ListID of a existing customer (Do "Select ListID, Name From Customer" to check).
1 is the ListID of a existing ItemService (Do "Select ListID, Name From ItemService" to check).
Both the Customer and the Service Item must exist to invoice them! Change the IDs to match your QuickBooks Online Edition company file and it should work.
|
|
|
|
|
|
| Posted : 2006-04-14 10:17:14 |
Isn't the "CustomerRefFullName" field required? I thought it was based on the data layout pages on this site. Are these pages up-to-date? If not, is there anywhere I can get a list of the required fields for both the invoiceLine and Invoice tables?
Based on your example query, can't I simplify it as
INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, FQSaveToCache)
VALUES ('2', '1', 0)
Thanks for your help Tom.
-Noah |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-14 10:35:18 |
It's actually either CustomerRefListID or CustomerRefFullName
A customer can change its name in QuickBooks, but never its ID, so I tend to use IDs. We are trying to create a InvoiceLine here, so put the rest of the values in for now........ |
|
|
|
|
|
| Posted : 2006-04-14 14:41:18 |
I tried the following insert query:
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "FQSaveToCache")
VALUES ('2', 'QBOE1', '1', 'test 1',
100.00000, 100.00, 0)
Where there is a customer and itemservice with those values and I received the following message: "Error: 3180 - An error occurred when attempting to save the Invoice in InvoiceAddRq. QuickBooks message: You must specify a date." |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-14 14:56:57 |
QuickBooks will use the default date if no date is specified, but from the looks of things the Online Edition requires a date! So try this instead:-
INSERT INTO "InvoiceLine" ("CustomerRefListID", "TxnDate", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "FQSaveToCache")
VALUES ('2', {d '2006-04-13'} ,'QBOE1', '1', 'test 1',
100.00000, 100.00, 0)
|
|
|
|
|
|
| Posted : 2006-04-14 16:19:58 |
It worked! I think I tried this before, outside but I kept putting the 'd' in {d '2006-04-13'} outside the curly brace.
Appreciate the help Tom!
Thanks,
-Noah |
|
|
|
|