Author Topic: "How to obtain an account balance when a filter is applied?"  (Read 4082 times)

Will

  • Member
  • *
  • Posts: 19
"How to obtain an account balance when a filter is applied?"
« on: August 22, 2010, 08:07:27 AM »
I would appreciate anyone's help with this task: How do you obtain the account balance when a filter is applied?
Here is the scenario: You create a "Other Current Liability" account called "Client Deposits". When you create a new job you receive $x,000 as a deposit for work you are going to do, so you record an entry in this account for $x,000 and for that job.

You do the work and create an invoice to charge the customer. At the end of the invoice you add a last line that is linked to the Client Depostis account with Quantity = -1 and the cost equal to the sum of the invoice. This zeros out the invoice due amount, since you have already received a deposit to cover this invoice amount. The last line also reduces the amount for this job in the Client Deposits account so that if you run a Customer Balance Detail report you see the initial deposit, the deduction for the invoice amount and the remaining balance.  All of this is explained in detail in QuickBooks when you receive a deposit, or an advance, for work you are going to provide for a customer.

Of course when you create the invoice you need to query QuickBooks to determine what the balance is in the Client Deposits account for this particular job. If the balance is less than the invoice amount then there will be a balance due amount on the invoice, otherwise there will not be.

The lines below will obtain the Client Deposits balance for the entire account:
SetField [Results::g.QBResult; PCQB_RqNew("AccountQuery"; "")]
SetField [Results::g.QBResult; PCQB_RqAddFieldWithValue("FullName"; "Customer Deposits")]

The question is how to find the balance in the Client Deposits account for a particular job. For example when I add the following line:

SetField [Results::g.QBResult; PCQB_RqAddFieldWithValue("IncludeRetElement:FullName"; "Smith, Bob")]

The balance returned is null. But when I run the Customer Balance Summary report filtering on Account=Client Deposits and Name=Smith, Bob then I get the correct balance.

Any ideas what I need to do to get the balance for that particular job?

Thanks for your assistance.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: "How to obtain an account balance when a filter is applied?"
« Reply #1 on: August 26, 2010, 02:01:38 PM »
Technical answer first:

IncludeRetElement is a filter that QB applies to the response before sending it back to the calling application. All elements EXCEPT those named in the IncludeRetElement functions will be omitted. It's intended to cut down on the amount of data transferred, and thus make communication faster and reduce the size of the data block that the calling app must process. It made a big difference when FMP text fields were constrained to ~64,000 characters, but now has far fewer practical uses.

I use the plug-in's Z functions for virtually all interaction with QB, but the format of the relevant line looks inconsistent with my understanding of the PCQB_RqAddFieldWithValue function. I would have expected this:

SetField [Results::g.QBResult; PCQB_RqAddFieldWithValue("IncludeRetElement; "FullName")]

which would tell QB to return only the FullName element of the Query's result. This would be of no use whatsoever in the situation you've outlined.

Non-technical potential answer:

Is your client creating Jobs for their Customers?
If so, your client should be making Receive Payment entries in QB for the Job, not the Customer. You could then do a CustomerQuery on the Job (NOT the Customer) and use the value of the Balance element to determine how much, if any, credit the customer has remaining.

If your client is not creating Jobs, and any as-yet-unapplied payment from a customer can be applied to that customer's invoices, the CustomerQuery (on the Customer) works also.

Answer you didn't ask for:

Your client should not be reducing the invoice value by adding a Line Item with a -1 qty and should be applying the Receive Payment to the Invoice. The InvoiceAdd grammar supports linking an Invoice to another transaction. You might want to test whether doing so automatically applies the payment to the Invoice.

HTH!


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

Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: "How to obtain an account balance when a filter is applied?"
« Reply #2 on: August 30, 2010, 08:43:34 AM »
Hi All,

Note that a full name is the name of the item prepended with a colon and the full name of its ancestors.  For instance "Customer Deposits:Smith:Deposits"  indicates that the desired account is named 'Deposits' which is a sub-account of 'Smith' which in turn is a sub-account of 'Customer Deposits'


To retrieve a particular account by its full name you must include the actual full name (as described above) in the request.  Start the request with...
PCQB_RqNew( "AccountQuery")
then add the filter for the full name...
PCQB_RqAddFieldWithValue( "FullName" ; $theAccoutFullName )
and finally execute the request....
PCQB_RqExecute

I think where Will MAY be tripping up is in determining the actual full name of the account.  He is filtering for an account "Customer Deposits"  which will return only the information for the top level "Customer Deposits" account.  Will states that he is looking for an account for a particular job.  I am guessing that the accountant/controller is entering sub-accounts into the Customer Deposits account - one for each job perhaps.  If so then the full name for the desired account will not be "Customer Deposits" but MAY be something like "Customer Deposits:Smith Deposits."   Will will need to determine the actual full name of the account from which to extract the balance and pass that name to the PCQB_RqAddFieldWithValue( "FullName"....) function. 


Chris

Will

  • Member
  • *
  • Posts: 19
Re: "How to obtain an account balance when a filter is applied?"
« Reply #3 on: September 04, 2010, 04:29:10 AM »
Geoff and Chris -
Thanks you both for your responses. One or both of you might be right, but I still can't get the balance I'm looking for.  See below:

Geoff -
You wrote:
I use the plug-in's Z functions for virtually all interaction with QB, but the format of the relevant line looks inconsistent with my understanding of the PCQB_RqAddFieldWithValue function. I would have expected this: SetField [Results::g.QBResult; PCQB_RqAddFieldWithValue("IncludeRetElement; "FullName")]
You are absolutely correct. My syntax was incorrect but like you wrote IncludeRetElement is not required.

Your question:Is your client creating Jobs for their Customers?
The answer is sometimes. When I look at CustomerQuery, as you suggest, it appears that the balance it would return would be the same as the Open Balance Report would show if I ran it for that customer in the  Customer Center section of QuickBooks. However, the balance I am seeking is not the customer balance but the balance from the other current liabilities account - specifically those entries that involve only this one customer/job.

You wrote: Answer you didn't ask for:Your client should not be reducing the invoice value by adding a Line Item with a -1 qty and should be applying the Receive Payment to the Invoice. The InvoiceAdd grammar supports linking an Invoice to another transaction. You might want to test whether doing so automatically applies the payment to the Invoice. I can't find a url on the intuit site to reference here, but if you do a search in the QBooks help section with this search criteria: "Handling Upfront Deposits and Retainers" then the answer from Qbooks is to use the -1 qty and the item linked to the other current liability account. It will look strange to anyone who sees the invoice but it works and is what QBooks recommends.

Chris -
I don't think that issue is with the formulation of the FullName. There is only the one other liability account, Customer Deposits, and it doesn't have subaccounts. All it has are entries to Payees which are the Customer/Job. I was optimistic that "FullName:Customer Deposits:Smith, Bob" would work but it doesn't because Smith,Bob is a payee and not a subaccount.

At this point I'm stuck. AccountQuery seems like it would be the most logical query option, but without being able to filter for the payee (which would be the customer/job) the balance returned is not helpful. CustomerQuery holds hope but I can't see how to filter on just the Customer Deposits account. I'm sure that there is something obvious here that I'm missing. I'm sure getting a balance from an account based on certain filters is doable.

Again your suggestions and insights would be most appreciated.

Sincerely,
Will


Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: "How to obtain an account balance when a filter is applied?"
« Reply #4 on: September 07, 2010, 01:14:01 PM »
I think that what you are after is the sum of all transactions recorded in a specific account for a specific entity/customer.  Is this correct?

Will

  • Member
  • *
  • Posts: 19
Re: "How to obtain an account balance when a filter is applied?"
« Reply #5 on: September 12, 2010, 06:28:17 AM »
That sounds right.

Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: "How to obtain an account balance when a filter is applied?"
« Reply #6 on: September 13, 2010, 08:31:47 AM »
I have not tested the following, but it may be worth a try....
Have a look at the JournalEntryQyery request message in the OSR.  This appears to be the request type that you are looking for.  It appears that one can specify the account and entity (customer) in the request and the response will contain entries in the journal for that account/entity combination.  Parse the response, and perhaps sum the amounts from the debit/credit lines to get a current balance.

It would be easier if your client were to receive the deposits using QB's Receive Payment interface.  In this interface any unapplied payments are record in QB as a CreditMemo, and CreditMemos are able to be queried directly by the plug-in/SDK for their remaining balance.

Chris

Will

  • Member
  • *
  • Posts: 19
Re: "How to obtain an account balance when a filter is applied?"
« Reply #7 on: September 13, 2010, 08:37:57 AM »
Thanks Chris. I had looked at Journal enteries but will look at them again. I'll post whatever I find out.