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 {} = {} then 1 else 0 end
  • Formula numeric: case when {}={} 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

Wait! Before You GoWould you like to talk to a business software expert to answer your questions?

When you schedule a free consultation with MIBAR, you will experience a one-on-one conversation with a business technology expert who is passionate about understanding your unique needs or issues. Schedule a free consultation today at the link below or give us a call at (212) 869-9300.

Learn More
NetSuite Offer: Save $15,000 With Our Limited-Time Discount

If you’ve been evaluating NetSuite, there is no better time to make the switch. With a NetSuite subscription through MIBAR by December 31, 2020, we will offer you 75 hours of professional consulting services for FREE.

Not only can a new ERP system like NetSuite help you reduce operating costs by automating manual processes, but this limited time offer can also help save you a substantial amount of money upfront.