This view is a little bit of a twist on my previous SQL view for inventory quantities on hand. The results of this view will give you a list of your Microsoft Dynamics GP inventory items, current quantities and the last sales and purchase dates along with the vendor. For other SQL views on Dynamics GP data, please visit […]
Continue reading
Archive | Dynamics GP RSS feed for this section
SQL view for Payables payment apply detail in GP
October 22, 2009 
108 Comments
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
October 12, 2009 37 Comments
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 readingHistory of Dynamics GP
September 30, 2009 2 Comments
Do you still call it Great Plains because you’re so used to it? Or is Dynamics GP the only name you’ve ever used? In either case, you may find looking over the timeline of Dynamics GP that DynamicsWorld UK put together very educational and interesting. I have been working with GP as a customer since the DOS […]
Continue readingSQL view for all posted Payables payments in Dynamics GP
September 11, 2009 13 Comments
This view is in response to more than a few inquiries I have had for a vendor check report. This view brings in all payments, not just checks, so if only checks are needed, add the following to the end of the WHERE clause: AND P.PYENTTYP = 0 For other SQL code, please visit my […]
Continue readingWhat’s with all these dates?
September 8, 2009 21 Comments
There are many different dates stored in Dynamics GP. This is typically a good thing, my philosophy is that it is always better to have more data than you need and not the other way around. However, it can get confusing rather quickly since a lot of the labels for the dates sound very similar. I will attempt […]
Continue readingSQL view for all unapplied Receivables transactions in Dynamics GP
September 5, 2009 29 Comments
Here is a SQL view that will return all unapplied Receivables transactions in Dynamics GP. This will calculate how many days overdue unapplied transaction are. If something is not overdue, or if it is a credit transaction (payment, credit or return) the Days_Past_Due will be zero. For more SQL code like this, please visit my GP Reports and […]
Continue readingService Pack 4 for Dynamics GP 10
August 25, 2009 16 Comments
Service Pack 4 for Dynamics GP 10.0 has been released. There is quite a long list of fixes, you can see them all in the Service Pack 4 Installation Guide. Starting with GP 10.0 any service pack or hotfix now requires an update of modified forms and reports using GP Utilities. Detailed steps for doing this […]
Continue reading SQL view for all posted GL transactions in Dynamics GP
August 11, 2009 123 Comments
Below is a SQL script to create a view showing all posted General Ledger transactions in Dynamics GP. This has the common columns asked for on reports, but you can certainly add your own as needed. For additional SQL code, please visit my General Ledger SQL views page or the GP Reports page. A few notes on this view: Year-end […]
Continue readingDynamics GP Windows 7 and Server 2008 R2 compatibility announced
August 10, 2009 7 Comments
Just as the Windows 7 and Windows Server 2008 R2 are released, Microsoft announces that Dynamics GP 10.0 is already compatible with them. No service pack required. While my memory is not what it used to be, I do not recall any other new operating system that an existing version of GP was immediately compatible with. We have had […]
Continue reading Dynamics GP Trial Balance in Excel using FRx
July 17, 2009 43 Comments
While I work with SQL and Crystal Reports every day, there are some reports that are just easier created elsewhere. One example is a GL Trial Balance that you want to export to Excel. There are many ways of handling this requirement, I like using FRx for it. Below are steps by step instructions on how to […]
Continue readingNew policy for Dynamics GP service pack support
June 26, 2009 1 Comment
Microsoft recently announced that the Dynamics products service packs will now be supported for 24 months after a new service pack is released. Previously service packs were supported for 12 months after a new service pack release. The new policy with key dates is outlined on Microsoft’s Longer Support Timelines for Key Microsoft Dynamics® Service Packs page. What this means to […]
Continue readingSQL view for inventory quantities on hand
June 18, 2009 27 Comments
I haven’t had much time to blog lately as we have added functionality to our GP Reports Viewer product that allows seamless replacement of SOP reports in Dynamics GP and the amount of interest has been overwhelming. However, I just needed to create a new SmartList to show current inventory quantities on hand with their costs and I […]
Continue reading SQL view with all SOP line items in Dynamics GP
May 17, 2009 72 Comments
Here is a script that I have found useful on numerous occasions – it returns the lines items for all SOP (Sales Order Processing) transactions, both posted and unposted. This view will work with either SmartList Builder or Crystal Reports. For more scripts like this, take a look at my GP Reports page. ~~~~~ CREATE […]
Continue readingSQL view with all posted Receivables transactions
April 24, 2009 30 Comments
Below is code to create a SQL view that returns all the posted Receivables transactions in Dynamics GP. I have had an abbreviated version of this view on my GP Reports page for a while, but I cleaned it up a bit and gave it some more friendly column names. I also added values for the commonly used […]
Continue readingHow to use a SQL view in SmartList Builder
April 20, 2009 43 Comments
Something I have been asked more than a few times is how to use a SQL view in SmartList Builder to create a new SmartList. I am going to walk through this step by step using Dynamics GP 10.0 SP 3 and SQL Server 2005 SP 3: STEP 1 – create SQL view To start […]
Continue reading Where did my transaction go?
April 11, 2009 7 Comments
This is a common question, especially with new users of Dynamics GP, and I was recently reminded of it by a post in the Microsoft Dynamics GP Newsgroup. While every transaction type will be slightly different, there is a basic lesson that is important for all GP users: beware of the Delete button. Most GP actions are updated automatically […]
Continue readingSQL view with security resource details for Dynamics GP
March 20, 2009 38 Comments
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 readingMy Last Book
Dynamics GP 2013 Implementation
My Previous Book
Dynamics GP 2010 Implementation
-
Using credit cards to pay vendors in Dynamics GP
-
SQL view for Payables GL distributions in Dynamics GP
-
SQL view for all posted GL transactions in Dynamics GP
-
SQL view for Payables payment apply detail in GP
-
Sales Transaction Entry GL distributions in Dynamics GP
-
SQL view to show AP apply information in GP
-
Year end close in Dynamics GP
- SQL view with AR apply detail
-
Granting access to a new SmartList in Dynamics GP
-
SQL view with all SOP line items in Dynamics GP
-
SQL view for unposted checks in Dynamics GP
-
SQL view for rolling 12 months of sales by item by site in Dynamics GP
-
Dynamics GP sales amounts and quantities for rolling 12 months
-
Updating Dynamics GP data directly in SQL
-
Did you know you can change Void dates on Payables transactions?
-
Refreshable Excel report for monthly actuals vs. budgets in Dynamics GP
-
SQL code to show top X percent of vendor data
-
Discount from Packt Publishing
-
Refreshable Excel report for historical GL trial balance in Dynamics GP
-
Refreshable Excel report for monthly GL budget in Dynamics GP
-
Victoria Yudin: Hi Matthew, This is a limitation in GP. "Why" is ...
-
Matthew: Why does "Sales" type distr...
-
-
Kevin Mackol: Does this still work on new...
-
-
Alex: Hi Victoria, Hi Victoria, Hi Victoria,Thank you so mu...
Victoria Yudin 
Recent Blog Comments