Hi everyone!

I’m excited to share some additional functionality that’s been added to the Export/Import Stock Count to Excel feature as it pertains to Serial and Lot numbers in the 2022 Year-End Update.

If you haven’t heard about this functionality, please review the blog from the 18.3 release in October 2020. 

Microsoft Dynamics GP Fall 2020 – Export/Import Stock Counts to Excel - Microsoft Dynamics GP Community

At the time of release the process was seamless when it came to non-tracked items.  You could easily import the stock count and complete the whole process with very little interaction in the Stock Count Entry windows.  However, tracked items with count changes weren’t as seamless as we hoped and still required a bit of user intervention after the data was imported. 

I’m excited to share that importing stock counts for serial/lot tracked items are now as seamless as non-tracked items!  Beginning with the GP2018 2022 Year end update, some additional functionality has been added to make the process of counting and recording Serial and Lots changes a whole lot smoother, giving us even more flexibility when using the export/import feature. 

Now with this new functionality, when you have Serial/lot items with count changes, you can populate the excel form used to import with the Quantity Counted and a Counted Status columns to indicate what was found and what wasn’t during the count. Indicating in the excel spreadsheet what you did and didn’t find allows us to properly update the required fields in the Stock count windows once imported, thus not having to have user intervention to set specific fields before posting.

LOT NUMBERED ITEMS:

For Lot items you must enter a TOTAL QTY for the item in the top level of the Counted QTY field (aka 45).  And in order to have any quantity entered for the lot, a quantity needs to be added on at least one of the same lot lines. (i.e. something for Lot A and something for Lot B). The system will sum the quantities for Lot A and start matching against the earliest date QOH (QUANTITY ON HAND) to make the adjustment.  The final entry for Lot A will have the variance (and if needed will move up from there.)

 

Below is how the Excel spreadsheet would look after adjusting the quantities and before the import:

  • The quantities in bold in the examples below are what you would/could enter.
  • Assume we counted a total of 45 on hand. That number would go at the top level for the item. 
  • If you counted 32 of Lot A you have two choices:
    • Example 1: Enter 32 for the first Lot of A and leave the rest blank.
    • Example 2: If you counted 32 you can split it up by adding 10, 10, 10 and 12 down for each lot A.
  • In both cases you end up with a variance of 5 total
    • +2 for Lot A
    • +3 for Lot B

 

After import results for both scenarios are the same:

SERIAL NUMBERED ITEMS:

A few key fields you will want to be aware of and understand for Serial items:

  1. There has been added a Qty On Hand column, a Counted Qty column and changes to the existing Serial Count Status
  2. Qty on hand will be what is on hand in GP.
  3. Enter the quantity you counted into Counted Qty

For Serial items, you would enter the TOTAL QUANTITY counted on hand at the top level, and then in the Counted Status column you would enter a 1 if you find the serial number or a 0 if the serial number is missing. The counted status is now taken into account using the following values:

 

COUNTED STATUS:

  • Blank = ‘Not Set’
  • 0 = ‘Not Found’
  • 1 = ‘Counted’
  • No other values are valid

 

In addition, GP will adjust if the user enters a lot or serial number that does not exist.  That would create a positive variance + and would create an appropriate history record when the stock count is posted.  

For a regular item, there will be just one row, but we will include the Qty on Hand for those items also.

Below is an example of both a Lot and Serial item on a spreadsheet for your reference.

Hope you are all as excited as I was to see this feature added!

Angela Ebensteiner | Sr Technical Advisor | Microsoft Dynamics GP