Dynamics 365 CommunityForums Microsoft Dynamics SL (Archived) How do I get the corre...

How do I get the correct promise dates and ship dates on a report?

(0) Share

Report

Posted on by Ryan Roach

I am trying to create an on-time ship report in Excel that pulls from SL. The report looks at the promise date (SoLine.PromDate) on the sales order and compares it to the actual ship from the shipper (SoShipHeader.ShipDateAct). The Problem is that depending on what table I pull the ordnbr from the promise date or the ship date are not correct. If I pull the ordnbr from a So table then all the lines on the sales order show as shipped for each line shipped, and if I pull the ordnbr from a SoShip table than the Promise Date always shows as the promise date for the first line of the sales order, which makes all lines after the first appear late. Any suggestions on how to make the report look at the promise date for the correct line?

*This post is locked for comments

All responses (1)

Answers (0)

Chris Munsell 25 on at

Like (0)

Report

Re: How do I get the correct promise dates and ship dates on a report?

Ryan,

I know you posted this a while ago, but I am looking at doing the same thing. I was wondering if you ever found the connection, or just made due with what you had...

I have been searching for an ERD for the OM module so I can find the cleanest way to connect SOShipHeader and SOShipLine and SOSched. Currently I am using an inner join in SQL to get the detail, but the location for the PromDate is actually stored in SOSched. Just connecting them on OrdNbr isn't enough, LineRef still returns more than one line.

SO has anyone had any luck completing something like this?

Thanks,

Chris Munsell