Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

Enrich Your NetSuite Item Hierarchy With Dynamic Subitem Sublists and Active Subitem Counts

4 min read

NetSuite’s item master supports hierarchical items, which is powerful. However, as your item hierarchy grows, it becomes harder to see which items are related. In this article, I explain a very simple way to leverage NetSuite’s powerful point-and-click tool (a.k.a. SuiteBuilder) to produce a child item sublist and a dynamic count of (active) child items.


TL;DR

While working on a project recently, we ended up with an item master three levels deep: An item could have a parent and a grandparent. Moreover, at each level, there could be a mirror item for intercompany transactions. Setting up the item master was straightforward. However, once the items were in place, the challenge of seeing which items are related as well as validating the hierarchy became obvious.

It is tempting to address this challenge by developing an item nomenclature that implicitly tells us what level we are at in the item hierarchy. While a consistent naming convention is recommended, it is not a reliable way to capture hierarchies – item names could get quite long, human error during data entry could produce inconsistencies, etc.

The good news is that NetSuite’s user interface has some very powerful paradigms one of which is the ability to create dynamic sublists. We can leverage this functionality to produce a descendant items’ sublists. Additionally, we can leverage the functionality of summary saved search-driven custom fields to produce a dynamic count of subitems. Here’s the final output:


Building an Subitem Custom Sublist

If you are not familiar with custom sublists, refer to SuiteAnswers ID 48803 which describes the paradigm.

Step 1.1: Create an Item Search

  • Go to Lists >> Searches >> Saved Searches >> New >> Item.
  • Check the Public and Available as Sublist View options.
  • Under the Results subtab, select whatever fields that you are interested in.
  • Under the Available Filters subtab, add filter Sub-Item Of and make sure that Show in Filter Region is unchecked. This is crucial for the sublist to work correctly. Essentially, we tell NetSuite: “When I use this search in an item sublist, find and show only items that are subitems (technically “descendants”) of selected item”. Simple yet powerful!
    • If you want other filters that will be visible in your sublist, you can do so and check the Show in Filter Region. Just make sure that the Sub-Item Of filter comes first.
  • (Optional) Under the Criteria subtab, add filter Inactive = No if you would want to exclude inactive items. Also, add any filters you might want to show up on your sublist. For such filters, you should check the Show in Filter Region.
  • Save.

As you may have noticed from the screenshot of the final result at the beginning of the article, NetSuite’s “Sub-Item Of” filter actually works as “descendant of”. Thus, it will include both direct and indirect children. As far as I know, it is not possible to automatically limit the output to direct children. To do that, we would need a dynamic filter that effectively says “parent is me” but NetSuite’s item saved searches do not support that.

How to Produce Field Names with No Hierarchy

As a aside, if you want to get just the child item name without the hierarchy (e.g. Grandparent Item : Parent Item : Child Item simply becomes Child Item), you can add a formula field with regular expression REGEXP_REPLACE({name}, '(.* : )*', '') as illustrated below:

Use a saved search Formula (Text) field with regular expression REGEXP_REPLACE({<fieldname>}, '(.* : )*', '') to strip out the hierarchy from any <fieldname> that represents a hierarchical structure, e.g. subsidiary, location… Click To Tweet

Step 1.2: Create an Item Sublist

Once we have our saved search from step 1.1, the next step is to create a custom item sublist. SuiteAnswers ID 44802 describes the steps clearly; thus, I will not repeat. The only thing to emphasize is that if the search you created in the previous steps does not show up as an option for the item sublist saved search, it’s likely one of two things: You forgot to check the Available as Sublist View or your Sub-Item Of filter is not the first and/or the Show in Filter Region is checked.

Step 1.3: (Optional) Move the Sublist Around on Your Item Forms

By default, the sublist will show up under the Custom subtab unless you specified a different subtab in Step 2 above. However, you can always customize the form and move the sublist to another location. I find it logical to place the sublist under the Related Records subtab. You could also create a top-level “Subitems” subtab via Customization >> Forms >> Subtabs and place it there.

This completes the first part of our challenge.


Dynamically Counting Child Items

The item sublist is cool and we can stop there if we want. But, let’s take it further: I want a field that instantly tells me how many active subitems an item has. This information will also help identify leaf items i.e. items with zero subitems. And, no, I don’t want any background script that computes it. Let’s have NetSuite do the work using an unstored custom field driven by a summary saved search! Refer to SuiteAnswers ID 31727 if you are unfamiliar with this technique.

Step 2.1: Create a Summary Item Saved Search

  • Go to Lists >> Searches >> Saved Searches >> New >> Item.
  • Check the Public option.
  • Under the Criteria subtab, add filter Inactive = No.
  • Under the Results subtab, add field Internal ID with Summary Type = Count. (Note: Any other field that produces a unique count per item would work fine.)
  • Similar to the previous search, under the Available Filters subtab, add filter Sub-Item Of and make sure that Show in Filter Region is unchecked. Essentially, we tell NetSuite: “When I use this search in an item sublist, give me the count of only items that are subitems of selected item”.
  • Save.

Step 2.2: Create a Custom Item Field

  • Go to Customization >> List, Records, & Fields >> Item Fields >> New.
  • Uncheck the Store Value option. This is crucial as it the field will be calculated on the fly based on a saved search so we cannot store its value.
  • Set the Item Type to Integer Number.
  • Under the Applies To subtab, select all the (desired) item types.
  • Under the Display subtab,
    • Set Subtab = Main (or whatever you prefer).
    • Set Display Type = Inline Text to signal that this value should is auto-generated and should not be changed. Note: If you left it as Normal and a user tried to change the value, their changes will be ignored.
  • Under the Validation & Defaulting subtab, in the Search field, select the active item summary saved search that you created in the previous step.
  • Save.

Limitation of Summary Search Custom Fields

Please bear in mind that summary search-driven custom fields are designed solely for display in the user interface. As such, they will not work in saved searches i.e. the value will be empty! This is in contrast to other fields with Store Value = False derived from lookups or based on formulas; the latter will show up in saved searches (but not in reports).

So, if you really need a subitem count that is persistent and can be included in saved searches, this solution would not work. On the other hand, if that is your use case, you can easily create a summary saved search with result fields Parent (Summary Type = Group) and Internal ID (Summary Type = Count) to obtain the desired output. Anything else might require scripting.


Conclusion

In this article, I showed you how to use two of NetSuite’s powerful UI features using item master hierarchies as a use case. Of course, there are several other applications of these techniques. NetSuite has more to offer in terms of UI customization options. It is fair to say that whoever designed the current UI was visionary. Unfortunately, it seems that NetSuite has not kept up since the original design was produced leading to some glaring omissions and annoyances (subject for another time). I hope that, with the talk of a new UI framework in the works, NetSuite would equip us with some more UI goodness. In the meantime, enjoy what is out there to make your NetSuite user experience better.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, check out how you can become an author/collaborator here.

Also, consider subscribing to our no-nonsense email list to get these insights delivered to your inbox as soon as they’re published. Sometimes, ignorance is a choice. Choose wisely!

Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

2 Replies to “Enrich Your NetSuite Item Hierarchy With Dynamic Subitem Sublists and Active Subitem Counts”

  1. First off I absolutely love your articles as they are always super interesting and always thinking way outside of the normal realm of thinking. I find this article on custom sub items super interesting. I was wondering if this type of approach or one similar could be used to setup a custom component substitution sublist located on the Assembly Item record? Currently we do not have Advanced BOM turned on and I am trying to stay away from that feature due to some of the reporting issues I read about and the loss of visibility between the Assembly Item to Sub-Assembly Item link. The issue we are having is that we are experiencing supply shortages. We have a component type on an Assembly Item but the vendor/supplier has it listed as on backorder for the foreseeable future. Therefore, our customer has approved a different component (but very similar traits to allow approval by our Customer’s Engineering Dept) that is a different part # from the vendor at a higher cost. This 1 component is currently listed in over 100+ other different Assembly BOM’s. I am wondering if you possibly had an idea to an approach similar to your recent article that would incorporate item substitution at the Assembly/Component level. Awhile back I tried the “Item Substitution” feature with the Supply Chain bundle and it only works for inventory item substitution which is very disappointing. Please let me know if you have any recommendations for this issue. Thanks in advance and keep up the great topics and information!

    1. Hey Andrew, thanks for your kind words. I’m glad to hear that you found the article insightful. I’m rather distant from your use case to give you a concrete answer. Why don’t you try out the technique and let us know if it solves your concern? Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

You deserve to know as soon as we share a new NetSuite Insight.
We won't disturb you with anything else or share/sell your data.