HI Team, I'm new to SuiteQL: I want to run a searc...
# suiteqlr
reza.seedin
11/17/2022, 11:41 PMHI Team, I'm new to SuiteQL: I want to run a search where it returns all the Children of a Assembly Item and also check the effective date and obsolete date to make sure its valid.
• In this example Grand child 3 should be excluded
• while Grand child 1 is part of the "Tree" via parent 2
• I have tried to do this via Normal search but it does not filter out Obsolete children and effect date of the children
• Open to Ideas
reza.seedin
11/17/2022, 11:47 PM@tdietrich Any thoughts 🙂
reza.seedin
11/17/2022, 11:54 PMI'm looking at your blog for inspiration https://timdietrich.me/blog/netsuite-suiteql-kit-package-item-components/
reza.seedin
11/17/2022, 11:59 PMMY base search
a
Anthony (NimbusLabs)
11/21/2022, 10:36 PMI did end up doing this for a client. It's not perfect, but works for them as they have a fixed number of levels. It essentially makes use of UNION ALL to get the levels. So first query is Level 1, then UNION ALL to the children (query 2), UNION ALL to grandchildren (query 3), and so on and so on until you have the max number of levels you may have
Anthony (NimbusLabs)
11/21/2022, 10:37 PMselect d.top_item, d.top_item_id, d.bom_level, d.item, d.parent,d.item_id,d.quantity, BUILTIN.DF(i.location) as location, il.leadTime, il.atpLeadTime
from
(
select BUILTIN.DF(m.parentItem) as top_item,m.parentitem as top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '1' as bom_level
from itemMember m
where m.parentitem = 63542
and (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)
UNION ALL
select b.top_item, b.top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '2' as bom_level
from itemMember m
INNER JOIN (select BUILTIN.DF(m.parentItem) as top_item, m.parentItem as top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '1' as bom_level
from itemMember m
where m.parentitem = 63542
and (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)) b on m.parentItem = b.item_id
where (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)
UNION ALL
select c.top_item, c.top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '3' as bom_level
from itemMember m
INNER JOIN (select b.top_item, b.top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '2' as bom_level
from itemMember m
INNER JOIN (select BUILTIN.DF(m.parentItem) as top_item, m.parentItem as top_item_id, BUILTIN.DF(m.parentItem) as parent,BUILTIN.DF(m.item) as item, m.item as item_id, m.quantity, '1' as bom_level
from itemMember m
where m.parentitem = 63542
and (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)) b on m.parentItem = b.item_id
where (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)) c on m.parentItem = c.item_id
where (CURRENT_DATE < m.obsoleteDate OR m.obsoleteDate is null)
) d
INNER JOIN item i on d.item_id = i.id
INNER JOIN aggregateItemLocation il on il.item = i.id AND il.location = i.location
Anthony (NimbusLabs)
11/21/2022, 10:37 PM(pardon the formatting)
r
reza.seedin
11/21/2022, 10:38 PMWoah
a
Anthony (NimbusLabs)
11/21/2022, 10:38 PMends up producing a result like:
r
reza.seedin
11/21/2022, 10:39 PM@Anthony (NimbusLabs) where do you find these fields top_item
reza.seedin
11/21/2022, 10:39 PMsorry as I new to SuiteQL. been a safe search guy for the whole NS career
a
Anthony (NimbusLabs)
11/21/2022, 10:40 PMif you're familiar with SQL, these are just aliases (or custom label from a saved search)
Anthony (NimbusLabs)
11/21/2022, 10:40 PMThe itemMember table is table with the majority of the data, then it's just joining to get the next level up
Anthony (NimbusLabs)
11/21/2022, 10:40 PM(or down)
r
reza.seedin
11/21/2022, 10:41 PMHaven't had to play around since I started NetSuite about 8yrs ago.