Tuesday, August 29, 2017

Victoria Does it Again - Unposted Check Report

Our own Victoria Yudin has once again hit a home run with her view revealing unposted checks in Dynamics GP. She has solved the problem of the "... universally hated ..." Check Batch Edit List report. You can read her post, and get her code here: https://victoriayudin.com/2017/08/29/sql-view-for-unposted-checks-in-dynamics-gp/

I'm always afraid links will break, so I have copied her post below. Be sure to check her site for any modifications she may make in the future. The little man pictured at the start of this post is from Victoria's Web site, she always has the best graphics
Live the dream!
Thank you Victoria -

SQL view for unposted checks in Dynamics GP

I have been asked more than a few times for a script that shows uposted checks and their apply detail. This can be helpful to replace the Check Batch Edit List report, which is universally hated by all GP users I have spoken to.

create view view_unposted_check_batch
as  -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

-- view_unposted_check_batch

-- Created Aug 29, 2017 by Victoria Yudin, Flexible Solutions

-- For updates see https://victoriayudin.com/gp-reports/

-- Shows details of unposted checks and how they are applied

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
rtrim(p.BACHNUMB) Batch,

rtrim(p.CHEKBKID) Checkbook,

rtrim(p.PMNTNMBR) [Payment Number],

p.DOCDATE [Check Date],

rtrim(p.CHEKTOTL) [Check Amount],

rtrim(p.VENDORID) [Vendor ID],

rtrim(p.VENDNAME) [Vendor Name],

rtrim(p.ADDRESS1) Address1,

rtrim(v.PYMTRMID) [Payment Terms],

rtrim(a.DOCNUMBR) [Invoice Number],

a.DOCDATE [Invoice Date],

a.DocDueDate [Due Date],

a.AMNTPAID [Amount Paid],

a.DISTKNAM Discount,

rtrim(a.TRXDSCRN) [Invoice Description]

from PM10300 p

left outer join PM10201 a


inner join PM00200 v


where p.BCHSOURC like '%check%'

and AMNTPAID <> 0

and CHEKTOTL <> 0

-- add permissions for DYNGRP
grant select on view_unposted_check_batch to DYNGRP

Wednesday, August 23, 2017

Additional Dynamics GP Tools from Microsoft- includes 2016

Below you'll find a list of additional tools available from Microsoft that are free for the downloading. This is a copy of the document displayed on Microsoft's web site. You will need access to Customer/PartnerSource in order to download them. These tools solve common aggravations that we have when we need the software to behave just a little bit different than it does. Don't pay for that customization until you check this list. Who knows, it might have been there waiting for you to download it all the while. Please note that there is no guarantee that these tools will be upgraded with new releases. Please read the Support Information section at the bottom.

Do​wnload / File type (size) /
Release Date
AutoDim2013.zip ​ZIP (112KB)   2/27/2013AutoDim2015.zip ZIP (112KB)    3/2/2015AutoDim2016.zip ZIP (112KB)    5/3/2016
Allows you to launch Microsoft Dynamics and run
one or more integrations or integration groups and have Microsoft Dynamics GP exit upon completion of the integrations.This tool also supports
integrating into several different companies.
AutoPost2013.zip ZIP (189KB)  2/27/2013AutoPost2015.zip ZIP (189KB)   3/2/2015AutoPost2016.zip ZIP (189KB)   5/3/2016
Allows you to call a COM DLL to post Sales Order
Processing, GL Transaction Entry, IV Transaction
Entry and RM Cash Receipt Batches. 
**Requires VB6 runtime to register the dll, not installed by default on a new operating system**
AverageDaytoPay.zip     ZIP (98KB)  2/24/2016
Calculates YTD & LTD Average Days to Pay for Customers.
Note: The same install can be used for
Microsoft Dynamics GP 2010,
Microsoft Dynamics GP 2013,
Microsoft Dynamics GP 2015, and
Microsoft Dynamics GP 2016
ZIP (284KB) | 2/27/2013
DetailPayrollActivityTracking_2013SP2.zip ZIP (287KB) | 9/18/2013 (or later)DetailPayrollActivityTracking_2015.zip
ZIP (284KB) | 1/27/2015
ZIP (284KB) | 5/3/2016
Allows you to track employee changes made in
Microsoft Dynamics GP.
         Previous Version Installs
ZIP (244KB) | 3/26/2012
ZIP (285KB) | 3/26/2012
Allows a receipt tolerance percentage for
quantities. A setup window allows the user to enter a tolerance percentage, that percentage is then
used when a Purchase Order is received. The
highest quantity that can be received is the original quantity ordered plus the percentage amount.
NOTE: This tool is included in Microsoft Dynamics GP 2013.
ZIP (308KB) | 3/2/2015
ZIP (311KB) | 3/2/2015
ZIP (311KB) | 5/3/2016
When invoicing a receipt in POP, this tool will
check the previous quantity invoiced and the
quantity returned for the line item. The maximum
quantity allowed to be invoiced is based the
following formula: Quantity Shipped minus
(Previous Quantity Invoiced plus Quantity
Returned). This tool will not allow invoicing of more than this amount.
ZIP (394KB) | 2/27/2013
ZIP (394KB) |  3/2/2015
ZIP (394KB) |  5/3/2016
Allows the user to Mass Apply posted RM Credit
Documents to posted RM Debit Documents. The
user can select a range of Customer, Debit
Document Dates, Credit Document Dates, and a
Credit Document Type restriction if desired.
ZIP (457KB) | 2/27/2013
ZIP (457KB) | 1/27/2015
ZIP (457) | 5/3/2016
Allows drop ship sales orders to be transferred to
invoice prior to invoicing the purchase order.
ZIP (347KB)   | 2/27/2013
ZIP (347KB)  | 3/2/2015
ZIP (347KB) |  8/31/2016
Allows the user to define custom sorting options to display line items in Sales Order Processing.
This tool will reorder the line items of Purchase
Orders generated by the SOP to POP transfer
process to be in the same order as they originally
were on the Sales Order document(s).
ZIP (63KB) | 8/12/2013
ZIP (66KB) | 3/2/2015
ZIP (64KB) | 8/31/2016
This tool allows the Tax Schedule ID in the Sales
Customer Detail Entry window to be used for the
Sales document header and line items.
SpTotals.zip   ZIP (63KB) | 6/4/2015
Allows for absolute values in the Safe Pay file if your bank requires it.
Mariano Gomez explains it as: This chunk file is supposed to add up voids and checks in the summary line of the file by altering the Net Total functionality, which, without the customization, subtracts total voids amount from the total checks amount.
Note: The same install can be used for
Microsoft Dynamics GP 2010,
Microsoft Dynamics GP 2013,
Microsoft Dynamics GP 2015 and later
Changer Scripts 10.0
ZIP (6KB) | 4/22/2013
Changer Scripts 2010
ZIP (4KB) | 4/22/2013
This tool allows you to change the Contract
Number, Contract ID, Project Number, Project ID
and Cost Category ID for Project Accounting

Tuesday, August 15, 2017

Learning Macros

Interested in getting started with Macros?
Today's the day you can start! GPUG is offering a one-hour Webinar Today (Aug 15, 2017) at 2pm CST entitled 'Getting Started with Macros'.
Join in the fun and learn what a macro is and how you can use them.
  • Learn how easy macros are to create and to even modify using Notepad.
  • Have you ever tried to use a macro to print a report to screen, just to have it shrink up on you?
  • Have you ever wanted to copy and paste a value?
  • Are you familiar with the concept of nesting macros?
  • Would you like to run check links or reconcile overnight?
Sign up for the Webinar today and I will show you some macro tips that will put you on the path of becoming a macro superstar.


Don't forget 2:00 - 3:00 Central time!