I have seen many requests recently for a way to see what Dynamics GP vendors have had no recent activity. Since ‘recent’ can mean different things, I thought I would put together a view that shows the last activity date for each vendor. Once you have this you can easily filter for whatever date range you need. I also added the date the vendor was created, since a brand new vendor might not have any activity yet.
Here is a sample of the results (please click on the image to see it bigger):
Some additional resources:
create view view_Vendors_Last_Activity as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- Created July 17, 2014 by Victoria Yudin -- Flexible Solutions, Inc. -- For other code, please visit http://victoriayudin.com -- Returns all vendors with last activity date -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select rtrim(v.VENDORID) Vendor_ID, rtrim(v.VENDNAME) Vendor_Name, max(coalesce(d.DOCDATE, '1/1/1900')) Last_Activity, v.CREATDDT Created_Date from PM00200 v left outer join (select VENDORID, DOCDATE from PM10000 union select VENDORID, DOCDATE from PM20000 union select VENDORID, DOCDATE from PM30200) d on v.VENDORID = d.VENDORID group by v.VENDORID, v.VENDNAME, v.CREATDDT go grant select on view_Vendors_Last_Activity to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Hi Victoria, we are running GP version 6. I tried running this script in SQL but get the following error response:
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM00200’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM10000’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM20000’.
Server: Msg 208, Level 16, State 1, Procedure view_Vendors_Last_Activity, Line 11
Invalid object name ‘PM30200’.
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name ‘view_Vendors_Last_Activity’.
It looks like it is not recognising the table names which look correct from what I can see.
Help
David
LikeLike
Hi David,
Are you sure you’re running the script against the company database in SQL? Those errors sound like you might be running it against another database that does not have those tables.
-Victoria
LikeLike
Hi Victoria. You were right. I’m very new to SQL. All working fine now. How do I send the output to Excel?
Thanks again
David
LikeLike
David,
This may depend on the version of SQL you’re on, however, generally, you can either copy the results and paste them to Excel or right click on the results and choose Save Results As…
-Victoria
LikeLike
Thank you Victoria for sharing so many of these SQL gems!
Leslie
LikeLike
Hi Victoria, sorry to be a pest. When I use either of these methods I do not get the headers, just the data.
Also, is there a similar report for sales?
LikeLike
Hi David,
What version of SQL are you using?
For sales – please take a look at my page with SOP SQL views.
-Victoria
LikeLike
Hi Victoria, we are using GP v6. The SQL version is 8.00.194. We are soon to upgrade.
Most of our sales invoices happen through Wennsoft service manager so the SOP reports are no good for me.
David
LikeLike
Hi David,
Version 8.00.194 is SQL 2000. I don’t have that installed anywhere anymore, so I cannot test this, but from Googling it, it looks like you can go to SQL Server Query Analyzer | Tools | Options | Results and there will be a setting there for including column headers with your results.
If Wennsoft is not populating SOP tables with their invoicing, then you are right you cannot use my SOP queries. You might want to talk to Wennsoft about what you’re looking for to see if they can help you.
-Victoria
LikeLike