This blog post details the process of fetching FIFO cost in NetSuite based on a particular date. Currently NetSuite does not have an option to directly show the FIFO cost for an item (and also bin if required). The saved search below also helps to find out the stock of an item as of a date along with FIFO costing.

Here’s how to create a transaction-based saved search and set following criteria within STANDARD.

  • Posting is TRUE
  • Quantity is not empty
  • Date within 01/01/2019 until 01/31/2019 (can be fetched for other date range by adding DATE in available Filters)
  • Formula numeric: case when {account.id} = {item.assetaccount.id} then 1 else 0 end
  • Formula numeric: case when {location.id}={item.inventorylocation.id} then 1 else 0 end

netsuite FIFO cost

Note: once the results are displayed, you can eliminate zero stock by using the SUMMARY Filters. If you would like to only see negative stock, then use the Summary filter and sett the sum of quantity to less than zero.

NetSuite FIFO cost

In the results tab,

  • Add column ITEM and GROUP it.
  • Add column QTY and SUM it
  • Add column Location and group it (so that we can visualize by item and its location)
  • Add column TransactionBin (if you use BINS it will be useful).
  • Add formula column as below and let it be round({amount} / NULLIF({quantity},0),5). Let the summary function have Maximum value.

NetSuite FIFO cost

Then, you can navigate to the Available filters section to add any columns that may be useful to filter further. Some columns that may be useful are Location, Date, Item, Bin number etc.

The above saved search will run for each transaction by item matching the criteria as well as parameter range specified in AvailableFilters.

For example, input the date range from Jan 1st to Jan 31, 2019 and it will identify all the items that have transaction within that date range. The date value within available filters can be used to find out the stock as of the date range as well as FIFO cost.

With the above saved search, it is possible to find out FIFO cost as well as stock count. The output can be exported to excel for further analysis. The stock status would help managers to plan for procurement.

Need help with NetSuite? We can help! Learn more about our NetSuite services, schedule a free consultation, or call us at (212) 869-9300. 

Additional Resources

How to use Summary Functions in Saved Search Without Using SUMMARY TYPE in Saved Search

Avoid This Common NetSuite Mistake When Building a Transaction Saved Search

NetSuite Saved Search Magic with Dynamic Drill Through

Creating a Save Search in NetSuite for Transactions with Related Records