I am trying to import receipts of purchase orders into QuickBooks Enterprise: Manufacturing & Wholesale Edition 8.0 using QODBC v8. I am able to successfully import single receipt lines using the FQSaveToCache value = 0. However, whenever I try to import two lines for the same receipt, setting one FQSaveToCache = 1 and the other = 0, only the line with the 0 value imports. No errors are generated - the other line just disappears into the ozone.

I am using these SQL statements:
INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('2', '1-1218479746', '4-1218479746', 20, 'QB Vendor', 1)
"INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('2', '1-1218479746', '3-1218479746', 10, 'QB Vendor', 0)

If I set the FQSaveToCache value to 0 for both of them, they both import successfully but as two different receipts. How do I get them both to show up on one receipt?

Thanks

Hi Tom,

I had read "Error parsing complete XML return string - ItemReceiptItemLine" before posting my question. If I'm understanding the answer, it seems to suggest that the problem is the statements will not work if the quantity received is not the same as the qty ordered. However, I don't believe that is the solution for the following reasons:

1) If I set both lines with FQSaveToCache = 1, they both post as written even though the qty received <> qty ordered.

2) When I modify the SQL so that the quantity value equals the qty ordered, I get no change in results. The line with FQSaveToCache = 1 does not post while the line with that value = 0 does post.

The problem is I am unable to batch them with the FQSaveToCache flag. If I use the value 1 on one record it doesn't post. If I use the value 0 on  both, they both post but as two separate receipts. I want to record one receipts with two lines.

You can try:-

INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID,
ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('2', '1-1218479746', '4-1218479746',
20, 'QB Vendor', 0)

Then do:

sp_lastinsertid ItemReceiptItemLine

to get the Transaction ID (TxnID) of the new Item Receipt and then try adding a line using the TxnID like this:-

INSERT INTO ItemReceiptItemLine (TxnID, RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID,
ItemLineQuantity, VendorRefFullName) VALUES ('xxxxxxxxxxxxxx', '2', '1-1218479746', '3-1218479746',
10, 'QB Vendor')

Sorry, I meant to say

1) If I set both lines with FQSaveToCache = 0, they both post as written even though the qty received <> qty ordered.

It appears whenever I use FQSaveToCache = 1, it goes into the cache but I can't seem to get it out of there and processed.

Hi,

It doesn't really fit into the structure of my program to do one line, query sp_lastinsertid, and then do the rest of the lines. If I did it that way though, how do I know the sp_lastinsertid is really the one I want? I know that in SQL Server when you ask for @@identity you get the id of the last record inserted but there are many circumstances when it's not necessarily the record you just inserted.

Really what I want to know is why doesn't the FQSaveToCache flag work as advertised? Is there some setting in the setup I am missing or am I using it incorrectly?

Thanks again for your help

Here's one more piece of information for you. The QODBC log is empty. The SDK log is shown below. Could the messages written in red below be telling me something about the cacheing feature not being enabled? If so, how do I enable it or what version of QuickBooks do I need? And another aside, I have tried with QB in multi-user mode and i have tried it in single-user mode. My QODBC is configured to use same mode as Company.

20081113.160054 I 2248 RequestProcessor ========= Started Connection =========
20081113.160054 I 2248 RequestProcessor Request Processor, QBXMLRP2 v7.0
20081113.160054 I 2248 RequestProcessor Connection opened by app named 'FLEXquarters QODBC'
20081113.160054 I 2248 RequestProcessor Opening the file in the DoNotCare mode.
20081113.160054 I 2600 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 2248).
20081113.160054 I 2600 SpecVersion Current version of qbXML in use: 4.0
20081113.160054 I 2600 QBSDKMsgSetHandler QUERY: Host
20081113.160054 I 2600 QBSDKMsgSetHandler Request 1 completed successfully.
20081113.160054 I 2600 QBSDKMsgSetHandler QUERY: Company
20081113.160054 I 2600 QBSDKMsgSetHandler Request 2 completed successfully.
20081113.160054 I 2600 QBSDKMsgSetHandler QUERY: DataExtension Definition
20081113.160054 I 2600 QBSDKMsgSetHandler Request 3 completed successfully.
20081113.160054 I 2600 MsgSetHandler Finished.
20081113.160054 I 2600 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 2248), ret = 0.
20081113.160059 I 2600 QBSDKProcessRequest Application named 'FLEXquarters QODBC' starting requests (process 2248).
20081113.160059 I 2600 SpecVersion Current version of qbXML in use: 7.0
20081113.160059 I 2600 QBSDKMsgSetHandler ADD: Item Receipt
20081113.160100 E 2600 ItemReceiptStorage::BuildItemLineRet This feature is not enabled or not available in this version of QuickBooks. HRESULT=0x80040527
20081113.160100 E 2600 ItemReceiptStorage::BuildItemLineRet This feature is not enabled or not available in this version of QuickBooks. HRESULT=0x80040527
20081113.160100 I 2600 QBSDKMsgSetHandler Request 1 completed successfully.
20081113.160100 I 2600 MsgSetHandler Finished.
20081113.160100 I 2600 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 2248), ret = 0.
20081113.160100 I 2248 RequestProcessor Connection closed by app named 'FLEXquarters QODBC'
20081113.160100 I 2248 RequestProcessor ========== Ended Connection ==========

How do you know the sp_lastinsertid is really the one I want? A TRUE setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 or FALSE, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks.

QODBC maintains a connection for each application using the driver, and this cache is specific for each connection, so multiple applications or users of QODBC will not interfere with the cached transactions of other users. There is no set limit to the number of lines that can be cached for a single transaction, other than what QuickBooks would limit you to.

The error in red is a QuickBooks error message. As you are using QuickBooks Enterprise: Manufacturing & Wholesale Edition 8.0 you're entitled to ask Intuit why QuickBooks is refusing to process the request and let me know so I can tell other users.

Hi Tom,

Wouldn't you know it - just my stupidity! Turns out another piece of code was closing and reopening my connection in between the two statements so of course the first statement which was cached never received the instruction to go ahead and process it.

So I fixed that but now I have a different problem with the multiline invoices:

Please consider the following 4 sql statements I am trying to execute to create two invoices with 2 lines each:

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache)
VALUES ('3.1', 'B3-1226696470', 'B6-1226696470', 4, 'XYZ', 1)"

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache)
VALUES ('3.1', 'B3-1226696470', 'B5-1226696470', 100, 'XYZ', 0)"

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache)
 VALUES ('4.1', 'B7-1226696553', 'BA-1226696553', 100, 'QB Vendor', 1)"

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache)
VALUES ('4.1', 'B7-1226696553', 'B9-1226696553', 12, 'QB Vendor', 0)"

The Inserts fail completely. The QODBC log is pasted below. Something about the LinkToTxn field having an invalid value, but I am not even sending a value for that - it appears to be making something up. Any ideas how to get this to work?

Thanks!

2008-11-14 15:50:04 QODBC Ver:  8.00.00.242 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('1.5', '1-1218479746', '3-1218479746', 10, 'QB Vendor', 0)
3210 - The "LinkToTxn" field has an invalid value "TxnID=1-1218479746, TxnLineID=4-1218479746".
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="7.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <ItemReceiptAddRq requestID = "1">
            <ItemReceiptAdd defMacro = "TxnID:FBF7F77E-39F8-4804-9D0A-FD1951A10303">
                <VendorRef>
                    <FullName>QB Vendor</FullName>
                </VendorRef>
                <RefNumber>1.5</RefNumber>
                <ItemLineAdd>
                    <Quantity>20.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>1-1218479746</TxnID>
                        <TxnLineID>4-1218479746</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
                <ItemLineAdd>
                    <Quantity>20.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>1-1218479746</TxnID>
                        <TxnLineID>4-1218479746</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
                <ItemLineAdd>
                    <Quantity>10.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>1-1218479746</TxnID>
                        <TxnLineID>3-1218479746</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
            </ItemReceiptAdd>
        </ItemReceiptAddRq>
    </QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
    <QBXMLMsgsRs>
        <ItemReceiptAddRs requestID="1" statusCode="3210" statusSeverity="Error" statusMessage="The &quot;LinkToTxn&quot; field has an invalid value &quot;TxnID=1-1218479746, TxnLineID=4-1218479746&q
            uot;. " />
    </QBXMLMsgsRs>
</QBXML>

2008-11-14 16:50:25 QODBC Ver:  8.00.00.242 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('3.1', 'B3-1226696470', 'B5-1226696470', 100, 'XYZ', 0)
3210 - The "LinkToTxn" field has an invalid value "TxnID=B3-1226696470, TxnLineID=B5-1226696470".
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="7.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <ItemReceiptAddRq requestID = "1">
            <ItemReceiptAdd defMacro = "TxnID:43006412-1C4B-46A6-B0DD-6E728EB1F314">
                <VendorRef>
                    <FullName>XYZ</FullName>
                </VendorRef>
                <RefNumber>3.1</RefNumber>
                <ItemLineAdd>
                    <Quantity>4.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>B3-1226696470</TxnID>
                        <TxnLineID>B6-1226696470</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
                <ItemLineAdd>
                    <Quantity>100.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>B3-1226696470</TxnID>
                        <TxnLineID>B5-1226696470</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
            </ItemReceiptAdd>
        </ItemReceiptAddRq>
    </QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
    <QBXMLMsgsRs>
        <ItemReceiptAddRs requestID="1" statusCode="3210" statusSeverity="Error" statusMessage="The &quot;LinkToTxn&quot; field has an invalid value &quot;TxnID=B3-1226696470, TxnLineID=B5-1226696470
            &quot;. " />
    </QBXMLMsgsRs>
</QBXML>

2008-11-14 16:50:40 QODBC Ver:  8.00.00.242 *********************************************************************************************************************
IsAService: False
SQL Statement: INSERT INTO ItemReceiptItemLine (RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity, VendorRefFullName, FQSaveToCache) VALUES ('4.1', 'B7-1226696553', 'B9-1226696553', 12, 'QB Vendor', 0)
3210 - The "LinkToTxn" field has an invalid value "TxnID=B7-1226696553, TxnLineID=B9-1226696553".
Input XML:
<?xml version="1.0" encoding="ISO-8859-1"?>
<?qbxml version="7.0"?>
<QBXML>
    <QBXMLMsgsRq onError = "continueOnError" responseData = "includeAll">
        <ItemReceiptAddRq requestID = "1">
            <ItemReceiptAdd defMacro = "TxnID:51DBB9BD-E8DC-4BD7-B3E0-B282849C2275">
                <VendorRef>
                    <FullName>QB Vendor</FullName>
                </VendorRef>
                <RefNumber>4.1</RefNumber>
                <ItemLineAdd>
                    <Quantity>100.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>B7-1226696553</TxnID>
                        <TxnLineID>BA-1226696553</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
                <ItemLineAdd>
                    <Quantity>12.00000</Quantity>
                    <LinkToTxn>
                        <TxnID>B7-1226696553</TxnID>
                        <TxnLineID>B9-1226696553</TxnLineID>
                    </LinkToTxn>
                </ItemLineAdd>
            </ItemReceiptAdd>
        </ItemReceiptAddRq>
    </QBXMLMsgsRq>
</QBXML>
Output XML:
<?xml version="1.0" ?>
<QBXML>
    <QBXMLMsgsRs>
        <ItemReceiptAddRs requestID="1" statusCode="3210" statusSeverity="Error" statusMessage="The &quot;LinkToTxn&quot; field has an invalid value &quot;TxnID=B7-1226696553, TxnLineID=B9-1226696553
            &quot;. " />
    </QBXMLMsgsRs>
</QBXML>

I think this goes back to the fact you cannot play with the ItemLineQuantity, each line must be receipted in full. Linked Item Receipts must be done like this:

INSERT INTO "ItemReceiptItemLine" ("VendorRefListID", "RefNumber",
"ItemLineLinkToTxnTxnID", "ItemLineLinkToTxnTxnLineID",
"FQSaveToCache") 
Select "VendorRefListID", {fn CONCAT('POLink', "RefNumber")},
"TxnID", "PurchaseOrderLineTxnLineID",
0 as "FQSaveToCache" from PurchaseOrderLine
where "VendorRefFullName" ='Perry Windows & Doors' and "RefNumber"='401'
and "PurchaseOrderLineSeqNo"=1

If the quantity needs to be varied, update the purchase order line first.

See: How do I receive Items against a Purchase Order? Receive Inventory without Bill? ItemReceipts for more information.

Where is this restriction about ItemLineQuantity coming from? I can open QuickBooks and manually post  a receipt for less than the quantity ordered. I can also send just one of my Insert statements through QODBC with the syntax I've posted here including the ItemLineQuantity less than the quantity ordered and it posts perfectly. The problem is only when I am trying to batch transactions together with that FQSaveToCache flag. And unfortunately it's important to me to be able to batch them.

The process here is pruely only a linked:

ItemLineLinkToTxnTxnID to PurchaseOrderLine.TxnID

ItemLineLinkToTxnTxnLineID to PurchaseOrderLine.PurchaseOrderLineTxnLineID

execution. Modifications are not supported.

FINALLY, I have found a solution to receiving lines with a different qty than ordered in a mulit-line receipt - annoying but workable!

In the example below I have a single receipt of two lines. I need to specify the qty because they received less (or more) than specified on the PO.  I use the FQSaveToCache flag as you explained to batch the insert statements for the item lines so that the two lines are inserted as a single receipt.  But the trick is, after the fact, I update the Qty to the real qty received. Hopefully I have not created some other problem that I am unaware of, but it seems to work just fine.

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, VendorRefFullName, FQSaveToCache)
VALUES ('5.1', 'C3-1227037305', 'C5-1227037305', 'QB Vendor', 1)

UPDATE ItemReceiptItemLine
SET ItemLineQuantity = 30
WHERE RefNumber = '5.1' AND ItemLineItemRefFullName = 'P102'

INSERT INTO ItemReceiptItemLine
(RefNumber, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, VendorRefFullName, FQSaveToCache)
VALUES ('5.1', 'C3-1227037305', 'C6-1227037305', 'QB Vendor', 0)

UPDATE ItemReceiptItemLine
SET ItemLineQuantity = 60
WHERE RefNumber = '5.1' AND ItemLineItemRefFullName = 'P101'

Me again  

I should have realized that with the FQSaveToCache flag set = 1 on the Insert statement that my subsequent Update statement would fail becaue the record hasn't really been inserted yet! So the true result is every other one works correctly, after the FQSaveToCache = 0.

Is there any syntax for caching UPDATE statements as well?

It is possible to cache an update statement by adding FQSaveToCache= 1 like this:

UPDATE ItemReceiptItemLine
SET ItemLineQuantity = 30, FQSaveToCache= 1
WHERE RefNumber = '5.1' AND ItemLineItemRefFullName = 'P102'

TxnID can be used in an INSERT statement on transaction tables to append lines even through TxnID is a read only column. QODBC uses the TnxID to locate the transaction to append new lines too. 

Thank you Tom. I am done now.

Time to move on to invoicing shipments. Hopefully this will go much smoother now that I've learned so much on receipts! If not, I'll see you on another thread!