Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Order SQL Queries
Small and medium business | Business Central, N...
Suggested answer

Order SQL Queries

(0) ShareShare
ReportReport
Posted on by TerryMitchell 10

Good day all.  I'm working on some SSIS jobs to extract summary data for analysis in an external BI solution.

Unfortunately, there are some relatively "simple" things that I can't seem to determine by looking at the data in it's raw form.  I suspect those who have worked with the database directly will know this stuff without having to think much about it.  Please advise if you can.  Thanks in advance:

1) Sales Order Status seems to be a very limited set of values, and I don't know how to translate them.  Is there more to it?

2) How do I determine if an order was cancelled?

3) I cannot seem to tie return tables to sales tables.  It doesn't seem that any of the Sales Order/Line ID's exist in the return tables.

4) Is there a general reference of some kind that would allow me to research these types of basic questions myself?

Happy coding!

  • TerryMitchell Profile Picture
    TerryMitchell 10 on at
    RE: Order SQL Queries

    Thanks for the quick response.  Unfortunately, what I'm finding in my database doesn't seem to match.

    1) My [$Sales Header] has status values of 0 and 4.

    3) There are no records in either of these tables: [$Return Shipment Header] and [$Return Shipment Line].

    4) I checked out the link, and it looks familiar.  I didn't catch where the schema is documented.  Did I miss something when I scanned through the contents?

    Thanks.

  • Suggested answer
    Robert B. Profile Picture
    Robert B. 257 on at
    RE: Order SQL Queries

    Hi Terry,

      I will try to answer your questions:

    1) Open,Released,Pending Approval,Pending Prepayment

    Values in SQL are 0,1,2,3  This is an option field in NAV and has the above captions

    2) On Hold field or the order can be deleted.

    3) try Return Order No. in the Return Shipment Header and Return Order No. and Return Order Line No. in the Return Shipment Line

    4) https://docs.microsoft.com/en-us/dynamics-nav-app/

    Cheers,

    Robert

    If this helps in resolving your issue, please verify my answer!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,409 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans