When running a Receivables workspace for the current date, the totals match the AR Aging Trial Balance. When backdating it, it doesn't match the AR Aging Trial Balance. The layout on the view has the same columns as the AR Aging Trial Balance. Below are the formulas that I'm using:

Current: if(and({Trx Date}<={As Of Date},{Trx Date}+30>={As Of Date}),{Current Amt},0)

31-60 days: if (and({Trx Date}+31< {As Of Date},{Trx Date}+60 >= {As Of Date} ),{Current Amt},0)

As of date: QUERYDATE("As Of")

What needs to be changed about the formula(s) so that it will give me the same totals as the Aging Trial Balance?

Miriam:

I'm thinking it's because the {Current Amt} field isn't date sensitive. I believe you need to use the {Original Amt} instead.

If I use the original amount then it shows way too much in Receivables.

Miriam:

Thinking about this further, I'm not even sure if this could be done in GridView.

Why are you recreating the Aged Trial Balance in GridView?

Our client needs to see all jobs associated with the receivables. They had one customer for each job (so when running the summarized aged Trial Balance they had one customer/job per line) and to clean up their jobcosting, we have merged all of the same customers together with toolkit. Now when running the summarized aged Trial Balance in AR they can't see the job number and running the detail version is a report of 400+ pages. They would like to see the summary and just click on a specific customer to see the detail.

I doubt that you'll be able to get a backdated GridView to agree to the Receivables ATB, since that requires inspecting every transaction in advance and deciding whether it belongs in the period, and also whether the invoice was paid in the backdated report.

Since this is for management purposes, why worry about whether the numbers tie to the ATB?

They need to be able to see the detail on a job basis. Before we merged the customers it was easy as they had one job per customer. Now that there's multiple jobs per customer and they run the detail report it's too long to go through and find the job you're looking for.
In GV they can just double click on the job and have the detail visible on a separate view.
Our client doesn't run the Aged report on the last day of the month as the month isn't completed yet. So when they run it 1 month later they have to see the exact same numbers as in AR.

I doubt that you'll be able to get a backdated GridView to agree to the Receivables ATB or the General Ledger AR Control account. Backing out the transactions that must be ignored would be VERY difficult, if not impossible.

Actually, I'm pretty sure that with a cash receipt spread across multiple invoices against multiple jobs, it WILL be impossible.

Sorry.

The issue is not that there is too much information after month end procedures have been performed, however, we need an option in the 'Receivable Aged Trial Balance' to sort the transaction by job# when selecting to run report with job detail. This is a suggestion for a future release. Does anyone have a workaround?

Hi, Eva,

For now, when they run the report in Receivables, all they need to do to find the Job they are really interested in is to use the Search/Binoculars function available on the report Preview window. Just make sure the Job(-Phase-Category) being searched for is entered in the search box exactly as it would be displayed on the report as this is a clear case of what you type is what you get (or not).