Inventory Reconcile, takes too long

  • Inventory Reconcile, takes too long

    Posted by DSC Communities on February 20, 2017 at 8:29 pm
    • Clark Patterson


      February 20, 2017 at 8:29 PM

      Company that imports inventory items and transactions where most item are basically a one off.  They are complaining about how long running Inventory Reconcile takes.


      We have implemented Profad Company Data Archive and removed several years of data. Now Profad does not support getting rid on the item cards, but I have marked them all inactive/discontinued (about 670,000 items) 

      Would eliminating those items in the live company decrease the time for the Inventory Reconcile process?


      My guess, if it is driven by the transaction records, no.  However if the process is driven by the Item Cards, then yes.   What say you?

      Clark Patterson
      Chicago IL

    • David Musgrave


      February 21, 2017 at 12:48 AM

      Hi Clark


      The item reconcile works on the range of specified items. If not range is selected it will run on all items.


      Even if there is minimal data per item, it is still going to check every item in the table unless you limit the range.


      You need to decrease the records in the table or select a range to improve performance.


      If the item numbers you create can be prefixed with a four digit year, then


      1. You can limit reconciling to the recent year’s items only.
      2. You can manually archive old items.


      Do they have a naming convention that could help like this?


      That said ……..


      Creating new items for every transaction is not the best way to use GP.


      You would be better off with a few custom items that you use the SOP Line Comments to put the specific details on.


      Or using non-inventoried items.





      David Musgrave MVP, GPUG All-Star

      Managing Director
      Winthrop Development Consultants

      Perth, Western Australia

    • Amber Bell


      February 21, 2017 at 11:19 AM

      I wanted to add an idea based on David Musgrave’s post:

      Could they change their item numbers using the PSTL Item Modifier after they are no longer used? Then they could use the ranges like David mentioned.

      They could export from SmartList and do a quick formula for the new item number like

      =”ZZZ-“&___ (the cell)

      You can do this in bulk with an import of a .csv file (2 columns, no header, Column 1 = old number; Column 2 = New Number0

      This would move all those items to the end of the item number list. Then they could select the items but stop before the ZZZ items.

      Amber Bell
      Training Dynamo LLC
      Twitter: @trainingdynamo

    • Mark Polino


      February 21, 2017 at 7:58 AM

      Have they tried the PSTL version of item reconcile? I’ve seen that significantly reduce the time.


      Mark Polino
      Director of Client Services
      Altamonte Springs FL

    • Beat Bucher


      February 21, 2017 at 8:20 AM

      Hi Clark,
      Agree with Mark.. PSTL has it’s own version of Item reconcile which is optimizized and runs quicker by a factor of at least 5 to 10 times.

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

    • Thaddeus Suter


      February 21, 2017 at 8:44 AM

      I’ve seen situations where the IV Reconcile took five days and cutting it to 18 hours is still unacceptable. I believe there is still a bug in the IV Reconcile where it reconciles Items flagged Inactive. It should only reconcile Items active and or Discontinued. Not Inactive.

      However, keep in mind, the IV Reconcile time is driven by the transaction volume and the IV00102 table size. Not so much the IV00101.

      Normally there is no need to mark “Include Item History” when reconciling IV as the objective is to update/correct allocations, on order amounts, on hand quantities etc. based on open records, not go thru the history transaction tables.

      The strategy often used where there are many “One Off” items is to remove the records from the IV00102 as soon as they are no longer needed but keep the IV00101 records as discontinued or inactive. This is also done for Stock Status reporting, Cycle Counts etc to improve performance

      A SQL job does this IV00102 delete task daily based on Last Order Date, Last Receipt Date, Quantity on Order = 0, Quantity On Hand = 0, Qty Back Order = 0, ….

      Set your company rules for Last Order Date or Last Receipt Date such as 30 days or even less and whether you test for Discontinued/Inactive in the IV00101 and delete them as they age into the range.

      In the example mentioned, you should be able to reduce the IV00102 by 95% if your items are one offs.

      There is no downside as IV Checks links can bring back any IV00102 that should have been kept and if an item gets purchased/ordered again the IV00102 record can be added on the fly. on integration. Set integration flag to create IV00102 if not found.

      Thaddeus Suter
      Retus, Inc
      Helotes TX

    • Thaddeus Suter


      February 21, 2017 at 8:59 AM

      David mentioned setting the NONINVEN flag to 1 on integration and this is a good strategy for one off items as well.

      Normally the reason people don’t use the Non inven flag is because you only get two distributions. Sales and Receivables and they want to distribute the real cost as well not using default accounts

      Well there is a slick customization we’ve done with a SOP10200 trigger where you get all four distributions. Sales, COGS, IV(Offset) and Receivables for NONINVEN that ties back to the correct costs and distribution accounts in POP

      This customization goes beyond the scope of this thread but NONINVEN is very useful when you can fully distribute on SOP Posting with actual costs and accounts.

      Thaddeus Suter
      Retus, Inc
      Helotes TX

    • Lucas Miller


      February 21, 2017 at 11:01 AM

      Just to add to the conversation, last summer I worked with a customer that was seeing the reconcile process for a single item took over 30 hours due to the number of sites it existed in.

      Working with the Development team we replaced some of the Dexterity calls that are made within the reconcile process with a SQL query and we were able to get that same item down to 4-5 hours.

      This would have been released in the June hotfix last year.  This specific customer was on GP 2015, but was also ported to GP 2016:

      Microsoft Dynamics GP Mid-year tax update released!! – Microsoft Dynamics GP Community

      If you haven’t already patched to the build where this was included I would recommend that you update to the latest patch for your version.

      Lucas Miller
      Fargo, ND

    DSC Communities replied 6 years, 7 months ago 1 Member · 0 Replies
  • 0 Replies

Sorry, there were no replies found.

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!