This week we are continuing our NetSuite Saved Searches Tips & Tricks blog series outlining how to use formulas to format and display parent and child records.
If you have tried to generate a saved search that includes records that are members of a parent-child hierarchy out of the box, then you have noticed that NetSuite tool saved search cannot display hierarchical records in a visible format that will allow the end-user to easily distinguish between parent records and their children.
To address this need we will be building a formula that will allow us to indent the child records in the saved search output so that the results are visibly pleasing as well as effective.
To demonstrate, we will create a new Item search and add a formula to the search criteria.
- Go to Lists > Search > Saved Searches > New
- Select Item as the search type
- Results Tab > Add formula (text) as the Field
- In the Formula field, add the following formula:
CASE WHEN {parent} IS NOT NULL THEN '<span style="padding-left: 20px">' || {name} || '</span>' ELSE {name} END- Enter Item Name in the Custom Label field
- Click Preview to view the results of the saved search formula on the Item hierarchy
When you run the saved search, you will notice that the results for the Item Name are displayed with the child item records indented and the parent is not.
The formula that we have added is applying inline styling to the result rows in the search where we are adding 20 px of padding to the left side of the cell. The below portion of the formula is what applies for inline styling.
<span style="padding-left: 20px">You can change the number of pixels in the above section of the formula to increase or decrease the amount of indentation for the child records until you find one that works best for you.
We can also piggyback on our previous Tips and Tricks blog, Removing Parent Hierarchy in Results, where we created a regular expression formula to remove the parent values from the child items when our search results are generated. To do this we can replace the item field reference {name} with our regular expression from last time which would look like this.
CASE WHEN {parent} IS NOT NULL THEN '<span style="padding-left: 20px">' || LTRIM(regexp_substr({itemid},'[^:]*$')) || '</span>' ELSE LTRIM(regexp_substr({itemid},'[^:]*$')) ENDThe above formula will indent the child item records as well as strip the parent values from the output displayed. It will look like this:
This indentation is the same as the prior example, however, the child records no longer display the parent reference in the output.
Stay tuned for more blogs from NetSuite experts in the saved search tips and tricks series and feel free to contact us if you are having 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