How-toInfor SyteLine (CloudSuite Industrial)SQL / IDO / Reports

How to replace NULL in Infor SyteLine (SQL, IDO and reports)

Question
infor syteline how to replace null

Also searched as

  • syteline isnull in sql view
  • syteline ido filter is null
  • syteline report expression null value

Short answer

In SQL use ISNULL(column, default) or the standard COALESCE(a, b, default). In an IDO filter use IS NULL or IS NOT NULL, and in SSRS report expressions test with IsNothing or IIf. NULL is not equal to an empty string or zero, so each layer needs its own check.

Applies to: Infor SyteLine 8.x to CloudSuite Industrial on SQL Server, with Mongoose IDOs and SSRS reports.

Replace NULL where it appears

  1. 1In a SQL query or view: SELECT ISNULL(c.cust_num, 'N/A') or COALESCE(c.cust_num, c.alt_num, 'N/A').
  2. 2In arithmetic: ISNULL(qty, 0) before adding or multiplying, since any NULL in an expression makes the result NULL.
  3. 3In a comparison: use IS NULL, never = NULL. Use IS NOT NULL for the opposite.
  4. 4In an IDO filter on a form or call: write the filter as Column IS NULL or Column IS NOT NULL.
  5. 5In an SSRS expression: =IIf(IsNothing(Fields!qty.Value), 0, Fields!qty.Value) or =Fields!qty.Value + 0 for numbers after a Nothing check.
  6. 6In an outer join, move the filter on the right table into the ON clause or add OR col IS NULL so unmatched rows are not removed.
  7. 7Test with a row that is known to contain a NULL.
  8. 8Store the fix in the view or report, not in every consumer.

ISNULL versus COALESCE

ISNULL is SQL Server specific and takes two arguments. The result takes the data type of the first argument, which can truncate a longer replacement string. COALESCE is standard SQL, takes any number of arguments and returns the first non-null one.

For readability and portability prefer COALESCE, and use ISNULL when you want the first argument's type, for instance in older code.

SELECT j.job,
       ISNULL(j.qty_complete, 0) AS qty_done,
       COALESCE(j.description, i.description, 'No description') AS descr
FROM job j
LEFT JOIN item i ON i.item = j.item AND i.site_ref = j.site_ref;

NULL in IDO filters and forms

IDO filters are passed to the underlying SQL, so IS NULL works. Writing Col = '' only finds empty strings, which are different from NULL, and returns different rows.

On forms, a blank field may be an empty string or a NULL depending on how it was saved. Check the data when a filter 'misses' rows that look blank.

NULL in report expressions

SSRS expressions fail or show #Error when arithmetic hits a Nothing value. Check with IsNothing first, and remember IIf evaluates both branches, so guard the risky branch.

A better fix is to apply the COALESCE in the report dataset query or the SQL view, so every report sees clean numbers.

Aggregates and joins

SUM, AVG and COUNT(column) ignore NULLs. COUNT(*) counts rows. If a missing value should count as zero, convert it before averaging, or the average is overstated.

Outer joins create NULLs by design, so check how they flow into the calculation.

Try it here, free

SyteLine IDO Names Reference

Open full tool

Searchable list of common SyteLine IDO names with the underlying tables, to speed up REST integrations and form work.

Common pitfalls

  • !Writing = NULL or <> NULL, which is never true.
  • !Expecting an empty string to match IS NULL.
  • !Letting ISNULL truncate a longer replacement string to the first argument's length.
  • !Filtering on the right table in a WHERE after a LEFT JOIN and silently turning it into an inner join.
  • !Averaging a column with NULLs when missing values should count as zero.

How an ERP-grounded AI assistant handles this

SyteRay reviews a SyteLine query, view or report expression and points out where NULLs enter and where they break a total, then proposes the COALESCE fix and shows the corrected SQL for a developer to test. It can run on a local model against your own schema.

See ERPraySee SyteRay for SyteLine

Frequently asked questions

What is the SQL Server function to replace NULL?

ISNULL(expression, replacement) or COALESCE(expr1, expr2, ..., default). Both return the replacement when the expression is NULL.

How do I filter for NULL in a SyteLine IDO filter?

Write the filter as ColumnName IS NULL, or IS NOT NULL for the opposite. Do not use equals NULL, which never matches.

Why does my total show NULL?

In SQL any arithmetic with a NULL operand returns NULL. Wrap the nullable columns in ISNULL or COALESCE before adding or multiplying.

Is a blank field in SyteLine NULL or empty?

It can be either, depending on how the row was saved. Test with both IS NULL and an empty-string comparison to see which rows you have.

Related

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.

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

SyteLine error: an error has occurred during report processing

This generic SSRS error in SyteLine usually comes from one of three sources: the client cannot reach the configured report server URL, the report's data source credentials or connection string are wrong, or the report definition itself has a parameter or dataset problem. Check the report server URL setting in SyteLine first, then confirm the report is actually deployed to that server, then look at the SSRS server's own execution log for the real underlying error.

How-to

Work order (job) item, status and routing tables in SyteLine

In SyteLine a work order is a job, keyed by job and suffix. The header is in the job table (item, quantity, status), operations are in jobroute, materials in jobmatl, and production transactions in jobtran. Item standard routings are stored as template jobs rather than in a separate routing table.

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.

Error fix

Resolving SyteLine multi-site replication conflict errors

A replication conflict in SyteLine's multi-site architecture means the same record (commonly an item, customer, or BOM) was changed independently at two sites between replication cycles, and the replication engine could not automatically merge the two versions. Check the replication conflict log first to see which record and which fields collided, then decide which site's version should win before resuming the queue.

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.

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 engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Guides

AI on your ERP

Integrations

Comparisons

Software

TermsIDO (Intelligent Data Object)IDO Collection