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:
- Run the SQL command on the GP company database, which creates a stored procedure that "reads" the stock count schedule details
- Open the Excel file, and adjust the ODBC connection by providing the SQL server name and required authentication to login
- Fill out the Stock Count Schedule ID on the home page, and refresh the Excel in order to retrieve the stock count schedule details
- Fill out Actual Quantities on the Macro Page.
- Copy the Macro column into Word (to preserve the code formatting), then into a Macro file.
- Run he Macro on the Stock Count entry
SQL Script :
CREATE PROCEDURE [dbo].[RetriveStockCount]
@StockCountID AS NVARCHAR(MAX)
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 ,
WHEN 1 THEN 'Available'
WHEN 2 THEN 'Started'
WHEN 3 THEN 'Entered'
END AS StockCountStatus ,
B.ITEMNMBR ItemNumber ,
B.LOCNCODE LocationCode ,
B.CAPTUREDQTY CapturedQuantity ,
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 ,
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.
Mahmoud M. AlSaad