Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Monday, November 28, 2016

Stock Count Macro Generator - Macro Essentials Series (Free Tool - Beta Version)


As part of the Macro Essentials Series, another tool is being delivered today which aims to make the data entry process into Dynamics GP easier and more efficient. The purpose of this tool is to do the following:
  • Retrieve a stock count Schedule from Dynamics GP
  • Fill out the actual stock quantity on the Excel Sheet, either manually or by considering a bar code reader solution for further automation
  • Generate a Macro in order to be enter actual quantities into Dynamics GP for a specific stock count schedule and calculate variances automatically. 



In order to use this tool, you need to follow the steps below:
  1. Run the SQL command on the GP company database, which creates a stored procedure that "reads" the stock count schedule details
  2. Open the Excel file, and adjust the ODBC connection by providing the SQL server name and required authentication to login 
  3. Fill out the Stock Count Schedule ID on the home page, and refresh the Excel in order to retrieve the stock count schedule details
  4. Fill out Actual Quantities on the Macro Page. 
  5. Copy the Macro column into Word (to preserve the code formatting), then into a Macro file. 
  6. Run he Macro on the Stock Count entry

SQL Script : 


CREATE PROCEDURE [dbo].[RetriveStockCount]
    @StockCountID AS NVARCHAR(MAX)
AS
    SELECT  CASE WHEN ROW_NUMBER() OVER ( ORDER BY A.STCKCNTID ) > 9 THEN 9
                 ELSE ROW_NUMBER() OVER ( ORDER BY A.STCKCNTID )
            END AS Row_ID ,
            A.STCKCNTID StockCountID ,
            A.DOCDATE DocumentDate ,
            CASE A.STCKCNTSTTS
              WHEN 1 THEN 'Available'
              WHEN 2 THEN 'Started'
              WHEN 3 THEN 'Entered'
              ELSE ''
            END AS StockCountStatus ,
            B.ITEMNMBR ItemNumber ,
            B.LOCNCODE LocationCode ,
            B.CAPTUREDQTY CapturedQuantity ,
            C.ATYALLOC AllocatedQuantity
    FROM    dbo.IV10300 AS A
            LEFT OUTER JOIN dbo.IV10301 AS B ON B.STCKCNTID = A.STCKCNTID
            LEFT OUTER JOIN dbo.IV00102 AS C ON C.ITEMNMBR = B.ITEMNMBR
                                                AND C.LOCNCODE = A.LOCNCODE
    WHERE   A.STCKCNTID = @StockCountID
            AND C.RCRDTYPE <> 1
    ORDER BY B.ITEMNMBR ,
            A.LOCNCODE;
GO




On the Macro Generator file, you need to fill out the "Stock Count Schedule ID" in order to retrieve its details:





You can download the Stock count Macro generator files from this link.


Best Regards,
Mahmoud M. AlSaad