Mass Update on Steroids
Fixing Inventory Costing Issues with Quick Update Tool
Are you a NetSuite Super User or an Administrator, and do you find yourself sometimes in a situation where you wish you can update a field in NetSuite, but you can’t? Does this slow you down, does it affect your work? If the answer is ‘yes,’ you are not alone; this is something NetSuite users face from time to time.
As we support our customers, we built some tools that help us administer our clients’ NetSuite environments. Recently, we bundled our tools into a product which we named BOLDAdmin. ‘BOLD,’ because that’s who we are and ‘Admin’ because NetSuite Administrators and Super Users are for whom this solution was designed.
In this article, I will not bore you with too many details related to the app’s features. Instead, I will focus on one single use-case where BOLDAdmin was able to help our client with a costing issue.
Inventory Costing Business Problem
Our client is a manufacturer and a distributor of luxury limited-edition eyewear. Their SKU’s are inventory items with a FIFO costing method. Recently, the client discovered that their Cost of Goods Sold did not add up, which ultimately skewed their product gross margins.
After some discovery, the production manager realized that the biggest reason why COGS wasn’t correctly recorded was the Return Merchandise Authorizations (RMA’s).
Given the fact that our client’s limited-series eye frames come with a hefty price-tag, the company will take scratched or damaged frames for repair. In many cases, these frames are recorded on a single RMA transaction that cannot necessarily be linked to the original sales transaction. Furthermore, when a standalone RMA for a specific SKU is created, this is likely to be the first return receipt for the item at the (Repairs) location, which is always different from the fulfillment location. What this means is that the FIFO costing layer will not be (properly) established for this SKU at the new location.
This can happen when the Item Receipt transaction linked to the RMA is not properly reviewed. Often the Item Receipt for a given SKU will be recorded at 0.00 dollars. This presents a series of problems for the accounting team as these SKU’s (received at $0.00 cost), will also record $0.00 Cost of Goods Sold when fulfilled and shipped to the customer.
This issue can be avoided only if the ‘rate override’ field on the item receipt line(s) is populated during the receipt.
Okay, you’ll say: ‘this is great if I need to do it on one transaction, but what do I do if I have to update thousands of Item Receipt transactions?’ Well, if I did not know that our solution could help, I would have told you that you are out of luck as Item Receipts in NetSuite cannot be updated via CSV Import tool.
There is no reason for sorrow or panic. Here is what we can do to overcome this system limitation.
- Build a Saved Search - First and foremost, we must build a Saved Search to identify which of the Item Receipt lines were posted with zero cost. This is an example of criteria that will give us what we need. Note, your use-case may be slightly different, but the lookup logic will not change.
Notice, I exposed the item’s transfer price (for reference only). My client has been maintaining this price; he wanted to use it as a true cost for the SKU. You can also use the ‘last purchase price’ field on the item record if you feel that this price represents the true cost of your item.
- Expose the source field on the transaction line- I will name my item record’s ‘transfer price’ the ‘source field.’ I want to expose it on the Item Receipt line so that I can update my override rate field in the next step.
- Go to: Customizations > Lists, Records, & Fields > Transaction Lines Fields > New
- See illustrations below for setup details (your configuration may be slightly different):
- Save & Apply to Forms. Make sure you select your default Item Receipt form.
- Run Quick Update Tool – You thought this step would never come, right?! Well, here we are! We will navigate to the BOLD Quick Update tool and click on the ‘Update Record Line Fields’ feature.
- Next, we will configure the Quick Update interface to update the lines that are listed as our Saved Search results.
- Notice, we use internal ID’s to reference our fields:
- Enter Field ID: This is the field that we are updating.
- Enter Source Field ID: This is the field from which we are pulling our value (Transfer Price field).
- Mode: Dynamic, which means that it will be sourcing the value from another field.
- Finally, we can execute the flow, which will update the fields and follow the progress.
- When the process is completed, we can download the logs.
- Let’s review one of the transactions.
In this article, I demonstrated how convoluted inventory costing problems can be fixed by thinking outside of the box. Also, I gave you a glimpse of what BOLD Quick Update and BOLD Admin can do to help you adjust seemingly unreachable records and fields.
Write to us if you liked this solution and should you want to learn more about BOLD Admin, and test if for free, reach out to us now!