Archive | System/Setup SQL code RSS feed for this section
3d small people - email

SQL view for SOP email setup in GP 2010

I am starting to work with emailing SOP documents using Dynamics GP 2010 and new functionality that we are going to be releasing in an upcoming build of GP Reports Viewer. It’s pretty cool stuff, however, as I am working with customers to set this up, we’re finding that out-of-the box GP does not have any […]

Continue reading
baby and horse

SQL view for user activity in Dynamics GP

There are many different pieces of code that I have seen for this over the years, but more often than not what our users ask me for is a way to quickly see who is logged into GP, what company, and when did they log in. While this can easily be seen on the User Activity window (GP | […]

Continue reading

Change GP fiscal period names using SQL Server

Every time I create a new fiscal year in Dynamics GP or show customers how to do it, I get a little frustrated that I have to manually rename all the periods. Some users may not care that instead of January GP displays Period 1, but I don’t think that is very user friendly. So, for more years […]

Continue reading
Calendar. September 2010.

SQL queries for fiscal years and periods in Dynamics GP

Often when creating reports we’re asked to show the current year vs. last year or select the current fiscal year or period as a date range for values returned. Sometimes dates can be hard-coded, especially when the fiscal year is the calendar year. But some reports can be made more dynamic by using the fiscal periods set up in […]

Continue reading

SQL view with security and SmartList details in GP

Robert Cavill from Emeco in Australia has sent me an amazing update to my Security Roles and Tasks in Dynamics GP SQL script that includes SmartList objects. This script was originally written for GP 10.0 and GP 2010, but will also work with GP 2013 and GP 2015. To get the most out of this script […]

Continue reading

SQL view with security resource details for Dynamics GP

As a corollary to my view showing the Dynamics GP security roles and tasks assigned to users, I have created another view that adds the security resource details. Thank you very much to Tim Foster for pointing me to a newsgroup post by David Musgrave detailing how to populate the SY09400 table in SQL which holds […]

Continue reading

SQL view to show security roles and tasks in Dynamics GP

Dynamics GP 10.0 has brought about a drastic change in GP security. Not only is the security pessimistic now, so by default no permissions are granted, but setting up and administering security is quite different from what many of us are used to after working with previous versions of GP for many years. And, of course, reporting on […]

Continue reading