Author Topic: PCQB_ZQuery Question  (Read 970 times)

mac

  • Member
  • *
  • Posts: 2
PCQB_ZQuery Question
« on: November 23, 2015, 10:31:56 AM »
We have been using ZQuery to return total balance for a customer (actually technically a job within a customer) such as:

PCQB_ZQuery(“CustomerQueryRs/*/TotalBalance/text()”;”Customer”;”<FullName>” & $$FULLNAME & “</FullName>”;””)

The question is:

Is there a parameter to use this function that will return a balance in a particular account (Such as Security Balance) for a customer(job). We have an account named  “TENANT SEC DEPST” and the account number is 2150

I looked at the discussion "How to obtain an account balance when a filter is applied?" and I get the feeling that this might not be possible with ZQuery.

Thanks for the help

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 469
Re: PCQB_ZQuery Question
« Reply #1 on: November 23, 2015, 11:37:54 AM »
From the details you've posted, I agree that PCQB_ZQuery cannot get what you want with "Customer" as the Object parameter. I suspect that it will be possible using "GeneralDetailReport" or "GeneralSummaryReport" as the PCQB_ZQuery function's Object parameter. I can say for sure that you can create a Request in XML and get it using the PCQB_ZExecute function.

Setting up the filters in the optSearchCriteria parameter may be a bit tricky, and the Xpath for either Report type is pretty esoteric. That said, I've set this sort of thing up for several clients and am certain this can be setup to create a very focused Request that QB processes quickly.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

mac

  • Member
  • *
  • Posts: 2
Re: PCQB_ZQuery Question
« Reply #2 on: November 24, 2015, 11:31:40 AM »
Geoffrey,

Thanks for the suggestion to try the different object parameter. I have been trying to find reference to the other parameters for ZQuery. I have some documentation from my original coding from the old fblink days. Do you know of any more specific info on the ZQuery parameters?

In particular, I would like to know the function of the asterisk and the next   field (here “TotalBalance”) in “CustomerQueryRs/*/TotalBalance/“

As you suggest, it might be better to try creating a request for ZExecute and give up on the one line obsession ;-)

Thanks,

Robert Sutton (mac)

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 469
Re: PCQB_ZQuery Question
« Reply #3 on: November 24, 2015, 02:45:50 PM »
Interesting question!

The asterisk in this context is a wildcard and a shortcut. Think of an XML Response as a series of nodes and elements, with some elements appearing in more than one node. CustomerQueryRs is the largest node, and may contain multiple CustomerRet nodes. The CustomerRet node contains multiple ListID elements, with the primary value ( the one that uniquely identifies the Customer ) at level one. At level two, there are ListID values for ClassRef, ParentRef, ContactRet ( and maybe more than one of these ), CustomerType, etc.

The asterisk in the filter makes the level/enclosing node moot. So using this Xpath filter...

"CustomerQueryRs/*/ListID/text()"

...would return List of all ListID values contained in the CustomerQueryRs Response. I can't think of an instance where that list would be particularly useful, but it's powerful nonetheless.

A more useful example might extract all the FullName values in the CustomerRef nodes from a Bill's ExpenseLineRet, ItemLineRet and ItemGroupLineRet nodes. The latter node contains its own ItemLineRet nodes at one level deeper than the first two. That Xpath filter looks like this...

"BillQueryRs/*/CustomerRef/FullName/text()"

...and will pull a List of FullName values from every CustomerRef node regardless of the name of its parent node or how deeply nested it might be.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 469
Re: PCQB_ZQuery Question
« Reply #4 on: November 24, 2015, 02:54:19 PM »
BTW: The PCQB_ZQuery may work fine to pull GeneralDetail and GeneralSummary Reports, I've just never tried it.

The Response from those is significantly trickier to parse because all data is expressed as an attribute and the Xpath is always relative. Totally possible in one line, but a BIG step up from mastering Xpath basics.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852