Author
Topic: PCQB_ZQuery question (Read 2910 times)

I need to query a chart of accounts called "Client Deposits" to determine what the balance is for a specific client. It appears that Geoffrey has done something similar to this with a ZQuery per his post of June 8. Here is what he posted with the missing bracket appended:

ZQuery and XPath together are a great shortcut when you need to put a single value from QB into a field in FMP, but without knowing exactly what you're looking for, I can't offer an example. It sounds like you might be looking for ReceivePayment transactions filtered to contain only those records for a particular client and deposited into a particular account. If so, ZQuery and XPath may be of limited value because the query will return a list of transactions. There's also no filter in the ReceivePaymentQuery that lets you ask for only those transactions with an UnusedPayment value > 0. That said, XPath can grab a return separated list of all UnusedPayment values for the client/account combo, and you could walk that list in a loop and sum the values to get your result.

Can you describe what you're looking for in greater detail? How would you get your result within the QuickBooks interface?

Random notes that might be helpful:

Intuit's On Screen Reference (OSR) can help you figure out what elements (if any) to include in the optSearchCriteria parameter. I always view the XMLOps tab to see all the tags in an easy to copy and paste format. That same view will help you construct the XPath string, which is basically ".//" followed by the tag hierarchy without angle brackets (and a "/" separating each tag) followed by "/text()" to return an untagged result. You'll use text() even when the result is a number, date, timestamp, etc.

Geoff,Thanks for the in depth response. Here is the situation: I have a Other Current Liability Account called Client Deposits. When my clients receive deposit money they make an entry into this account for the amount of the deposit and the job name. Then when they create an invoice they add a line from this account at the bottom of the invoice so that the invoice zeros out. For example, my client might receive $20,000 as a deposit for work and services not yet performed and add it to the Client Deposits account. Then when they issue an invoice that has services and materials totally $1,500 they add a List Item that draws from the Client Deposits account but they set the quantity to -1 and the price to the total of the invoice amount. The causes the invoice to zero out and the Client Deposit account to decrease the correct amount for this job.

Of course before they run the invoice I need to verify that there is a sufficient balance for this job to cover the invoice amount so I have to query Client Deposits for this job to obtain the current balance. At Devcon this week the pretty young woman at the Productive Computing booth (whose name escapes me at the moment) suggested the AccountQuery call. I think that will probably work as I just want a single number (the balance) and according to the documentation it should work. I'll post here as soon as I can confirm that.

I'm having serious difficulty reconciling your description of your customer's workflow with good accounting practices, and suspect I'm missing some key elements of their interactions in QB. Please see my reply in your new thread.