This article is relevant if you are seeking an easier way to understand how to build a two level dropdown list and related dependent dropdown list to drive your NetSuite forms.
Background
During a recent client implementation, one of our senior consultants needed to satisfy a client requirement while designing a refined Returned Authorization process. The client wanted to be able to select a primary reason for the customer return and a secondary related reason.
This pattern comes up frequently. I like to call it the filtered dependent dropdown challenge. NetSuite has discussed methods of doing this in the past using multiple tables. NetSuite has a SuiteAnswer article called Multiple Dependent Dropdown Lists. When I review the article, I find it challenging to follow.
In our consultant’s mind, he saw the problem in a simple manner. He wondered why we couldn’t have a single dropdown using a hierarchical outline. Indeed we can model the problem using a single list; yet the client did not want a dropdown with a long list of options. The client preferred two short dropdown lists versus one long list element. Nonetheless, our consultant knew that the way he wanted to organize the data was the correct relationship. Recognizing that NetSuite now supports hierarchical custom records, he saw a different method which I believe, and I hope you will as well, is very intuitive.
Setting up a NetSuite Dual Dropdown Lists using a Single Hierarchical Custom Record
There are three simple structures you need here:
- Hierarchical Custom Record
- Main Dropdown List
- Dependent Dropdown List
Create Hierarchical Custom Record
In our case, all we needed is a simple list. As soon as we check the record option to indicate that the list is hierarchical, the concept of a parent pointer is automatically added. This kind of pattern is common in entity relational modeling. A record that points to itself represents an opportunity to go to unlimited depth. In our assumptive case, we are going down one level from the top. Yet, the pattern can go as deep as we desire.Produce the Main Dropdown List
The next task is to produce the main drop down list which is the custom form field that should be selected first. In our client’s case, they wanted the first dropdown list to be the primary reason for the product return. Set the filter criteria option to ensure that the Parent reference is empty. See image.Produce the Dependent Dropdown List
Intuitive Setup and Easy Maintenance
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
Very Nice Marty. Is there way not to show the hierarchy in the value?
For example, I set this up to test with some test data and the records show:
‘Test Company : FedEx – Ref# 55555, Account: 1234567’
There are options in preferences to show the last entity or item but not sure if that is possible with these types of values.
Thanks,
Rob
I should add that the 2nd value is ‘FedEx – Ref# 55555, Account: 1234567’
I am looking for a similar dependent dropdown list but for using in a saved search – in the available filter section. Within the saved search the user could select the Account Manager (custom field) and based on that Account Manager, they can then select an item (that is assigned to them). Instead of showing all of the items listed in Netsuite, the list would only show items that the Account Manager is assigned to. I have not been able to figure out a way to do this – if it is even possible?
Hi Robert,
Based on the way these new fields work, it reveals the hierarchy. I would have to play with it but I don’t think so. My intuition is to go to script but that certainly does not make it “easier”.
Marty
Hello Kevin,
This should be possible. Are you able to at least create a custom field that references the item as a list and then filters based on the Account Manager using the point-and-click customization?
Also, where are you trying to use this? In the item filter definition of a custom transaction form?
Marty
Thanks for the reply Marty.
Thanks Mary,
I am trying to do this in the available filter section of a saved search. The challenge is, the item list shows everything that is an item, fixed assets, etc and I would like it to show just the items that are associated with the account manager.
I cannot seem to figure out if it is possible to have an available filter in a saved search pull from another saved search as opposed to the entire item list.
Did you try to use the available filters in the field definition to limit the kinds of data returned?
Marty
I just learned that 2016.2 appears to have an option to turn off or on the hierarchy. display That should be a welcome switch.
Marty
Hi Marty,
Congratulations for another great tip for doing things faster and easier on NetSuite.
I have successfully used this technique to build dependent filtered dropdowns (including three level ones). The only issue I am having is with translation. Although all record values have all the translation variants, they show only in the default language my company is configured to, no matter which language the user selects.
I think this is a NetSuite bug, but wanted to double check with you first to see if I might be missing something…
Hi Edson,
I think it may make sense to go to NetSuite Support on this. I don’t have specific thinking.
Marty
While selecting the line item for editing, it automatically removes the Dependent Drop-Down List values that are already filterd using the sourcing and filtering option. It completely removes the value from Dependent Drop-Down Lists.
I have two dropdowns on line item to have category and sub category. While choosing the main category I have created a filter to show only the values related to the main category field on sub category dropdown. This works fine while creating the new line items but fails while editing any field after the insert. It’s getting refreshed and removes the filtered values from the second and showing blank dropdown.
Interesting challenge. I wonder if this article can help get more control:
https://blog.prolecto.com/2019/11/16/learn-how-to-script-netsuites-sublist-view-dropdown-behavior/
Marty
This gets extremely complex to maintain after the 2nd level because you have to build the 3rd level from all possible options from the first and second level. I was reading this as a parent to many relationship, but I can’t seem to make it work this way. I have multiple parents that need to reuse next level children. Like first level option 1, 2 and 3 need to all share a common second level option A. Currently, I have to create 3 separate option A’s and maintain them as unique records.
I tried but this process not work in my situation.
I have projects so my source list is from Project
Then I wanted to create the Task List based on filter condition of Project and I want that list only show the project > Task which are Parent only.
Tried with script but it does not work out, then created on custom field and that also not work. Any suggestion
Hello Akber,
It’s difficult to see what is happening. It’s important that you reference the project right. For projects, that might just be the entity or the customer.
I recommend you contact us directly. Our firm can assist you:
https://www.prolecto.com/services/innovations/
Marty
Thanks Marty for this great post.
I wonder why this feature is not affecting lists in saved searches or saved reports!
is there a way to implement this dependent list on saved searches or reports too?
Hello Ahmad,
This feature, out of the box, only works on Form views. Our content renderer and query renderer tools give you all the flexibility to produce those types of dependent lists.
https://blog.prolecto.com/2021/11/27/render-netsuite-sql-queries-like-saved-searches/
https://blog.prolecto.com/2021/04/10/content-renderer-engine-2-0-with-netsuite-suiteql/
Marty
Marty