Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Dynamics SQL question RE Statement Aging

Dynamics SQL question RE Statement Aging

Dynamics SQL question RE Statement Aging

I have created a custom statement in Access. I am using RM00101 for Customer data, RM00103 for aging and balance information, RM20101 for open transactions and RM30101 for transaction history. Based on those tables, I am accurately (except for NSF cases) creating statements. In addition, I am calculating accurate Balance Brought Forward based on a calculation. However, I can not figure out how to recreate the aging data found in RM00103. I have found tables RM20201 and RM30201 which seems to have some of the necessary data.

Has anyone figured out how to create aging as of a specified date outside of Dynamics? If so, some tips would be appreciated.

RE: Dynamics SQL question RE Statement Aging

The aging in Great Plains on RM is not real time it is snap shot data to fix the ledger in a position between statements.

To recreate the aging information on the sales ledger you would need to calculate it on the same date as the aging was last calculated in great plains.

To ignore the RM00103 data you could perform your own summary calculations based the due date field in the RM Open and RM History files, you should also refer to the RM work which you are not currently doing as there may be posted applied payments which would effect the statement position.

It is worth asking the qeustion why you are not using the statements in Great Plains, is it because you don't know how to use the report writer adequeatly well as it is a powerfull tool and in 6 years of consulting there are only about 5 cases where I have not been able to produce the report exactly to meet someones requirements.

RE: Dynamics SQL question RE Statement Aging

Thanks for the reply. The company had two large GP consulting firms say that there was no way to create the statement like the company wanted in GP. I was able to create the statement like they wanted in the Access report writer. I have figured out how to accurately calculate the balance brought forward at any given date. However, the aging is still an issue. There is additional restrictions to normal GP usage due to a third party application that feeds data to the GP system. That may be why others have failed to create the statement correctly. I need the ability to calculate the aging as of the statement date, not when the system was last aged. That is why I am looking to ignore the RM00103 data and calculate it on the open, history, and now work tables. I need to do this in a stored proc as there are some plans to let the customer pull their own statements via the web at some point in the future.

Thanks for your reply and suggestions.

RE: Dynamics SQL question RE Statement Aging

I might be over simplifying the issue but if you are in Access why don't you create a query like this (either in Access or in a SQL view (In SQL you have to use case statements iif is not a valid function))

Current: iif(DOCDATE > (StatementDate - 30), CURTRXAM, 0)

30 - 60 Days: iif(DOCDATE > (StatementDate - 60 and DOCDATE <= StatementDate - 30), CURTRXAM, 0)


This would assume that you had a union query with all the transactions in and that StatementDate was a user input field.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close