Knowledgebase: Microsoft Products
[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 item not sold after a certain date and mark it as inactive.

Problem Description:

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

Solution:

1) Get the list of Sold Items by exporting SalesByItemSummary report for 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 How to create sp_reports using Microsoft Excel for exporting report into excel.

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

Now we will split ItemName from the description using "Text to Columns" option. We will select "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 the different column from Opening Brace.

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

Click "OK" to replace destination cell contents.

Data Split into two columns.

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

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

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

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

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

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

The ItemInventory table is exported to sheet2.

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

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

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

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

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

Result in QuickBooks.

Same way you can repeat 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).