How do I create a Smart List that shows the following:
customer ID
invoice number
invoice date
invoice amount
unpaid amount
check number applied to invoice (not the payment number assigned by GP)
check amount (the total payment. not the amount paid for the invoice)
posting date of the check
I've searched, but haven't been able to find anything like this. Hopefully, I'm not just using bad search terms.
Thank you!
I believe this view from my blog has most of the fields you're looking for, it might need to be modified slightly to achieve what you want:
https://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/
- Victoria Yudin
Hello VMedina,
You are correct there isn't a default SmartList option that shows the materials you are outlining but you should be able to use SmartList Designer to build one dependent on the current status of the payment in Dynamics GP as most of the information you are looking for can be pulled from the apply and document record for the payment.
If the Payment is in OPEN status you can use the RM20201 (RM Open Apply) table to base your listing from or RM30201 (RM History Apply) if the payment is in HISTORY (Paid Transaction Removal Utility has been ran to move it).
Accordingly, the OPEN payment detail would be held in the RM20101 (RM Open Transactinos) and HISTORY payment detail would be held in RM30101 (RM Historical Transactions).
Fields that would hold this information for display/linking purposes are:
customer ID - CUSTNMBR
invoice number - APFRDCNM would be payment Document Number and APTODCNM would be for the invoice it is applied to in the apply tables.
invoice date - DOCDATE in transactions.
invoice amount - ORTRXAMT in transactions.
unpaid amount - CURTRXAM in transactions.
check number applied to invoice (not the payment number assigned by GP): CHEKNMBR field in transactions (Note: invoices have a RMDTYPAL value of '1' and payments '9'.
check amount (the total payment. not the amount paid for the invoice): ORTRXAMT of payment in transactions.
posting date of the check: GLPOSTDT of payment in transactions.
Best Regards,
Jeff Grant
Microsoft Dynamics GP Support
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (