Insert blank row into a Quickbooks purchase order using ItemAggregate

Question

I'm looking to automate the generation of purchase orders (POs) in Quickbooks.

We're using the CData ADO.NET provider for Quickbooks (desktop), but since we're using ItemAggregate to add lines to POs, I'm not sure the specific adapter makes a difference.

For readability, our POs have blank rows between certain sections. I'm struggling to replicate this in ItemAggregate. I've tried:

1. adding <Row/> (QB ignores this)
2. adding <Row><ItemDescription/></Row> (QB ignores this)
3. as #2, but with a single space in the ItemDescription (QB ignores this)
4. as #2, but with a tab in the ItemDescription (QB ignores this)
5. as #2, but with   in the ItemDescription (QB prints " ")
6. as #5, but change   to a single space after saving the PO (causes an exception: "There was an error when modifying a PurchaseOrder. QuickBooks error message: You have no items or one or more of your amounts is not associated with an item. Please enter an item.")

Does anyone know of a programmatic way to get QB to add a blank line to a purchase order?

Answer 1

The CData ADO.NET Provider has been updated to support this functionality.

You will need to add a new hidden connection property to your connection string. Add PreserveAggregateWhitespace=true to your connection string. Now when you insert or update transactions, your XML elements will retain their whitespace:

<Row><ItemDescription> </ItemDescription></Row>

Your INSERT statement for a new SalesOrder could look like:

INSERT INTO 
  SalesOrders (CustomerName, ItemAggregate) 
VALUES 
  ('Doe, John','<SalesOrderLineItems><Row><ItemName>Repairs</ItemName><ItemQuantity>1</ItemQuantity></Row><Row><ItemDescription> </ItemDescription></Row><Row><ItemName>Removal</ItemName><ItemQuantity>2</ItemQuantity></Row></SalesOrderLineItems>')

Notice the blank space in the ItemDescription.

You can contact the CData Software Support Team if you need the updated build.