Inventory Upgrade Procedures

Pre-Upgrade Procedures

ℹ️ These steps will help streamline the upgrade process.

1. Export the Inventory Default View for past financial years (at least three) in case they were used to draw up Financial Statements.

2. Check and resolve Negative Stock Quantities as displayed in the In Stock (column) in the Inventory report: Create Stock Checks to set the quantities to zero.

3. Delete empty Categories, Classes, and Locations (i.e. blank lines).

4. Set unused Items to inactive, and delete blank Items.

5. Units:

   1. Check whether the selected Base Units are practical.

   2. Resolve unsuitable Units which result in huge quantities (e.g. counting in mm instead of m). Change the Base Unit to a more suitable unit or uncheck the Stock Check checkbox.

   3. Dealing with off-cuts:

      • Create a separate Goods Returned transaction type (Called 'Offcuts') to use for stock that is returned from production. In this case, the items are viewed through the Goods Returned item list and moved to Work Orders as they are used by changing the source transaction. This system is only practical if there are only a few offcuts or if goods are carefully stored and labelled.

6. Check the number of decimals allowed for Inventory by navigating to Main Menu > Setup > Settings > General (tab): Items & Inventory > Decimals used to display Inventory quantity (0-4).  If they are not using all the decimal places, reduce the decimal quantity. 

7. Check Custom Actions and Reports for potential issues. 

   2. • Note: The stored procedures SELECT_STOCK_QTY and STOCK_SELECT_STOCKITEMQTY still work the same as before.

8. Check that there are no Pending Stock Checks. The upgrade script sets them all to Complete. If they cannot be set to complete, delete them and create them again in the new version.

9. Export the Inventory Report to compare the inventory quantities to the upgraded version.

   2. • Tip: Export per category so it is easier to compare and find errors by comparing the category totals.

Understanding the Improvements

1. Stock Counts:

   • OLD

     • Stock checks were required, and stock counts were done from the latest stock check to date. 

     • When a new item was created and added to inventory, a stock check was automatically created that could not be deleted. 

   • NEW

     • Stock is counted from when the item is created to date. 

     • Stock checks are optional.

2. Base Units / Estimated Quantities on hand:

   • OLD

     • Stock can be counted for any unit. 

     • Stock was counted in the base unit to determine the quantity that should be in stock.

     • If the stock-check checkbox was not checked for the base unit, no estimated quantity was displayed.

   • NEW

     • Stock can be counted for any unit. 

     • The quantity in stock is calculated using the estimated count: Count (Est Qty).

     • Only one unit can have the Count (Est Qty) selected. All other units can be blank (not counted) or Count.

     • If Count (Est Qty) is selected for an additional unit, the previously selected unit is changed to Count once the changes are saved.

     • The Stock Check options on the Units tab are:

       • Blank: The unit is not used in Stock Checks.

       • Count: The unit is used to count only.

       • Count (Est Qty): The unit is used to calculate the Est Qty and for counting. Only one unit can be set to this option. 

         • Note: The upgrade script will update Base Units to this option.

3. Recording Stock Movements:

   • OLD

     • All the inventory data, except for stock checks, was stored in a table named STOCKDATA. 

     • Stock movements from transactions were recorded based on their statuses. 

     • When the stock was counted, the last stock check was found, and the data in the STOCKDATA table that was created since the date of the last stock check was added to it. 

     • Any changes to transactions prior to the last stock check were ignored. 

   • NEW

     • Stock-check data are added to STOCKDATA.

     • Counts are calculated by adding all the applicable data in STOCKDATA. For example, Goods Received add to the count, Goods Issued deduct from the count. 

     • Changes to transactions that predate a stock check are included. Warning messages are displayed to alert users that the transaction predates a stock check.  

4. Stock Check Display and Procedures

   • OLD

     • Only the last stock check was displayed.  

   • NEW

     • You can filter by date and view all the stock checks.

     • A Stock Check Update now only updates the estimated quantity that should be in stock (Est Qty).

     • After a stock check transaction is created, the date cannot be changed. The date can only be changed on the form that is used to select items to create stock checks, and as before, stock checks must be created for yesterday or earlier. 

     • Functionality to Export and Import Stock Checks in .csv format has been removed.

     • You can only Stock Check a Batch if it is not empty. If you want to bring an empty batch back into Stock, you must create a Receive transaction.

     • Adding items:

       •  All the items and units for which Count or Count (Est) was selected are added to the dropdown list for selection.

       • An In Stock column displays the quantities in stock to facilitate checking for negative stock.

       • Stock Checks are similar to transactions, and each entry has a Status.

       • Once created, the Count Qty is set to zero if the Estimated Quantity is less than zero.

5. Marking Items as Inventory Items

   • OLD

     • The Inventory checkbox was checked.

   • NEW

     • In the item's header, an inventory option is selected as follows:

       • None: The item is not an inventory item.

       • Fixed Locations: The item is an inventory item and stored in fixed locations. Locations must be added on the Inventory (tab).

       • All Locations: The item is an inventory item and stored in all or any inventory locations. Locations may be added to the Inventory (tab), but will also be added as the item is used in transactions.

Test & Post Upgrade Procedures

1. REBUILD the inventory to update the STOCKDATA table (Inventory Report > Menu (button) > Rebuild).

2. Items > Units (tab): Check whether all the units need to be counted, and that an appropriate unit is selected for the estimated stock on hand (Count (Est Qty)).  

   • Note: A custom script can be created based on the fixes required in the database.

3. Draw an inventory report and compare it to the previously exported inventory report. 

   • Identify and resolve any large variances and negative quantities.

     1. Right-click the entry and select View Item Summary from the context menu.

     2. Click Inventory (tab) > Movement (tab).

     3. Look for the transaction that is causing the problem and edit or delete the transaction, if allowed by the customer.

4. Where stock reports are unusable (massive quantities and many negative quantities): 

   • Note: A custom script can be created to record the quantities on hand, delete all the old stock checks, and create new stock checks to recapture the quantities in stock.

5. Where stock reports need minor tweaking: 

   • Draw a stock check report and resolve any exceptions: Actions (button) > Update Stock Checks.

     1. If you are unable to resolve an issue, check the Units and especially the conversions. 

        • There may be a conversion that is set up incorrectly. 

        • The wrong unit might be selected for the Count (Est Qty). 

     2. Remember to Rebuild the inventory if unit conversions are changed.

6. Resolve Stock Check Errors: If the Inventory Report is messy and it appears that it is not being used correctly (e.g. View Item Summary > Click Inventory (tab) > Movement (tab)), consider deleting all the stock-check entries. Record the stock quantities on hand before deleting the records. 

Related Topics

• Reference: Stock Checks

• Tutorials: Stock Checks

• Reference: Inventory Reports Default View

• Reference: Item Creation and Editing

• Tutorials: Item Creation