Data inconsistency in A/R Listing

I have 3 issues; not sure if they're related. It is a foreign currency set-up, and I'm aware of the Knowledgebase article saying that one should call Sage support for database repair -- but I'm not sure I'm at that point -- would prefer to explore ideas from the community first. Thanks.

1. First issue is in the summary report displayed by the data consistency check. Does the A/R balance shown in that report get drawn from the balance sheet A/R (control) account? [This is what I would expect.] However, in my case, although the amounts on each side of the inconsistencies summary report are within $30 of each other, the base amounts are on the order of $176,000. Whereas, on my B/S and A/R listing, the amounts from those sources are on the order of $186,000 (but also showing a discrepancy of $30). [FWIW, I see from another forum post that the customer deposit account comes into the picture, but the amount there is only around $100.] The point here is not the $30 difference [see next], the point is why the roughly $10,000 difference.

2. Second issue: is how to track down the discrepancy between the B/S side versus the A/R listing side in the inconsistency summary window. I've tried searching the AR listing for the discrepancy amount, but without finding anything helpful. 

3. Third issue is a F/X problem: At the moment, we have one customer with a USD balance, which balance is correct and is fully understood how it arose, and it's a very recent entry (about USD$51; strictly speaking a customer deposit item, so a credit balance). But, when I display (in home and foreign currency), the B/S, trial balance, and also the unrealized FX gain/loss report, the USD balance shown for A/R balance is about USD$117 (also a credit balance). Whereas in the AR listing itself, it's only showing the one USD $51 item as the sole foreign currency item. In the AR listing, I have searched for the difference between those two amounts (in both USD and CAD), with nil results. So I'm not sure what Sage is looking at when it finds the USD$117 amount.

  • Issue 1: found a partial answer. The data inconsistency report draws the A/R data from whatever produces the report called "aged overdue receivables". The A/R listing I was referring to in my question is the report called "client aged". I do have different aging schemes in each of those reports, but comparing the differences, it's still not clear to me where the roughly $10K discrepancy comes from. The AOR report does have a column for "1 to 60", while the client aged report has a "current" column, so maybe there's a difference between a day 1 and day 0? (The amount shown as "current" in the client aged report is more than $10K.)
  • Actual database corruption at the database level is rare, and from the program level is unlikely. Sometimes report results look odd until the strange and wonderful entry is discovered.

    1. Sage 50 is modular and the report compares the A/R and G/L module contents. The A/R modules transaction data basically sum to the amount after the last transaction in the last year in the G/L module. If they were once in balance and now aren't, there's been a program error. Make sure all customers are selected, and the date is the latest allowed date for both reports. If the report shows a $30 difference, that is the difference.

    2. Be aware that prepayments go to a different G/L account but are summed into the grand totals for A/R. Use the Aging report as it will show all amounts, not the Aged Overdue report as it may not show all amounts.

    3. Again, be aware that Deposits show up as a liability on the balance sheet, not in Accounts Receivable.

    When looking for any corruption where for the stored year end total disagrees with the transaction detail, use a 'drill-down' mouse click from the trial balance to see if the numbers agree with the G/L listing report.
  • dlmoir said:
    The data inconsistency report draws the A/R data from whatever produces the report called "aged overdue receivables". The A/R listing I was referring to in my question is the report called "client aged".

    All the A/R data is in one set of tables - tCusTr and tCusTRDT.   The Overdue report gets one additional piece of data - the number of due days - from tCusTR and uses it to calculate which column to display the amount in.  

    The regular Customer Aged report uses only the invoice date  and is a measure for finance. 

    Aged Overdue Receivables is exactly that - a calculation of overdue receivables intended for collections.  

    If the default number of due days on the customer record isn't filled in, it won't be filled in on the invoice, and the program will assume every invoice is due immediately.  (Changing the due days on the customer record has no effect on previous invoices.)