Changing Item Cost Methods Guide

What Cost Method Means

The item cost method tells QBM how to calculate inventory cost when stock is sold, delivered, returned, transferred, adjusted, or otherwise moved out of inventory. This affects inventory value, cost of goods sold, gross profit, and accounting balances.

Why It Is Not Changed Inside QBM

QBM does not normally allow users to change the cost method from the application after item history exists because changing the method can recalculate historical stock cost. That can affect sales cost, stock value, gross profit, and accounts balance.

When the change is required, the supported process is:

1. Back up the database and make sure all users are logged off.
2. Use SQL Server Management Studio or another trusted database editor to change the CostMethod value in the Products table.
3. Open QBM and run Help > Maintenance > Reevaluate Inventory Transactions.
4. Review inventory, gross profit, and accounting reports after reevaluation.

Cost Method Values

In the QBM database, item cost method is stored in the dbo.Products table in the CostMethod column.

Before You Change Anything

Complete this preparation before using SQL.

• Confirm the company database: make sure you are connected to the correct QBM company database.
• Back up the database: keep a restorable backup before changing the Products table.
• Log off all users: no one should be entering invoices, purchases, POS receipts, adjustments, or transfers during the change.
• Approve the method: confirm whether the target method is Average, FIFO, or LIFO.
• Identify item scope: decide whether the change applies to one item, selected items, or all inventory items.
• Identify date scope: know the transaction date range that must be reevaluated after the SQL update.
• Test first when possible: run the same change on a copy of the database before changing live data.

Find Current Item Cost Methods

Use this query first to review existing items and their current cost method.

-- Select the correct QBM company database first.
USE [YourCompanyDatabaseName];

-- Review current item cost methods before making changes.
SELECT
    ProductID,
    ProductName,
    LookupCode,
    UpcSku,
    IsInventoryPart,
    IsInactive,
    CostMethod,
    CASE CostMethod
        WHEN 1 THEN 'Average'
        WHEN 2 THEN 'FIFO'
        WHEN 3 THEN 'LIFO'
        ELSE 'None or invalid'
    END AS CostMethodName
FROM dbo.Products
ORDER BY ProductName;

Change One Item

This example changes one item to FIFO. Replace the database name and ProductID before running it.

-- Select the correct QBM company database.
USE [YourCompanyDatabaseName];

-- CostMethod values:
-- 1 = Average
-- 2 = FIFO
-- 3 = LIFO

BEGIN TRANSACTION;

-- Change one item to FIFO.
UPDATE dbo.Products
SET CostMethod = 2
WHERE ProductID = 123;

-- Verify the item before committing the change.
SELECT
    ProductID,
    ProductName,
    LookupCode,
    UpcSku,
    CostMethod,
    CASE CostMethod
        WHEN 1 THEN 'Average'
        WHEN 2 THEN 'FIFO'
        WHEN 3 THEN 'LIFO'
        ELSE 'None or invalid'
    END AS CostMethodName
FROM dbo.Products
WHERE ProductID = 123;

-- If the result is correct, run COMMIT.
-- If the result is wrong, run ROLLBACK instead.
COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Change Many Items

Use an update query when many items need the same method. Always run a SELECT query first with the same WHERE condition so you know exactly which rows will be updated.

Change selected items by lookup code

-- Select the correct QBM company database.
USE [YourCompanyDatabaseName];

-- Review selected items first.
SELECT ProductID, ProductName, LookupCode, CostMethod
FROM dbo.Products
WHERE LookupCode IN ('ITEM-001', 'ITEM-002', 'ITEM-003');

BEGIN TRANSACTION;

-- Change selected items to Average.
UPDATE dbo.Products
SET CostMethod = 1
WHERE LookupCode IN ('ITEM-001', 'ITEM-002', 'ITEM-003');

-- Confirm how many rows were updated.
SELECT @@ROWCOUNT AS UpdatedRows;

-- Review the result before committing.
SELECT ProductID, ProductName, LookupCode, CostMethod
FROM dbo.Products
WHERE LookupCode IN ('ITEM-001', 'ITEM-002', 'ITEM-003');

COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Change all inventory items from Average to FIFO

-- Select the correct QBM company database.
USE [YourCompanyDatabaseName];

-- Review items that will change from Average to FIFO.
SELECT ProductID, ProductName, LookupCode, CostMethod
FROM dbo.Products
WHERE IsInventoryPart = 1
  AND CostMethod = 1;

BEGIN TRANSACTION;

-- Change all inventory items currently using Average to FIFO.
UPDATE dbo.Products
SET CostMethod = 2
WHERE IsInventoryPart = 1
  AND CostMethod = 1;

-- Confirm how many rows were updated.
SELECT @@ROWCOUNT AS UpdatedRows;

COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Change all inventory items from FIFO to Average

-- Select the correct QBM company database.
USE [YourCompanyDatabaseName];

-- Review items that will change from FIFO to Average.
SELECT ProductID, ProductName, LookupCode, CostMethod
FROM dbo.Products
WHERE IsInventoryPart = 1
  AND CostMethod = 2;

BEGIN TRANSACTION;

-- Change all inventory items currently using FIFO to Average.
UPDATE dbo.Products
SET CostMethod = 1
WHERE IsInventoryPart = 1
  AND CostMethod = 2;

-- Confirm how many rows were updated.
SELECT @@ROWCOUNT AS UpdatedRows;

COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Change From The Products Table

For a small number of items, a database administrator may edit the Products table directly in SQL Server Management Studio.

1. Open SQL Server Management Studio.
2. Connect to the SQL Server that hosts the QBM company database.
3. Expand the correct database, then expand Tables.
4. Open dbo.Products.
5. Find the item by ProductID, ProductName, LookupCode, or UpcSku.
6. Change the CostMethod value to 1 for Average, 2 for FIFO, or 3 for LIFO.
7. Save the row and then continue with the reevaluation step in QBM.

Reevaluate Inventory Transactions

After changing CostMethod in the database, QBM must recalculate the affected inventory transactions. This step is required because the stored item method has changed, but historical transaction cost must still be recalculated.

1. Open QBM after the SQL change is complete.
2. Go to Help > Maintenance > Reevaluate Inventory Transactions.
3. Read the warning. Reevaluation updates transactions within the selected dates except closed dates and may affect account balances.
4. Select the declaration checkbox confirming that all users are logged off and the database has been backed up.
5. In Item, choose All if all changed items must be recalculated, or choose the specific changed item or items.
6. In From Date and To Date, select the full transaction date range that should be recalculated.
7. In Type, choose All unless support has a specific reason to recalculate only purchases or only sales.
8. Leave Zero cost only unchecked when the purpose is to recalculate all affected transactions after changing cost method.
9. Enter the administrator password. The user ID normally shows sa.
10. Click OK and wait for the task to finish.

After The Reevaluate Step

After reevaluation completes, review reports before allowing users back into the company file.

• Inventory valuation: confirm total inventory value is reasonable after the method change.
• Kardex or inventory movement: review affected items and confirm cost movement is explainable.
• Gross profit: compare margins before and after if the method change affects sales history.
• Trial balance or account balances: confirm inventory and cost of goods sold balances are acceptable to finance.
• One sample item: open one changed item and verify its cost method and costing result.
• User release: allow users to log in only after support and finance accept the result.

Troubleshooting

Support Checklist

• Confirmed the requested new method: Average, FIFO, or LIFO.
• Confirmed the affected items: one item, selected items, or all inventory items.
• Confirmed the correct company database in SQL Server.
• Backed up the database and confirmed the backup location.
• Confirmed all users are logged off.
• Ran a SELECT query before the UPDATE query.
• Updated only the intended rows in dbo.Products.CostMethod.
• Ran Help > Maintenance > Reevaluate Inventory Transactions in QBM.
• Selected the correct item scope and date range during reevaluation.
• Reviewed inventory valuation, Kardex, gross profit, and account balances after reevaluation.

Related Guides

• Inventory Costing Methods Guide
• Item / Product Setup Guide
• Inventory Kardex Detail Guide
• Inventory Kardex Summary Guide
• Inventory Status Reports Guide
• Inventory Movement Reports Guide
• Common Messages and Troubleshooting Guide