|
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
|
|
| InvoiceLine.Tax1Total |
| Author |
Message |
|
|
| Posted : 2008-10-17 15:55:01 |
Hi,
Using QODBC v6 we were able to supply values for the Tax1Total field when creating lines, now I get this error (v9.00.00.253 AU/NZ):
ERROR [00000] [QODBC] Field not allowed in insert.
And I can see this in QODBCTRA.TXT:
ISAMPutData
0x0B757870 [ISAM]
0x0B77FC18 [InvoiceLine]
COLUMN: 116 [Tax1Total]
SQL_C_CHAR
30.00
ISAM_FIELD_NOT_ON_INS S0000 00000 [QODBC] Field not allowed in insert.
ISAMGetErrorMessage
0x0B757870 [ISAM]
S0000 00000 [QODBC] Field not allowed in insert.
Removing Tax1Total from the insert statement solves the problem, however it is very important for us to supply this value so our quickbooks data will match exactly our CRM system (otherwise we get rounding discrepancies).
What is the new way to set the tax amount on a line item?
Regards
Michael |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-17 23:37:16 |
| Tax1Total should only be used in the final INSERT Invoice header insert (for pre-QuickBooks 2008/09 only). |
|
|
|
|
|
| Posted : 2008-10-20 10:41:49 |
Hi,
I am trying to split my statement into an Insert InvoiceLine and Insert Invoice, as follows:
INSERT INTO InvoiceLine
(CustomerRefListID,
InvoiceLineItemRefListID,
InvoiceLineDesc,
InvoiceLineRate,
InvoiceLineTaxCodeRefListID,
FQSaveToCache
) VALUES (
'1840000-1112685502',
'1B0000-1151902315',
'23007',
95.9727272727273,
'8000000A-1224053897',
1
)
INSERT INTO Invoice
(CustomerRefListID,
RefNumber,
Memo,
PONumber,
ARAccountRefListID,
Tax1Total,
TxnDate
) VALUES (
'1840000-1112685502',
'138622',
'SCRUBBED',
'ETC',
'430000-1110939704',
9.6,
{d'2008-10-15'},
)
The invoice line statement appears to work but the Invoice fails with this error:
ERROR [42000] [QODBC] Expected lexical element not found: <identifier>
I can see that some people have this issue with UPDATE statements, but what does this relate to when inserting?
I can't see anything useful in the trace log.
Regards |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-20 13:09:43 |
There's an extra comma after the date that shouldn't be there: {d'2008-10-15'},
An "Expected lexical element not found:" simply means there's a syntax error. Also, 95.9727272727273 might need to be 95.9727 |
|
|
|
|
|
| Posted : 2008-10-20 13:29:17 |
Great, thanks - I'll remember that.
Back to square 1 now, this is what I get in QODBCTRA.txt after fixing the syntax error:
ISAMPutData
0x0022FF10 [ISAM]
0x0B88C140 [Invoice]
COLUMN: 81 [Tax1Total]
SQL_C_CHAR
9.60
ISAM_FIELD_NOT_ON_INS S0000 00000 [QODBC] Field not allowed in insert.
Looking at the table references for InvoiceLine and Invoice (http://doc.qodbc.com/qodbc/20/tables/table_detail_oz0371.html?details_id=38&tn_oz=TRUE, http://doc.qodbc.com/qodbc/20/tables/table_detail_oz185f.html?details_id=37&tn_oz=TRUE) I would think tax1total is insertable for both. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-20 14:55:20 |
|
|
|
|
|
| Posted : 2008-10-20 15:36:03 |
Hi,
The sp_columns output was a bit big for a screen shot, please grab them from here:
http://helpdesk.mgram.com.au/Invoice.csv
http://helpdesk.mgram.com.au/InvoiceLine.csv
This seems to contain the SQL, not sure if you were after QODBCLOG.TXT but it doesn't contain the latest insert statements anyway. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-10-20 16:14:28 |
Thanks for that. Ok, with QuickBooks 2008/09, Tax1Total functionality has now been moved to InvoiceLineTaxAmount, try:
INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('1840000-1112685502', '1B0000-1151902315', '23007',
96, 9.6,'8000000A-1224053897', 0) |
|
|
|
|
|
| Posted : 2008-10-21 09:10:03 |
This works.
Thanks for your assistance. |
|
|
|
|