How-toInfor SyteLine (CloudSuite Industrial)Engineering / Bill of Material

How to Export an Indented (Multi-Level) BOM from SyteLine

Question

how to export an indented bom from syteline

Also searched as

syteline multi level bom to excel
syteline bom explosion sql
syteline export bill of material csv

Short answer

You can get an indented BOM out of SyteLine three ways: run the BOM report with the multi-level option and export it, use the grid export on the job material or BOM form for single levels, or run a recursive SQL query against the BOM tables for a full multi-level explosion. The SQL route is the most reliable for many parents at once.

Applies to: Infor SyteLine 8.x and CloudSuite Industrial (CSI) on SQL Server; table and column names should be verified against your version before use

How to export a multi-level BOM

1Decide scope: one parent item, a family, or every active parent. Single parents suit the application; large exports suit SQL.
2In the application, open the BOM or bill of material report from the forms list, choose the item and set the option to explode all levels if available, then send output to a file or Excel through report options.
3For a quick single-level list, open the BOM maintenance form for the item, use the grid's export to Excel option and repeat for each sub-assembly.
4For a full explosion, run a recursive query on a reporting copy or with read-only access (sample below), adjusting names to your version.
5Include the fields you need: level, parent, component, quantity per, unit of measure, scrap, effective dates and operation number.
6Save to CSV or paste into Excel, then check a known assembly by hand to confirm the quantities and levels.
7Decide how to treat phantoms, alternates and effectivity so the export matches what planning actually uses.

How SyteLine stores the BOM

SyteLine keeps manufacturing BOM data in the job structures: a standard job holds the material lines in the job material table and the operations in the job route table, and each item points to its standard job. Because of that, a BOM export is a join between the item and its standard job material, and a multi-level export is that join repeated for each component that has its own standard job.

Always query through the site views where your installation uses multi-site tables, and filter by site. Querying the base tables across sites can mix data and produce duplicates.

Sample recursive query

The following sketch shows the idea. It starts from one parent, walks its components and multiplies quantities by level. Verify column names against your schema, and add filters for effective dates and for the site. Treat it as a starting point, not a drop-in script.

Add a cycle guard in real use: a BOM that refers back to itself will loop. Limit recursion depth with OPTION (MAXRECURSION n) and investigate any item that hits the limit.

WITH bom AS (
  SELECT 0 AS lvl, i.item AS parent, m.item AS component,
         m.matl_qty AS qty_per, CAST(m.matl_qty AS decimal(28,8)) AS ext_qty,
         i2.job AS child_job, i2.suffix AS child_suffix
  FROM item i
  JOIN jobmatl m ON m.job = i.job AND m.suffix = i.suffix
  LEFT JOIN item i2 ON i2.item = m.item
  WHERE i.item = 'PARENT-ITEM'
  UNION ALL
  SELECT b.lvl + 1, b.component, m.item, m.matl_qty,
         CAST(b.ext_qty * m.matl_qty AS decimal(28,8)),
         i3.job, i3.suffix
  FROM bom b
  JOIN jobmatl m ON m.job = b.child_job AND m.suffix = b.child_suffix
  LEFT JOIN item i3 ON i3.item = m.item
)
SELECT lvl, parent, component, qty_per, ext_qty FROM bom
ORDER BY lvl, parent, component
OPTION (MAXRECURSION 20);

Making the export trustworthy

A BOM export is only useful if it matches what planning uses. Decide how to handle phantom assemblies (pass through), alternate components, scrap and effective dates. If you ignore effectivity you will export obsolete components alongside current ones.

Reconcile a few assemblies against the application's own display, and compare totals such as the number of distinct components. Exports are often used for costing and for PLM comparison, where small errors compound.

Try it here, free

Recursive BOM Explosion SQL CTE Generator

Open full tool

Builds a recursive common table expression that explodes a multi-level BOM with extended quantities, plus a where-used query, using your own table and column names.

Fill in

BOM table name

Generic name. Replace with your ERP table or view.

Parent item column

Component item column

Quantity per column

Effective-from column

Effective-to column

Top-level item

As-of date

Max levels

Guards against circular BOMs.

Generated code

Download:

PostgreSQL

WITH RECURSIVE bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         b.qty_per AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

SQL Server (no RECURSIVE keyword)

WITH bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         CAST(b.qty_per AS decimal(18,6)) AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= CAST('2026-10-20' AS date)
    AND (b.eff_to IS NULL OR b.eff_to > CAST('2026-10-20' AS date))
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         CAST(t.ext_qty * b.qty_per AS decimal(18,6)), t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= CAST('2026-10-20' AS date)
    AND (b.eff_to IS NULL OR b.eff_to > CAST('2026-10-20' AS date))
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item
OPTION (MAXRECURSION 20);

Oracle

WITH bom_tree (parent_item, component_item, qty_per, ext_qty, lvl) AS (
  SELECT b.parent_item, b.component_item, b.qty_per, b.qty_per, 1
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

MySQL 8 and later

WITH RECURSIVE bom_tree AS (
  SELECT b.parent_item AS parent_item,
         b.component_item AS component_item,
         b.qty_per AS qty_per,
         b.qty_per AS ext_qty,
         1 AS lvl
  FROM bom_line b
  WHERE b.parent_item = 'ASM-1000'
    AND b.eff_from <= '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, b.qty_per,
         t.ext_qty * b.qty_per, t.lvl + 1
  FROM bom_line b
  JOIN bom_tree t ON b.parent_item = t.component_item
  WHERE t.lvl < 20
    AND b.eff_from <= '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > '2026-10-20')
)
SELECT lvl, parent_item, component_item, qty_per, ext_qty
FROM bom_tree
ORDER BY lvl, parent_item, component_item;

Where-used (PostgreSQL, upward)

WITH RECURSIVE where_used AS (
  SELECT b.parent_item AS parent_item, b.component_item AS component_item, 1 AS lvl
  FROM bom_line b
  WHERE b.component_item = 'ASM-1000'
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
  UNION ALL
  SELECT b.parent_item, b.component_item, w.lvl + 1
  FROM bom_line b
  JOIN where_used w ON b.component_item = w.parent_item
  WHERE w.lvl < 20
    AND b.eff_from <= DATE '2026-10-20'
    AND (b.eff_to IS NULL OR b.eff_to > DATE '2026-10-20')
)
SELECT DISTINCT parent_item, lvl FROM where_used ORDER BY lvl, parent_item;

Table and column names are generic. Map them to your ERP BOM table or a view that exposes parent, component, quantity and effectivity. Most ERPs add scrap factors, yield, phantom handling, alternates and revision logic that this query does not apply, so extended quantity here is a plain product of quantities per. Test on a small assembly first and compare with the ERP's own BOM report. Run read-only queries on a replica when possible.

BOM query review

Optional help mapping this query to your actual ERP BOM tables and checking it against standard ERP reports.

Optional. The tool and every export work without this. No spam.

Common pitfalls

!Querying across sites without a site filter and getting duplicate lines.
!Ignoring effective dates and exporting obsolete components.
!Forgetting a recursion limit and looping on a circular BOM.
!Treating phantoms as real stocked assemblies in cost roll-ups.
!Running heavy queries against the live transactional database during working hours.

How an ERP-grounded AI assistant handles this

Writing and checking recursive BOM queries is a good task for SyteRay: it can generate the explosion for your version, flag circular references and items without a standard job, and explain the result. It works read-only, shows the SQL it ran and can run on-prem so engineering data never leaves your network.

See ERPray See SyteRay for SyteLine

Frequently asked questions

Does SyteLine have a built-in indented BOM report?

There are BOM reports with multi-level options, and the available forms vary by version and customization. Search the forms list for bill of material reports and test with a known assembly.

How do I get a multi-level BOM into Excel?

Export a report to file or Excel from the report options, or run a recursive SQL query and save the results as CSV. SQL is better for large numbers of parents.

Where is the BOM stored in SyteLine?

In the job material and job route tables of standard jobs, which the item record points to. Verify table and column names against your version.

Is it safe to run the query on production?

Prefer a reporting copy or off-hours with read-only access. Recursive queries on large BOMs can be heavy, so test with a limited scope first.

How-to

How to Maintain the BOM and Routing in SyteLine

In SyteLine the manufacturing structure for an item is a standard (type S) job: its material lines are the BOM and its operation lines are the routing. You maintain it from the item's BOM and routing forms, set effectivity dates, then let new job orders pull a copy. Existing jobs do not update automatically, which is where most confusion comes from.

Advanced

How to Build SQL Views for Custom SyteLine Reporting

Reports should read from purpose-built SQL views, not directly from SyteLine's core transactional tables, so an Infor patch cannot silently break them and heavy report queries do not lock rows the ERP itself needs. Put custom views in their own schema or under a clear naming convention, build them on stable base tables, and grant reporting tools read access to the views only.

How-to

How to Run a Cost Rollup in SyteLine

Run Item Cost Rollup from Product Definition > Costing > Cost Rollup, select the site and item range, run it in Simulate mode first to review variances, then run it live to post new standard costs to Item records and the Costed Bill of Material.

How-to

How to Sync the BOM Between PLM and ERP

Make PLM the system of record for the engineering BOM (EBOM) and ERP the system of record for the manufacturing BOM (MBOM), and push only released data from PLM to ERP on a change-release event. Never edit the same BOM structure in both systems, and treat every failed transfer as a visible work item, not a silent log line.

Error fix

Fixing SyteLine's 'Object reference not set to an instance of an object' error

This is a generic .NET NullReferenceException surfacing through the SyteLine IDO Runtime, not a SyteLine-specific error code. It almost always means a form, script or IDO method referenced a field, row or object that came back null, usually after a customization, a missing related record, or a view/IDO method call before the form finished loading. Turn on detailed client logging and check the most recent customization or form event first.

Error fix

Diagnosing and fixing SyteLine session timeout errors

SyteLine session timeouts come from one of three independent layers: the IDO Data Service session timeout on the app server, the IIS/application pool idle timeout for the web (Mongoose Web) client, or a load balancer/proxy idle timeout in front of a CloudSuite hosted environment. Fixing the wrong layer is the most common mistake - you need to identify which layer is actually expiring the session before changing anything.

AI for ERP

AI for Infor SyteLine and CloudSuite Industrial

Add grounded AI to Infor SyteLine or CloudSuite Industrial: natural-language answers, agents over IDOs and ION, on-prem or CloudSuite deployment.

AI for ERP

An Air-Gapped Private LLM for SyteLine, Built for Defense Suppliers

Deploy a private LLM on an air-gapped network alongside Infor SyteLine for defense suppliers: no internet egress, ITAR and CMMC-aware architecture.

Stuck on Infor SyteLine (CloudSuite Industrial)?

Talk to engineers who work inside Infor SyteLine (CloudSuite Industrial) every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineer Browse ERP Help

All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

CADTALK ERP Integration with Infor SyteLine (CSI)How CADTALK turns engineering BOMs from CAD, PDM or PLM into manufacturing BOMs, items and routings in Infor SyteLine. Setup, pitfalls, alternatives, FAQ.

Guides

AI on your ERP

TermsMulti-Level BOM BOM (Bill of Materials)Phantom BOM BOM Explosion CloudSuite Industrial

How to Export an Indented (Multi-Level) BOM from SyteLine

How to export a multi-level BOM

How SyteLine stores the BOM

Sample recursive query

Making the export trustworthy

Fill in

PostgreSQL

SQL Server (no RECURSIVE keyword)

Oracle

MySQL 8 and later

Where-used (PostgreSQL, upward)

BOM query review

Common pitfalls

How an ERP-grounded AI assistant handles this

Frequently asked questions

Related

How to Maintain the BOM and Routing in SyteLine

How to Build SQL Views for Custom SyteLine Reporting

How to Run a Cost Rollup in SyteLine

How to Sync the BOM Between PLM and ERP

Fixing SyteLine's 'Object reference not set to an instance of an object' error

Diagnosing and fixing SyteLine session timeout errors

AI for Infor SyteLine and CloudSuite Industrial

An Air-Gapped Private LLM for SyteLine, Built for Defense Suppliers

Stuck on Infor SyteLine (CloudSuite Industrial)?

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

Guides

AI on your ERP

Fill in

PostgreSQL

SQL Server (no RECURSIVE keyword)

Oracle

MySQL 8 and later

Where-used (PostgreSQL, upward)

BOM query review