Hi,

We have developed AR Aging report  using QODBC and Crystal reports . We are using the following Query

 SELECT "Command"."TXNDATE", "Command"."CUSTOMERREFFULLNAME", "Command"."REFNUMBER", "Command"."PONUMBER", "Command"."DUEDATE", "Command"."BALANCEREMAINING"
 FROM   "EBIZNET"."INVOICE" "Command"
 WHERE  "Command"."CUSTOMERREFFULLNAME" LIKE 'MetroPCS HO%' AND "Command"."BALANCEREMAINING"<>0

The data we are gettig for Crystal Report is not same as AR aging report coming from QuikBooks.

The BALANCEREMAINING column in INVOICE table of QuikBooks is not updated to 0 even the there is a Payment applied . The IsPaid flag is also not updated to 1. But when we open the invoice from QuikBooks it is showing as paid.

Please help me Is the where the BALANCEREMAINING field is updated. Is the query which we are using is correct.

Thanks in advance.

Sravan

You need to use the UNOPTIMIZED tag in your SELECT statement to ensure you see very recent changes, see: How do I setup the QODBC Optimizer? Where are the Optimizer options? for further information.

Please also check that your are using QODBC v6.00.00.155 (or later) as SELECT statements with OPEN BALANCES are automactically run in UNOPTIMIZED mode.

HI Tom,

Thanks for the response. We have upgraded QODBC version and ran the invoice query  UNOPTIMIZED.

Unfortunately it did not solved our problem.

We are still not getting BALANCEREMAINING correctly.

We are fetching BALANCEREMAINING from INVOICE table to know whether invoices are paid or not.
The problem we have is it is not updated properly in INVOICE tbale even there are payments to that invoice.

Is there any place in QuikBooks where we can get correct value of BALANCEREMAING for every invoice ?

This is a priority issue for us and our customers are unhappy because of wrong AR Aging report we have on our dashboard.

Appriciate your quik response.

Sravan

The A/R aging summary report summarizes the status of unpaid invoices and statement charges in accounts receivable. For each customer who owes money, the report shows:

• What the customer owes for the current billing period
• What the customer hasn't paid from previous billing periods
• Subtotal balances for each job (if the customer has multiple jobs with your company)

While the Customer balance summary report shows unpaid customer balances, grouped by customer and job.

When I ran the Customer balance summay report in QuickBooks against the Sample Rock Castle Constructtion company file the result was:

Which matches the Balance Remaining in the Invoice table exactly as shown in QODBC with this SQL Statement:

SELECT "CUSTOMERREFFULLNAME", SUM("BALANCEREMAINING") AS BALANCE
FROM   "INVOICE" WHERE "BALANCEREMAINING" > 0
GROUP BY "CUSTOMERREFFULLNAME"

While the A/R Aging Summary Report in QuickBooks shows:

Which is different to the Customer Balance Summary Report (as Abercrombie, Kristy shows a $3,111.28 amount) however running the following sp_report CustomerBalanceSummary stored procedure in QODBC shows:

sp_report CustomerBalanceSummary show  Text, Label, Amount 
parameters DateMacro = 'ThisMonthToDate',
           SummarizeColumnsBy = 'TotalOnly'

As you can see the results differ depending on whether the date 15 Dec, 2007 or 31 Dec, 2007 was used by the report.

See also: sp_report ARAgingSummary stored procedure