Your first call should be to your reseller. There are some balancing utilities that can be run that should correct the issue. One question, what is the valuation method? FIFO, Lot or Serial? To correct something like this it is best to use a trained consultant who understands the entire process.

I would also suggest you discuss upgrading to a current supported version such as 2016 or 2017 asap. The version you are on had several product updates that fixed issues with inventory so unless you are on the 4.40 product update 10 or a higher version you may experience further inventory issues that are fixed in current versions.

Thanks guys, yes, we're planning to go live with 2016 premium Monday 12/19/2016. My reseller has told me nothing can be done and not to worry about this issue, and I'm inclined to agree. Its just a clearing warehouse so no product lives there permanently. We just bill thru it to control cost on certain things. But the boss (type A) wants them cleared so I was looking into options.

Assuming I understand what you are describing (inconsistency between various files), the inventory balancing utilities mentioned above really should be used to make sure everything is synced before migrating as this can cause later problems.

On the other hand if you are showing inventory records with 0 quantities that are still showing a $ value, you should be able to clear those out by doing an inventory adjustment transaction to put in a quantity of 1 with 0 cost. Update. Then do a second inventory adjustment transaction of -1 and the amount of the cost that needs to be removed. Note that using DFDM to adjust the warehouse records directly won't work, because the next time you do a rebuild of inventory the error will simply be recreated from the transaction activity.

Hey Tom, You are correct about the symptom I think. My main problem is I have an item with like 50 quantity in this warehouse, according to the Inventory Trial Balance report. But if I look at the item in inventory, it shows 0 on hand. So I'm unable to adjust a negative quantity into the warehouse to make it zero, because Sage thinks i'm driving the inventory negative. That's why I posted to you users, hoping someone had a creative way of using the software itself to get around it. Trust me, I'm duly scared of DFDM!!! Thanks for the input all.

Here is some useful information from the Knowledge Base:

Cause


The standard Sage 100 ERP reports are the Inventory Valuation Report, Inventory Stock Status Report, Trial Balance Report, and Detail Transaction Report from the Inventory Management Reports menu.

Inventory Valuation and Stock Status Reports are not date sensitive. These reports are perpetual inventory reports, as of the moment run, and should not be used to reconcile to the General Ledger.
Trial Balance and Transaction Detail Reports in Inventory are date sensitive and use the same posting date as the General Ledger. The Trial Balance and Detail Transaction reports should be used for reconciling to the General Ledger.

As with any reconciliation, you may encounter situations where the Trial Balance Report in Inventory does not balance to the corresponding account in the General Ledger. Sage Software Customer Support cannot review non-standard reports, nor help to reconcile to the General Ledger.


Resolution


Report/Table features:

Trial Balance and Valuation by Period reports use the IM_PeriodPostingHistory table.
This table contains the BeginningBalance, PeriodChangeQty and PeriodChangeDollarAmts.
This should match the Detail Transaction Report when run for the same period.
These reports should be run far into the future to capture any future period changes when comparing with the Stock Status, Valuation Reports and GL Inventory Accounts.
Detail Transaction Report uses the IM_ItemTransactionHistory table and provides detail records for the Trial Balance and Valuation by Period for the same period.
Valuation Report uses the IM_ItemCost table.
The Stock Status Report uses the IM_ItemWarehouse table.

Factors that can cause the Inventory Reports to not match each other and be out of balance with the Inventory Account in General Ledger.

Turning off Integration to General Ledger from any module that posts to Inventory.
Connectivity lost, errors and hardware malfunctions during update from a module register causing entries to be lost.
Clearing the Daily Transaction Register postings before updating the Daily Transaction Register. Postings are made to Inventory during the subsidiary module update but not to the General Ledger history.
Posting to wrong accounts.
Example: In Common Information, Main menu, Miscellaneous Item Maintenance, one or more Miscellaneous or Charge type item may have an "Inventory" account entered. However, reports in Inventory Management module do not track miscellaneous items or charges. Any "Inventory" accounts entered in Miscellaneous Item Maintenance should not be the same as those used for true Inventory Management Item Codes.
Example: In Inventory Management, Setup menu, Product Line Maintenance, one or more product lines may have had an inventory account number in the wrong field (such as putting in an Inventory account for the Cost of Goods Sold field). Or, a non-inventory account number was entered for the Inventory field.
Example: In Accounts Payable, Main menu, Invoice Data Entry, users may have posted a vendor invoice to an Inventory account.
Note: One way to check for this is to open General Ledger, Reports menu, General Ledger Detail Report and specify a Source Type of "Module Registers" and specify for Account Number an Inventory account, then Preview the report to see if any postings were made to Inventory from the Accounts Payable module.
Rounding issues, if the decimal precision is set to more than 2 characters for the unit cost.
Restoring some files and not the entire MAS_XXX (XXX=Company Code).
Editing of data files using Utilities.
Issues in data from prior versions
Not closing modules in a timely manner in versions prior to 4.40.
Posting to a closed period in versions prior to 4.40.
Use of the 'Fix' button in 3.x versions.

The following is a list of things to be aware of when reconciling the Inventory Reports to the corresponding GL Account(s):

Look at all areas where posting accounts are required. Verify that the Inventory Account is being used wherever it is required, and incorrect accounts are being posted to.
Verify the GL accounts setup for postings is not using the same GL account for the debit posting as the credit posting causing a zero entry.
Use original hard copies of the register, journals, and the Daily Transaction Register to verify entries are not missing from the General Ledger.
Use the Library Master, Main, Company Maintenance to make a copy of your live data files to a test company. Then, reconcile the General Ledger balance against the Inventory Reports.
Use the G/L Detail By Source Report to reconcile. Verify each transaction posting back to each register. Verify that no postings to the Inventory Account in General Ledger resulted from an inappropriate subsidiary module or Journal Entry. If a transaction was posted that did not result from an appropriate subsidiary module, reverse the entry in the appropriate module and reenter the transaction in the appropriate module.

I suspect that your various inventory tables such as ItemWarehouse and Item Costing and Detail History are not in balance. Hence the need to run the balancing utilities.