In the vast and sometimes tangled world of NetSuite, custom fields are pretty much the heroes when it comes to tweaking data to fit just what your business needs. But here’s where things get interesting: imagine trying to count how many options someone’s picked in those fields where you can choose more than one thing. That’s exactly the kind of puzzle I stumbled upon with a request from a client, kicking off an adventure into the wilds of search analytics.
Initial Discovery
My quest for a solution led me to an intriguing article online, presenting a formula designed to tackle this very challenge. The initial formula was a static approach, structured as follows:
CASE WHEN {customfieldID} LIKE '%,%,%,%' then 4 WHEN {customfieldID} LIKE '%,%,%' THEN 3 WHEN {customfieldID} LIKE '%,%' then 2 WHEN {customfieldID} LIKE '%' THEN 1 ELSE 0 END
This formula was created to count the number of selections made in a custom field that allows multiple selections. Each selection is separated by a comma, and this formula cleverly counts these selections based on the presence of commas.
This method worked under the assumption of a fixed number of selections, assigning counts based on the presence of commas as separators between selections.
Evolving the Solution
However, the static nature of the original formula quickly revealed its limitations in the face of an ever-evolving list of selectable options. The need for a dynamic, scalable solution was clear — one that could effortlessly adapt to the addition of new selections without requiring manual updates to the formula itself.
The Dynamic Formula
The following formula uses a combination of the LENGTH, REPLACE, and CASE functions to dynamically count the number of commas and thereby determine the count of list elements. This method works by calculating the difference in length between the original string and the string with all commas removed. The result gives the number of commas, which, when added to 1, provides the total number of elements in the list (assuming the list is comma-separated and there’s no trailing comma):
CASE WHEN {customfieldID} IS NOT NULL AND {customfieldID} <> '' THEN LENGTH({customfieldID}) - LENGTH(REPLACE({customfieldID}, ',', '')) + 1 ELSE 0 END
Here’s the breakdown:
- LENGTH({customfieldID}) calculates the length of the original string.
- LENGTH(REPLACE({customfieldID}, ‘,’, ”)) calculates the length of the string after removing all commas.
- Subtracting the second length from the first gives the number of commas.
- Adding 1 to the result yields the total number of elements in the list because each element is separated by a comma.
- The CASE statement checks if the {customfieldID} is not null or an empty string to prevent returning 1 when there are no elements.
This formula dynamically adjusts to the number of elements in any list by counting how many delimiters (commas) it contains, making it highly adaptable for various scenarios where the list size might change over time.
