MYOB EXO – Unsold Stock Items

Ever wanted to know what stock you’ve had sitting unsold for a long time?

The following custom filter on the stock search screen will list only those items for which

• You currently have stock on hand AND
• You haven’t sold any in the last 12 months AND
• You first purchased more than 12 months ago.

Follow the instructions in our previous tip of the week custom filters to set up the filter below.

Copy from this tip of the week the Filter Name and the Filter SQL and paste (using Crtl – C for Copy and Crtl – V for paste) both into the Filter Screen.

Click OK.

Back in the Stock Search Screen you will see a funnel icon showing a filter is active. Right click on the grid header, select Custom Filter; it will show “Items in stock not sold…” with a tick beside it.

Filter Name:

Items in stock not sold in the last 12 months (excludes stock first purchased in last 12 months.

Filter SQL:

s.totalstock > 0 and s.stockcode not in (select distinct stockcode from stock_trans t inner join period_status p on t.period_seqno = p.seqno where transtype = 0 and p.age <12 and quantity > 0) and stockcode in (select distinct stockcode from stock_trans t inner join period_status p on t.period_seqno = p.seqno where transtype = 1 and p.age >=12)

If you want to look at a period shorter (or longer) than the last 12 months, just change both occurrences of the number 12 (= 12 months) in the Filter SQL to the number of months you want.