Search code examples
qbxmlqbfc

In the qbXML ItemQuery request , how do I get the description for a service item?

I am creating a tool that will synchronize our production database with QuickBooks (QB). I am trying to get a list of all the items in QB using ItemQuery and I want to get the description of each item as well. However, it seems that different types of items have different ways of specifying the description. Using IncludeRetElement, I am able to get the SalesDesc for ItemInventoryRet, but I am struggling with getting the description for ItemServiceRet (and a few others, but I think if I can figure this one out, I will be able to figure out the others).

Here is my request...

<?qbxml version="12.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError">
    <ItemQueryRq requestID="2">
      <IncludeRetElement>ListID</IncludeRetElement>
      <IncludeRetElement>Name</IncludeRetElement>
      <IncludeRetElement>FullName</IncludeRetElement>
      <IncludeRetElement>ParentRef</IncludeRetElement>
      <IncludeRetElement>SalesAndPurchase_SalesDesc</IncludeRetElement>
      <IncludeRetElement>SalesOrPurchase_Desc</IncludeRetElement>
      <IncludeRetElement>ItemDesc</IncludeRetElement>
      <IncludeRetElement>SalesDesc</IncludeRetElement>
    </ItemQueryRq>
  </QBXMLMsgsRq>
</QBXML>

And here is the response I'm getting (shortened for clarity)...

<QBXML>
  <QBXMLMsgsRs>
    <ItemQueryRs requestID="2" statusCode="0" statusSeverity="Info" statusMessage="Status OK">
      <ItemServiceRet>
        <ListID>240000-1071531214</ListID>
        <Name>Delivery</Name>
        <FullName>Delivery</FullName>
      </ItemServiceRet>
      <ItemInventoryRet>
        <ListID>270000-1071524193</ListID>
        <Name>1/2" Line</Name>
        <FullName>Irrigation Hose:1/2" Line</FullName>
        <SalesDesc>1/2"  Vinyl Irrigation Line</SalesDesc>
      </ItemInventoryRet>
      <ItemGroupRet>
        <ListID>1E0000-934380927</ListID>
        <Name>Walkway</Name>
        <ItemDesc>Walkway lighting</ItemDesc>
      </ItemGroupRet>
    </ItemQueryRs>
  </QBXMLMsgsRs>
</QBXML>

According to the documentation (pick ItemQuery from the dropdown), the description I think I want is ItemServiceRet > ORSalePurchase > SaleOrPurchase > Desc. The request includes one way I've tried, but I've tried quite a few other ways as well...

  1. <IncludeRetElement>ORSalesPurchase:SalesOrPurchase:Desc</IncludeRetElement>
  2. <IncludeRetElement>ORSalesPurchase.SalesOrPurchase.Desc</IncludeRetElement>
  3. <IncludeRetElement>ORSalesPurchase_SalesOrPurchase_Desc</IncludeRetElement>
  4. <IncludeRetElement>SalesOrPurchase:Desc</IncludeRetElement>
  5. <IncludeRetElement>SalesOrPurchase.Desc</IncludeRetElement>
  6. <IncludeRetElement>SalesOrPurchase_Desc</IncludeRetElement>
  7. <IncludeRetElement>Desc</IncludeRetElement>

So the question is, how do you retrieve sub elements in qbXML queries?

I have found that if I remove all the IncludeRetElements, I do get the values. But I would like to learn how to only get the data I care about. We have a HUGE QB database so this could be a major performance issue if I have to get everything.

As a note, I switched to using QBFC10Lib instead of creating the XML myself hoping it would help me solve this issue, but it didn't. I am still having the exact same issue. I'm guessing that an answer to one will resolve both qbXML and QBFC.

Solution

  • I figured it out. You have to add each level separately, like this...

    <?qbxml version="12.0"?>
<QBXML>
  <QBXMLMsgsRq onError="stopOnError">
    <ItemQueryRq requestID="2">
      <IncludeRetElement>ListID</IncludeRetElement>
      <IncludeRetElement>Name</IncludeRetElement>
      <IncludeRetElement>FullName</IncludeRetElement>
      <IncludeRetElement>ItemDesc</IncludeRetElement>
      <IncludeRetElement>SalesDesc</IncludeRetElement>
      <IncludeRetElement>ORSalesPurchase</IncludeRetElement>
      <IncludeRetElement>SalesOrPurchase</IncludeRetElement>
      <IncludeRetElement>SalesAndPurchase</IncludeRetElement>
      <IncludeRetElement>Desc</IncludeRetElement>
    </ItemQueryRq>
  </QBXMLMsgsRq>
</QBXML>

    I had tried this earlier, but I think I missed a level. Anyway, hopefully this answer will help somebody else from wasting half their day :).