dollar symbol

SQL view for Dynamics GP vendors with last activity date


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.

13 Responses to “SQL view for Dynamics GP vendors with last activity date”

  1. 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

    Like

    • 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

      Like

    • 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

      Like

      • 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

        Like

  2. Thank you Victoria for sharing so many of these SQL gems!

    Leslie

    Like

    • 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?

      Like

      • Hi David,

        What version of SQL are you using?

        For sales – please take a look at my page with SOP SQL views.

        -Victoria

        Like

        • 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

          Like

          • 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

            Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP vendors with last activity date - Microsoft Dynamics GP Community - July 23, 2014

    […] out SQL view for Dynamics GP vendors with last activity date by Victoria Yudin Till […]

    Like

  2. SQL view for Dynamics GP vendors with last activity date – 7/17, Victoria Yudin | - July 17, 2014

    […] Continue reading on Source Blog […]

    Like

  3. DynamicAccounting.net | SQL view for Dynamics GP vendors with last activity date | Victoria YudinSQL view for Dynamics GP vendors with last activity date | Victoria Yudin - DynamicAccounting.net - July 17, 2014

    […] Yudin shows off a SQL view for Dynamics GP vendors with last activity date This is nice for cleaning out […]

    Like

  4. SQL view for Dynamics GP vendors with last activity date : Interesting Findings & Knowledge Sharing - July 17, 2014

    […] More: SQL view for Dynamics GP vendors with last activity date […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,923 other followers

%d bloggers like this: