How to Extract a Specific Substring from a String Using Saved Search in NetSuite

Introduction:

When working with NetSuite saved searches, extracting specific values from strings can be a common requirement. In this article, we’ll explore how to fetch a particular substring using a saved search, focusing on retrieving the file ID from an item’s thumbnail URL.

Scenario:

Consider a scenario where you need to obtain the file ID from the thumbnail URL of items within NetSuite.

Solution:

To achieve this, follow the steps outlined below:

1. Create a Saved Search:
2. Begin by creating a saved search in NetSuite that includes the relevant items and the thumbnail URL field.
3. Add a Formula Text Field:
4. In the “Results” tab of your saved search, add a Formula(Text) field to perform the substring extraction.
5. Apply the Formula:
6. Use the following formula to extract the file ID:

CASE WHEN INSTR({your_formula_field}, ‘id=’) > 0 THEN SUBSTR({your_formula_field}, INSTR({your_formula_field}, ‘id=’) + 3, INSTR({your_formula_field}, ‘&’) – INSTR({your_formula_field}, ‘id=’) – 3)ELSE ” END

1. Replace {your_thumbnail_url_field} with the actual field containing the thumbnail URL.
2. Resulting Output:
3. For example, if the thumbnail URL is:

Example:

The field value gets from the thumbnail URL field : “/core/media/media.nl?id=4220203&c=6462484_SB1&h=GZ6_6R7W7BKYWtbC8iLh_s8mamDGF1mNi5fAf3dti7J0ZhOD“;

The output will be: 4220203

Conclusion:

By utilizing a formula text field in your saved search, you can efficiently extract specific substrings, such as file IDs from URLs, streamlining your data retrieval process in NetSuite.