Inventory snapshot – automatic

  • Inventory snapshot – automatic

    Posted by David-York on April 27, 2017 at 9:05 am
    • David York


      April 27, 2017 at 9:05 AM


      Does anyone know of a way to automatically track inventory on hand by storing a snapshot of each day’s quantities? Rather than running a historical stock status and adding everything up every day, I’m picturing an automatic data pull each day at noon, for example, and storing it in Excel without having to actively do it myself the hard way.

      The applications would be numerous, but I’m most interested in calculating a room’s capacity and tracking how much space our inventory is taking up over time.

      Is this possible? We also have Power BI, if that might make things easier than setting up an Excel report.

      Thank you,
      David York

      David York
      Allagash Brewing Company
      Portland ME

    • Barry Crowell


      April 28, 2017 at 8:55 AM


      I would use one of the SQL Inventory views from Victoria Yudin and create a SQL Job that runs everyday at noon to insert the inventory data into a custom table. I would then use Power BI to analyze your inventory data.  You may want to look at’s Synoptic Designer tool.  With the tool you can add a layout of your warehouse floor to use as a custom visual inside of Power BI.

      Barry Crowell
      Senior Business Consultant
      KTL Solutions, Inc
      Frederick MD

    • Beat Bucher


      April 28, 2017 at 9:49 AM

      Hi David,
      We do that on a weekly basis thru our SSRS scheduled reports that export the result to an Excel workbook on a shared network drive for the users.. This way they do have a weekly status of the inventory historical value based on the recevings.
      Use one of the many views that are available on the net or from Victoria as suggested by Barry.

      Beat Bucher
      Business Analyst, Dynamics GP MVP
      Ultra-Electronics Forensic Technology Inc.
      Montreal QC/Canada
      Montreal QC GPUG Chapter Leader
      GP2013R2 / MR2012 CU14

    • Joseph C. Markovich


      May 4, 2017 at 11:44 AM

      Hi David-

      We do something like this but on a monthly basis — using Extender and SmartConnect.

      There’s an Extender window linked to Item Maintenance. SmartConnect runs a bulk data load job based on a view I created (only because I forgot about Victoria’s excellent views on her blog). It’s scheduled to run at midnight the 1st of every month and populates the Extender detail window.

      The nice thing is all the data is stored in GP. You could easily dump this to Excel, create an Excel refreshable report or link it to PowerBI.


      Joseph Markovich
      IT Manager
      Okaya Electric America
      Valparaiso IN

    • Chris Adaline


      May 5, 2017 at 3:31 PM

      If you’re using multibin, it might be useful to capture all the detail in IV00112.  You could create a scheduled job to run something like this:

      USE CompanyDB
      DECLARE @sqlcmd nvarchar(100)
      set @sqlcmd = ‘SELECT * INTO IV00112_’+ CONVERT(VARCHAR(8), GETDATE(), 112) + ‘ FROM IV00112’
      EXEC (@sqlcmd)

      Then use Excel to access the tables.  The problem is that these can get quite big after a while.  I used to keep a couple years of monthly snapshots of the FIFO layers in IV10200 like this for analyzing average costing. 

      Chris Adaline

    David-York replied 7 years, 2 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

The discussion ‘Inventory snapshot – automatic’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!