What would be the best way to create a Bill with QODBC which has tax applied to only a 'portion' of the bill amount.

For example the pretax bill amount is $85 and the tax @ 5% is based on $20 of the $85. Tax in this case would be $1 for a total Vendor Bill amount of $86. 

Thanks again Tom for your reply....I really appreciate your assistance. I can set up the majority of our odd ball tax situations the way you suggest.

I do have 'one' more question which is really odd ball. I'm looking at a fairly complex bill that is the result of cross border shopping from the US into Canada for a preowned vehicle.

The total Invoice amount is $1390.35 of which $1041.85 is GST Tax and the remaining $348.50 is a charge amount from the Trade Broker (Vendor) who handled the transaction. Really I'm not making this up...

Would you have an idea on what would be the best way to approach this one?

Thx...Don

Hmm...so much for one more question on this topic.

I'm using Quickbooks 2008 Canadian Version and now struggling with your first suggestion:

If I use the following sql code I can put in a one line item bill and it automatically calculates taxes (I assume it must get the correct tax code from the Vendor table)

sSQL = "INSERT INTO BillItemLine (VendorRefListID, RefNumber, ItemLineItemRefListID, ItemLineDesc, " & _
                    "ItemLineQuantity, ItemLineCost, Memo, TxnDate, TermsRefListID) " & _
                    "VALUES ('" & strVendorRefListID & "', '" & strRefNum & "', '" & strItemLineItemRefListID & "', " & _
                    "'" & strItemLineDesc & "', 1, " & dblItemLineCost & ", 'Bill created by xxx software', {d '" & strTxnDate & "'},  '" & strTermRefListID & "')"

In Quickbooks 2007 we successfully used the following sql code for a line item without taxes (where we would provide the blank tax code RefListID)

 sSQL = "INSERT INTO BillItemLine (VendorRefListID, RefNumber, ItemLineItemRefListID, ItemLineDesc, " & _
                  "ItemLineQuantity, ItemLineCost, Memo, ItemLineTaxCodeRefListID, Tax1Total, TxnDate, TermsRefListID) " & _
                   "VALUES ('" & strVendorRefListID & "', '" & strRefNum & "', '" & strItemLineItemRefListID & "', " & _
                   "'" & strItemLineDesc & "', 1, " & dblItemLineCost & ", 'Bill created by xxx software', " & _
                    "'" & strItemLineTaxCodeRefListID & "', 0, {d '" & strTxnDate & "'}, '" & strTermRefListID & "')"

In Quickbook 2008 the above code does not work....again we have checked the values

Here is the bottom part of the Trace Log:

2008-07-30 11:26:24 QODBC Ver:  8.00.00.242 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO BillItemLine (VendorRefListID, RefNumber, ItemLineItemRefListID, ItemLineDesc, ItemLineQuantity, ItemLineCost, Memo, ItemLineTaxCodeRefListID, Tax1Total, TxnDate, TermsRefListID) VALUES ('800000FF-1217442310', '8000', '8000001C-1217442324
', '2007 BMW 235 - Base Invoice', 1, 10000, 'Bill created by xxx software, '60000-1078291348', 0, {d '2008-07-27'}, '10000-1078292074')
Error parsing complete XML return string (8)
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="6.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <BillAddRq requestID = "1">
            <BillAdd defMacro = "TxnID:9361C369-72DE-40F1-92DD-E4F60221C105">
                <VendorRef>
                    <ListID>800000FF-1217442310</ListID>
                </VendorRef>
                <TxnDate>2008-07-27</TxnDate>
                <RefNumber>8000</RefNumber>
                <TermsRef>
                    <ListID>10000-1078292074</ListID>
                </TermsRef>
                <Memo>Bill created by SureFire</Memo>
                <ItemLineAdd>
                    <ItemRef>
                        <ListID>8000001C-1217442324</ListID>
                    </ItemRef>
                    <Desc>2007 BMW 235 - Base Invoice</Desc>
                    <Quantity>1.00000</Quantity>
                    <Cost>10000.00000</Cost>
                    <TaxCodeRef>
                        <ListID>60000-1078291348</ListID>
                    </TaxCodeRef>
                </ItemLineAdd>
                <Tax1Total>0.00</Tax1Total>
            </BillAdd>
        </BillAddRq>
    </QBXMLMsgsRq>
</QBXML>
    ISAM_ERR_IN_COMP_SQL S0000 00000 [QODBC] Error parsing complete XML return string.
ISAMGetErrorMessage
    0x0BF056A8 [ISAM]
    S0000 00000 [QODBC] Error parsing complete XML return string.
ISAMClearUpdateRecord
    0x0BF056A8 [ISAM]
    0x0C08DB38 [BillItemLine]
    NO_ISAM_ERR 00000 00000
ISAMCloseTable
    0x0BF056A8 [ISAM]
    0x0C08DB38 [BillItemLine]
    NO_ISAM_ERR 00000 00000
ISAMClose
    0x0BF056A8 [ISAM]
    NO_ISAM_ERR 00000 00000
*********************************************************************************************************************

Would it be possible for you to provide an example of what Insert statements we would use to add in a line for a non-taxable item and a line for a taxable item?

Thx...Don

The Canadian release of QuickBooks 2008 now uses the USA Intuit qbXML SDK v6.1, so everything is back to being called SalesTaxCode instead of TaxCode that's in Canadian QuickBooks 2003, 2004, 2005, 2006 and 2007. I do have an Australian example of using a TaxCode in How do I create Bills? 

I would need to see the column names using:-

sp_columns BillItemLine

in your Canadian version of QuickBooks 2008. Hopefully you have a ItemLineSalesTaxCodeRefListID column.

Hi Tom, some of the fields have changed to SalesTaxCode.... vs the older TaxCode but not in the BillItemLine table. The Canadian version of Quickbooks 2008 still uses ItemLineTaxCodeRefListID in the BillItemLine table.

I've tried a few of the Australian examples but can't get them to work either.

What is the best way of sending you the results of sp_columns BillItemLine ?

Don

Hi Tom here are the 3 screen dumps

1 of 3

2 of 3

3 of 3...Thx.....Don

Thanks Tom..Sorry I'm a little confused. Is the fix for this out or do I have to wait for it to be released?

I understood that I am using the latest QODBC driver.

Don

Hi Tom, where can we go to find out the expected timeframe for fixing QBCA001356: Sales tax items cannot be added using SDK ?

From what I see on the forum this issue has been out there for a least four months...

Thx.

Don Garry

Hi Tom, thanks again for your reply

Pardon my lack of knowledge but what is 'R4P' ?

And if the QODBC still needs fixing, where would we find out when it is expected to be fixed?

Thx...Don

You will need to update to QODBC v9, see: QODBC v9 Released!  Tax1Total functionality has now been moved to LineTaxAmount, for example:

INSERT INTO InvoiceLine (CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc,
InvoiceLineRate, InvoiceLineTaxAmount, InvoiceLineTaxCodeRefListID, FQSaveToCache)
VALUES ('1840000-1112685502', '1B0000-1151902315', '23007',
96, 9.6,'8000000A-1224053897', 0)