When was the last time a particular customer bought a particular item? Here is a little view that will tell you. For other Dynamics GP views and reporting tips, take a look at my GP Reports page. Or check out the SOP Tables page for more details about reporting on SOP data. ~~~~~ CREATE VIEW view_Last_Sale_By_Customer_Item AS /******************************************************************* […]
Continue readingCustomer shipping addresses in Dynamics GP
Need to see a list of all the shipping addresses for your Dynamics GP customers? You can use the view below which pulls out the ship to address and the associated tax schedule, shipping method , salesperson, etc. To see other SQL views for Dynamics GP receivables data, take a look at the Receivables SQL Views page. For […]
Continue readingYearly Fixed Assets depreciation totals in Dynamics GP
Around this time of year we get a lot of questions about closing the year for the Dynamics GP Fixed Assets module, since you cannot run January depreciation until you close the prior year. And right after we start getting requests for reports of prior year depreciation. Here is a view that shows year to date, last year […]
Continue readingSQL view for Dynamics GP Checkbook Register
The view below will return all Checkbook transactions for Dynamics GP. It does not show details for the deposits because I wanted to keep this to one line per transaction, as it appears on the Checkbook Register in GP. To add deposit detail to you report, you can join table CM20300 on CM20200.CMTrxNum = CM20300.depositnumber. You […]
Continue readingSQL view for Payables apply detail and GL distributions in Dynamics GP
Who doesn’t need yet another view for Payables transactions in Dynamics GP? 🙂 The view below is a combination of my Payment Apply Detail and GL Distributions for AP Transactions views. It lists all Payments, then shows the transactions they were applied to and the GL distributions of those applied to transactions. You can find other Dynamics GP Payables […]
Continue readingSQL view for Payables invoices originating from POP in Dynamics GP
This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice as well […]
Continue readingSQL view for asset account numbers in Dynamics GP
Want to see all the account numbers set up for all your Fixed Assets? You can do this in SmartList, but SmartList is sometimes limiting when you want to do complex searches. Here is a view that will return all the account numbers assigned for each asset in Dynamics GP. ~~~~~ CREATE VIEW view_Asset_Accounts AS /******************************************************************* view_Asset_Accounts […]
Continue readingSQL view for Inventory Price Levels in Dynamics GP
When you are creating prices for different price levels in Dynamics GP, there is no easy way to see these, or to see all the prices for a particular price level. Below is a view that can help with this. ~~~~~ CREATE VIEW view_Inventory_Price_Levels AS /******************************************************************* view_Inventory_Price_Levels Created on Aug 9, 2010 by Victoria Yudin – […]
Continue readingSQL view to show yearly totals for Dynamics GP Vendors
Below is a view that will show yearly totals for your Dynamics GP Vendors. It’s something we have used internally for a while, but I have recently gotten a few requests for it, so I cleaned it up and am sharing it. This will show yearly totals for calendar years. If you want to use fiscal years […]
Continue readingUpdates to SQL view to show all GL distributions for AP transactions
I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the […]
Continue readingAwesome resources available from Mark Polino
Mark Polino, one of the very first Dynamics GP bloggers and someone who has personally inspired me often with his writing, has done some spring cleaning on his blog. There is a ton of great content, but I would like to point out these two great resources: SQL Scripts – one location with descriptions and links for many […]
Continue readingSQL 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 readingSQL view for Fixed Assets depreciation in Dynamics GP
Below is a view I have put together to get the monthly Fixed Assets depreciation amounts in Dynamics GP. For more detail on the Fixed Assets module, please visit my Fixed Assets Tables page. If you’re looking for more SQL code, you can find it on my GP Reports page. ~~~~~ CREATE VIEW view_FA_Depreciation AS /******************************************************************* view_FA_Depreciation Created on […]
Continue readingThere’s a new blog in town
Ron Wilson, who you may have seen posting on various Dynamics GP forums and newsgroups, has recently started a blog called Real Life Dynamics User. Ron has been busy posting tips and tricks, sharing his experiences with Dynamics GP and posting useful SQL scripts and views. Keep up the great work, Ron! For the Office Space fans out there, […]
Continue readingSQL view with AR apply detail
This has been a hot topic in the newsgroups lately and several people have asked me if I have any code for Receivables (AR) apply information in Dynamics GP. Below is a view that should help you get started if you’re building a report to show AR apply information in Dynamics GP. For more views like this, check out […]
Continue readingSQL view with all GL distributions for AR transactions
As a corollary to my SQL view with all posted Receivables transactions, below is a view that will add all the General Ledger distributions to the AR transactions. I made a few changes to the original AR transactions view, primarily to remove some columns that are typically not needed and add underscores to the column names so […]
Continue readingSQL view for Payables payment apply detail in GP
I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information. Below is a view that returns a list of all posted payments with details on how […]
Continue readingSQL view for all posted Payables transactions in Dynamics GP
Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded. For other SQL views on Dynamics GP data, please visit my GP Reports page. ~~~~~ CREATE VIEW view_PM_Transactions AS /*************************************************************** view_PM_Transactions Created Oct 12 2009 by Victoria Yudin – Flexible […]
Continue reading
Recent Blog Comments