This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

does anyone have a query to traverse through the customer hierarchy (bottom to top) and check if one of the relationship includes a value?

Top Level Parent
Child
Child
Child - My row of data would be here but i need to check through each level above for a specific value on the upper level customer records

Do you know the maximum number of levels you might have?

```SELECT level1.id AS level1_id, level1.entityid AS level1_entityid,
level2.id AS level2_id, level2.entityid AS level2_entityid,
level3.id AS level3_id, level3.entityid AS level3_entityid,
level4.id AS level4_id, level4.entityid AS level4_entityid,
level5.id AS level5_id, level5.entityid AS level5_entityid

FROM customer level1
LEFT JOIN customer level2 ON (level1.parent = level2.id)
LEFT JOIN customer level3 ON (level2.parent = level3.id)
LEFT JOIN customer level4 ON (level3.parent = level4.id)
LEFT JOIN customer level5 ON (level4.parent = level5.id)

WHERE 'whatever' IN (
	level1.custentity_myvalue, 
	level2.custentity_myvalue,
	level3.custentity_myvalue,
	level4.custentity_myvalue,
	level5.custentity_myvalue
)```

Thanks <@U4TPAFXEC> - Its not always a fixed amount of levels, which is the problem, but i guess i could see where i could use the above query (maybe add in a _max_ somewhere to get that info! Thanks for the starter query :slightly_smiling_face:

Here’s an example idea that might do what you’re looking for. It will traverse from the lowest level customer and work its way up to the top and then count up how many of the parents have an email address that is on gmail.
```WITH CustomerData AS 

(
SELECT id, parent, email, LEVEL as parent_level
FROM customer
START WITH id = 4959001
CONNECT BY PRIOR parent = id
)

SELECT COUNT(*)
FROM CustomerData
WHERE email LIKE '%@gmail.com'
AND parent_level &gt; 1```

There's the `HIERARCHY` built in: <https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/article_161950565221.html>
I'm not sure you can use it for that, but something to look into

Recursive handling with SuiteQL + SuiteScript:
<https://netsuitediagnostics.com/posts/bom-components-recursively/>

Thanks all - Will be checking these options out and see what is possible with SuiteQL!