Inventory snapshot – automatic

  • Inventory snapshot – automatic

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

      Member

      April 27, 2017 at 9:05 AM

      Hello,

      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

      Member

      April 28, 2017 at 8:55 AM

      David,

      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 sqlbi.com’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

      Member

      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
      +1-514-489-4267
      @GP_Beat http://dyngpbeat.wordpress.com/
      Montreal QC GPUG Chapter Leader
      GP2013R2 / MR2012 CU14
      ——————————
      ——————————————-

    • Joseph C. Markovich

      Member

      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.

      -Joe

      ——————————
      Joseph Markovich
      IT Manager
      Okaya Electric America
      Valparaiso IN
      ——————————
      ——————————————-

    • Chris Adaline

      Member

      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
Now

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!