265
I would like to know the last date (Document Date) we made a payment to a vendor. What table(s) is this data stored in? Can this be accomplished using SmartList?
Any vendor who has not been paid in the last 36 months should be marked inactive.
*This post is locked for comments
There is a standard Smartlist report that should work. I am in GP 2010, so the report is under Microsoft Dynamics GP>SmartList, then under Payables>Payables Transactions is a default report Today's Payments.
Review the out of the box columns and add any you need (I would be sure to pull in the field Voided to ensure you do not pull into the the report any voided payments).
Best regards,
Dale
Just a small warning about this - the last check date field is exactly what it says - last CHECK date. If you paid your vendor with a credit card or cash after the last time you cut them a check, this date will not reflect the last payment, only the last check. So if you want the last payment date, regardless of the type of payment, and assuming you're only looking at posted payments, you could use the following code:
select VENDORID Vendor_ID, max(DOCDATE) Last_Payment
from (select VENDORID, DOCDATE
from PM30200 --historical trx
-- only look at payments and ignore voids
where DOCTYPE = 6 AND VOIDED = 0
union
select VENDORID, DOCDATE
from PM20000 --posted but open trx
-- only look at payments and ignore voids
where DOCTYPE = 6 AND VOIDED = 0) a
group by VENDORID
In SmartList you can add the "Last Check Date" column to the Purchasing>Vendors view.
That will be in the PM Vendor Master Summary(PM00201).
PM00201.LSTCHKDT
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,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (