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

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:

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, learn how you can become an author/collaborator here.

Don’t miss a beat – subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they’re published. Ignorance? Nah, you’ve got a smarter option. Choose wisdom, choose insights!