Knowledgebase: Functions
[QODBC-Desktop] Troubleshooting - How to find item not sold after a certain date and mark it as inactive.
Posted by Jack - QODBC Support on 24 March 2017 11:25 AM

Troubleshooting - How to find an Item not sold after a specific date and mark it inactive.

Problem Description:

We would wish to inactive items that have not sold after a certain date. Is this possible? If so, any help would be appreciated.

Solution:

1) Get the list of Sold Items by exporting the SalesByItemSummary report for a certain period in MS Excel as below

For Example:
sp_report SalesByItemSummary show Text, Label, Quantity, Amount, Percent, AveragePrice parameters DateFrom = {d '2016-06-01'}, DateTo = {d '2016-12-31'}

Please refer to How to create sp_reports using Microsoft Excel for exporting reports into excel.

By running the above query, you will get ItemType + Parent Item Name in the Text column & ItemName + description in the Label column.

We will split ItemName from the description using the "Text to Columns" option. We will select the "Label" column & click on "Text to Columns."

Choose "Delimited" and click "Next."

Select "Other" and write "(" (i.e., Opening Brace) and click "Next." We are splitting data into different columns from Opening Brace.

You can see split data from the Data preview. Now click "finish."

Click "OK" to replace destination cell contents.

Data is Split into two columns.

Now we will add a new column & use the formula for trimming whitespace from the Child Item name & removing the prefix "Total" from the Parent Item name.

=TRIM(SUBSTITUTE(C2,"Total","",1))

The formula is applied to all rows. Now we have furnished the Item Name.

2) Now We have the Item Name & Item type for sold items. Now we need to export Item tables depending on Item Type (i.e., ItemInventory, ItemNonInventory, ItemService, etc.).

In this example, We will export the ItemInventory table in sheet2 & we will make inactive ItemInventory which is unsold for a certain period.

Please refer to Using QuickBooks Data with Excel for exporting the ItemInventory table into excel.

The ItemInventory table is exported to sheet2.

Now we will go to sheet 3 & use vlookup formula for getting a list of unsold Items.
=IF(IFERROR(VLOOKUP(TRIM(Sheet2!E1), Sheet1!B: B,1, FALSE), "ERROR")=" ERROR," Sheet2!E1,"")

The formula is applied to all rows. Now we have the name of unsold ItemInventory.

3) Now, We have the name of unsold ItemInventory. You can make it inactive by running the below update query.

Update ItemInventory set IsActive = False where Name = 'Cabinet Pulls'

You can also use VBA to make all unsold Items inactive. Please refer to Using QuickBooks Data with VBA and write a VBA script per your requirement.

Result in QuickBooks.

The sYou can repeat the above steps for Item Type like ItemNonInventory, ItemService, etc...

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).