Hi, all. I am wondering if anyone has experience on create a report/saved search to show quantity sold and quantity on hand with a given range of date, but it will need to include the items showing zero even they are not sold during that specific timeframe. We tried creating the saved search, but it always filters out the items that are not in the invoice during that period of time.

Create as an Item search and Join the Transactions. As you are experiencing, if you create as a Transaction search then Join the Item then you will not get all Items.

Hi, @Netsuite Tragic. I did create an item search and join the transaction table by selecting Transaction : Type = ‘Invoice’. Is that a correct way to perform the join from item to transaction like you mentioned?

No. Your results columns are what you want to show with Transactions : Quantity. Criteria you would have Transactions : Type = Invoice.

This is what I did earlier where I used Item-based saved search, and use Transactions : Quantity as result tab and Transactions : Type = 'Invoice'. I also added the date as 'last month' grouping to sum up the quantity, but it only gives me 4.5K of item results where I have an 10K items in total. I would also like to show the items that are not sold during this period of time on the saved search with quantity of zero as well.

Ok. I see. You need to remove the transaction criteria and use a result formula to do the filtering.

I tried doing the transaction table on the result tab but it still only shows the same thing.

Hi Richard, You moved

transaction:type

to a results formula filter, you would need to do the same with

transaction:date

and

transaction:main line

P.S. I think you'll still have items excluded from the search that have zero related transactions. Would be worth checking

Hi, @David B Thank you for the tips. If I moved transaction:date to the result tab, is there a way that can allow user to select the range of dates then? Is that simply being added to the available filter in that case?

I believe so, yes

I removed all conditions under criteria tab, and added transaction : date to the available, but it seems to show the same behavior as previously.

This is what I meant for the formula (untested) Moving the criteria into the formula (lines 3 and 4). Also line 4's

AND {transaction.line} gt 0

not really necessary (this is equivalent to

main line == false

) as the join from the item record always links to an item line on the transaction.

Thanks for the sharing the formula, but we will need the feature where users are able to toggle the date range in order for them to be presented with the saved search with the given range.