Sales Shipment Lines empty or after given date

Business Central/NAV Developers Forum
1

I want to make a report with Sales Lines and underlying Sales Shipment Lines.

I want the Sales Lines WITHOUT any Sales Shipment Lines AND I want the Sales Lines where the Sales Shipment Lines have a Shipment Date BEFORE a given INPUT DATE.

So, in SQL, [Sales Shipment Line].[Shipment Date] IS NULL OR [Sales Shipment Line].[Shipment Date] > @InputDate.

How do I do that in a NAV 2013 report?

2

Welcome to forum!

The above is not in fact correct assumption. Records in Sales Line table are deleted, when the SO is completely shipped & invoiced, and Sales Shipment Line record does not exist until at least a partial shipment is posted.

Then, I assume that TSQL was used only for illustration purposes, but if for reporting purposes data are fetched by some real TSQL script or SP, remember that ALL fields in ALL NAV tables are NOT NULL by design. Therefore, SQL value for empty date is not NULL but 1753-01-01 00:00:00.000

3

Thanks Modris. I guess we have to look for a different approach then.