ANS-0196 · SAVED SEARCHES & FORMULAS
How to Extract the Last Segment of a NetSuite Job ID from Entity ID?
Learn how to create a formula field to isolate the specific project or job identifier when the entityid contains hierarchical, colon-separated values.
Short answer
To retrieve the final segment of a NetSuite Job ID from the entityid field, especially when it's structured hierarchically with colons, use a formula combining SUBSTR and INSTR. This approach precisely locates the last colon and extracts the subsequent characters, ensuring only the specific job identifier is returned for reporting or custom field population.
Scenario
NetSuite users often encounter entityid fields for projects or jobs that contain multiple segments separated by colons (e.g., "Parent Project:Sub Project:Job ID"). When reporting or integrating, there is a need to extract only the final, most specific job identifier from this hierarchical string, rather than the entire entityid.
Solution
To extract the last segment of a NetSuite Job ID from the entityid field, a formula can be implemented in a custom field or saved search. This formula uses the INSTR function to find the position of the last colon and then the SUBSTR function to extract the characters immediately following it, including an assumed space. The corrected formula, ensuring all required parameters for SUBSTR are provided, is as follows:
SUBSTR({entityid}, INSTR({entityid}, ':', -1) + 2, LENGTH({entityid}) - (INSTR({entityid}, ':', -1) + 2) + 1)This formula will return the specific job number, effectively removing any preceding hierarchical project identifiers.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support