How to get the BOM using SuiteQL

Sometimes you need to get the Bill of Materials for an Assembly Item via code. There is a way to do this with saved searches, but it takes a number of chained searches to get to the final answer. SuiteQL makes quick work of this with a simple query.

select i1.itemid, brc.bomquantity, i.id
from item i
    inner join bomAssembly ba on ba.assembly = i.id
    inner join bomRevision br on br.billofmaterials = ba.billofmaterials
    inner join BomRevisionComponent brc on brc.bomRevision = br.id
    inner join item i1 on i1.id = brc.item
where i.id in ((item internalid goes here))
    and masterdefault = 'T'

Running SuiteQL searches in your account

Tim Dietrich has developed the SuiteQL Query Tool which greatly simplifies exploration of NetSuite’s database tables. I recommend downloading and installing it in your account to learn the basics of SuiteQL. There’s no better way to learn than to get started querying.