Hi,

I have tried multiple methods to write these sales receipts from FileMaker Pro.  For several of these methods, it works if I write the statements consecutively using the VB Demo application, but not if I write those exact same statements programmatically from FileMaker Pro.

-If I write several 'salesreceiptline' insert statements with FQSaveToCache = 1, followed by a final 'salesreceipt' insert statement, FileMaker throws me a generic-looking ODBC error that says something to the effect of: [QODBC] not supported.  If I execute these same statements through the VB Demo application, in exactly the same order, the sales receipt comes through just fine.

-If I write all of the insert statements on 'salesreceiptline', only the final statement with FQSaveToCache=0 is actually written into Quickbooks--the preceding statements with FQSaveToCache are just completely ignored.  Again, if I execute these statements through VB Demo, it works fine.

-I tried the sp_batchstart . . . sp_batchupdate method and the first sp_batchstart command initiates the driver but immediately kills the FileMaker Pro 8 client and I have to kill the whole program

-I read online about using a Union All statement to write multiple rows of data in the same SQL statement--which might work in this case, since it seems that FileMaker is somehow managing to kill the cached statements every time it executes a new statement--but I read on the forum that Union won't be supported until next version.

I did read on one of the posted topics about a quirk in Access that attempts to select on the just-executed insert statement and will fail--does the behavior I observed with FileMaker make you think that it works in a similar way?  And that post said that a work-around was to do an Append or Pass-through query--what exactly does that mean so that I can also try that workaround in FileMaker Pro?

Thanks for your help.

FileMaker Pro connects to and disconnects from QODBC after running every SQL statement, so it's not possible to do multiple Sales Receipt lines using our standard FQSaveToCache method. You will have to use Plan B, which is to create a single line Sales Receipt and add additional lines to it afterwards as per the following example.

This creates a new one line SalesReceipt:

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID", "FQSaveToCache") VALUES ('470001-1071525403', '1', '250000-933272656', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 0)

We can then add two new lines to the SalesReceipt by using the TxnID of the SalesReceipt we just created. The TxnID is found by searching the SalesReceiptLine table for the SalesReceipt by doing:

select TxnID, TxnDate, RefNumber, CustomerRefFullName from SalesReceiptLine
where CustomerRefFullName='Lamb, Brad:Room Addition'  and RefNumber = '1'

Where 1 is the RefNumber for the Sales Receipt we just created.

This adds two new lines to the existing SalesReceipt:

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('5C60-1197756901', '250000-933272656', 'Building permit 2', 2.00000, 2.00, '20000-999022286')

[Well it's supposed to work! - This is a bug in v6.00.00.155 but now fixed in v6.00.00.172 !!!- Administrator]

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('5C60-1197756901', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286')

We now have the following Sales Receipt with three lines in QuickBooks:

See: How do I create a Sales Receipt using QODBC?  for more.

Tom--thanks for your post.

When I tried the method you recommended, I got an error which said QODBC field not allowed in insert.

I then did a 'sp_columns salesreceiptline' and the TxnID field looks like it is not insertable, i.e., the Insertable column shows a 0.  I gather this means that I can't write a TxnID directly into the SalesReceiptLine table, which means I probably can't use this method to write a multi-line sales receipt, right?

Is there any way to string together a bunch of insert statements so that they execute in a batch--other than using the sp_batchstart...sp_batchupdate (which causes my FileMaker to crash?)

Or any other method that I can use?

Thanks.

I did the example off the top of my head near midnight, but on testing the theory today there's a bug in our append procedure for SalesReceipts. I've reported the issue to the Product Engineer.

2006-06-01 15:25:19 QODBC Ver:  6.00.00.155 ********************************************
IsAService: False
SQL Statement: INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID",
"SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount",
"SalesReceiptLineSalesTaxCodeRefListID") VALUES ('5C60-1197756901',
'250000-933272656', 'Buildin
g permit 2', 2.00000, 2.00, '20000-999022286')
Error parsing complete XML return string (8)
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="5.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeNone">
        <SalesReceiptModRq requestID = "1">
            <SalesReceiptMod>
                <TxnID>5C60-1197756901</TxnID>
                <EditSequence>1197756901</EditSequence>
                <SalesReceiptLineMod>
                    <TxnLineID>5C62-1197756901</TxnLineID>
                </SalesReceiptLineMod>
                <SalesReceiptLineMod>
                    <ItemRef>
                        <ListID>250000-933272656</ListID>
                    </ItemRef>
                    <Desc>Building permit 2</Desc>
                    <Amount>2.00</Amount>
                    <SalesTaxCodeRef>
                        <ListID>20000-999022286</ListID>
                    </SalesTaxCodeRef>
                </SalesReceiptLineMod>
            </SalesReceiptMod>
        </SalesReceiptModRq>
    </QBXMLMsgsRq>
</QBXML>

20060601.155210 I 3376 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3764), ret = 0.
20060601.155210 I 3376 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 3764).
20060601.155210 E 3376 XercesSAXErrorHandler Element 'ItemRef' is not valid for content model: '(TxnLineID,ItemRef?,Desc?,Quantity?,((Rate|RatePercent|PriceLevelRef))?,ClassRef?,Amount?,ServiceDate?,SalesTaxCodeRef?,OverrideItemAccountRef?)' -- error at line 7, column 367 in XML data.
20060601.155210 I 3376 MsgSetHandler Parser error, finishing.
20060601.155210 I 3376 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3764), ret = 1.
20060601.155210 E 3764 RequestProcessor Process Request error = 80042500, There was an error when parsing the provided XML file.

Tom,

Thank you for following up on this, I really appreciate it.

Would you mind letting me know what the Product Engineer says in terms of fixing the append procedure and any chance that this fix might be included in a not-too-distant update?

Thanks again.

I have just retested the following sequence of SQL Statements using QODBC v7.00.00.194 and QuickBooks 2007 Premier USA edition with the sample Larry's Landscaping & Garden Supply company file:

INSERT INTO "SalesReceiptLine" ("CustomerRefListID", "RefNumber",
"SalesReceiptLineItemRefListID", "SalesReceiptLineDesc",
"SalesReceiptLineRate", "SalesReceiptLineAmount",
"SalesReceiptLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('400000-1071506196', '111', '120000-934380927',
'Building permit 1', 1.00000, 1.00, '20000-999021789', 0)

sp_lastinsertid SalesReceiptLine
Result: 22C5-1197757804

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID",
"SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount",
"SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('22C5-1197757804', '120000-934380927', 'Building permit 2',
2.00000, 2.00, '20000-999021789')

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID",
"SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount",
"SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('22C5-1197757804', '120000-934380927', 'Building permit 3',
3.00000, 3.00, '20000-999021789')

and the following Sales Receipt was generated without any errors:

I also retested QODBC v7.00.00.194 with QuickBooks 2006 and the original set of SQL statements still functioned correctly without errors.

Gotcha.  Well, there's still something going on with my solution that changed between v. 6 and v. 7.  It chokes when it tries to write the shipping line item, but I found out it works on regular line items.  Here is the error message I get from 'Review QODBC Messages':

2006-10-15 23:49:57 QODBC Ver:  7.00.00.194 *********************************************************************************************************************
IsAService: False
SQL Statement: insert into "salesreceiptline" ("TxnID", "TemplateRefListID", "DepositToAccountRefListID", "SalesReceiptLineItemRefListID", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID") values ('124C1-1160977747','50000-1
091137929','540000-1091137930','2D0000-1099490098',9.79,9.79,'50000-1114101743')
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 = "includeNone">
        <SalesReceiptModRq requestID = "1">
            <SalesReceiptMod>
                <TxnID>124C1-1160977747</TxnID>
                <EditSequence>1160977792</EditSequence>
                <SalesReceiptLineMod>
                    <TxnLineID>124C3-1160977747</TxnLineID>
                </SalesReceiptLineMod>
                <SalesReceiptLineMod>
                    <TxnLineID>124C5-1160977747</TxnLineID>
                </SalesReceiptLineMod>
                <TemplateRef>
                    <ListID>50000-1091137929</ListID>
                </TemplateRef>
                <DepositToAccountRef>
                    <ListID>540000-1091137930</ListID>
                </DepositToAccountRef>
                <SalesReceiptLineMod>
                    <TxnLineID>-1</TxnLineID>
                    <ItemRef>
                        <ListID>2D0000-1099490098</ListID>
                    </ItemRef>
                    <Amount>9.79</Amount>
                    <SalesTaxCodeRef>
                        <ListID>50000-1114101743</ListID>
                    </SalesTaxCodeRef>
                </SalesReceiptLineMod>
            </SalesReceiptMod>
        </SalesReceiptModRq>
    </QBXMLMsgsRq>
</QBXML>

Any thoughts?  Thanks.