[ErnestoC]

I have attempted to use the below SuiteAnswers response and the mentioned second reference but neither have been able to populate the information I am seeking. I need a Saved Search that will display inventory which has not been sold within a given time frame, for example 1/18/2016 – 2/1/2016 and I need the results to be Item Name,Display Name, Average price & Other Prices. When I use the examples below the information displayed is items included in a Sales Order, not items not included within the chosen time frame. Can anyone suggest a resolution for this? Or a formula which will execute the information I desire?

Answer Id: 38028

1. Navigate to Lists > Search > Saved Search > New > Item

2. Navigate to Criteria tab > Summary subtab:

Summary Type: Maximum | Field: Formula (Date) | Formula: max(case when {transaction.type}=’Sales Order’ then {transaction.trandate} end) |Value: is empty

3. Results Tab: Remove all Fields and add below Field

–Name > Summary Type: Group

Save & Run

Notes:

1. This search can also be used for other transaction types such as Purchase Orders, Transfer Orders etc. Just change the formula expression under the Criteria tab with the desired Transaction Type.

2. Another variation of this search is documented under SuiteAnswer ID:13357 > Search for Items with no sales transactions for a particular period
This is a cached copy. Click here to see the original post.

[MikeB1235]

I’ve just done something similar. The formula field will return all item records with no sales order records when the Formula Value “is empty” That part works fine. If you shoose anything other than “is empty” (like a date range) then you only get items with orders in that range.

Getting the Item Records to display for a specific date range meant I had to change the Formula to : max(case when {transaction.type}=’Sales Order’ and ({transaction.trandate} > ({today}-365)) then {transaction.trandate} end), and set the Value = “is empty”.

For my purposes, I was trying to find Items that hadn’t sold in the last year. Note that this isn’t a dynamic date range. It’s hard coded. Somebody with more scripting in NetSuite knowledge than I have might be able to explain how to use a user selectable parameter in place of the hard coded date range.

[dansteg]

If you see yourself doing this for more than one search, you may want to create a custom field on the item record “Last Sales Activity”. Have a scheduled script run nightly that looks at searches all invoices from that day and returns all items, grouped. Then cycle through the items and update the custom field with the current date. (or you can have it do this at time of invoicing, but this may add some slight overhead and run multiple times for a single item.)

[Author]

Posts