NetSuite saved search is a very powerful NetSuite tool for both data extraction and reporting. Users can build searches that range from very simple to complex from simple lists to financial reports. One of the most powerful features of the saved search is the use of formulas within a query.
As part of our Saved Search Tips and Tricks series, we are going to explore some solutions to very common challenges clients experience in producing search results that meet their needs.
Today we are going to focus on how to Return a list of items that excludes the parent hierarchy when using parent/child item relationships.
NetSuite allows you to create a parent/child hierarchy relationship for items which makes it easier for end-users to see what type or category of item it is in global search. When you use this hierarchy, however, the item name field will output that same hierarchy or parent and child into the Name field when adding it to a saved search.
The below example shows how the item Name field will display as output in a saved search when using the subitem of assignment in NetSuite. It contains the parent item and child item separated by a colon.
In order to remove the parent item from the Name field in search output, you can create a text formula that uses a regular expression to isolate the item name from the preceding parent value in the output.
To do this
- Add a Formula(Text) field to the search results field list.
- In the formula field, add the following formula: LTRIM(regexp_substr({itemid},'[^:]*$’))
This regular expression searches the field for the colon character and then removes everything to the left of it. If you are facing any issues here, then feel free to seek help from our NetSuite experts, they will be glad to assist you in every way possible. The resulting values appear like this:
Below is a side-by-side comparison of the two outputs, with and without the regular expression formula applied:
This same formula can be used for other fields that contain a parent-child hierarchy, such as subsidiary or account. In order to apply it to other fields, you would need to replace the reference {itemid} with the field reference that you are working with.
Hopefully, this little trick will be useful in your saved searches. Oftentimes clients need to remove the parent in order to easily export the results and perform VLOOKUP formulas against other data in Excel that already do not contain the parent relationship. Other times it is useful in search scripts if matching by field name values.
Stay tuned for more blogs in the saved search tips and tricks series and feel free to contact us if you are having particular challenges in your own environment with searches that you cannot seem to figure out on your own. We are always happy to work with new clients to achieve their goals.
About Us
We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience. We specialize in implementation, optimization, integration, rapid project recovery and rescue as well as custom development to meet any business need. If you would like more information on NetSuite or in need of consultation for your project or implementation, feel free to contact our NetSuite support.
To Contact Us Click HERE