Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Kevin Brennan]

Hi,

I’m new to the group – looking forward to expanding my knowledge of NAV.

From my welcome message – post a question about a challenge we are facing:

We would like to develop a query that shows customers and their orders and their items that are short.

Can anybody point us in the right direction?

Thanks,

-Kevin

——————————
Kevin Brennan
Coloramics LLC dba Mayco Colors
Hilliard OH
——————————

[Jeanmarie Curley]

Hi Kevin-
You can try back order fill by customer report or back order fill by item report. It will show all the items/customers with orders that have not shipped yet and are past their ship date. This report keys off of the date in the sales line for each item, not on the shipment date of the shipment fast tab. The item will not appear on backorder until the date on the sales line has passed- so for example if it is 11/10/20 and you run the report and the sales line shipment date is 11/10/20 the item will not show up. But if you run the report on 11/11/20 it will. It will only show items or customers for whom items are on back order- it will not show their entire order. 
Another thing we do sometimes is run the open sales order lines by following the instructions founf here. By the way we have found many useful tips on this site. It is definitely a good one to bookmark. 
https://www.olofsimren.com/add-order-lines-to-navigation-pane/ 

We use NAV18 and require lot tracking and our items have shelf life so it adds another layer of complexity to our backorder management- if you dont have these contstraints it may be easier to find a way to manage it strictly via reports. 

——————————
Jeanmarie Curley
Sr Director Bus Ops
Quality Biological Inc
Gaithersburg MD 20878
——————————
——————————————-

[Kevin Brennan]

Hi Jeanmarie,

Thanks –  

Question:  Is there a way, looking at a NAV report, that I can see the underlying tables/fields or query?  That’s really where I’m stuck.

Background:

Once we’ve found a feasible report on the NAV side, my next challenge is to “add the SQL” to a Vicinity/MRP “Vicinity Query” on the Vicinity side:  Vicinity is our MRP package that works alongside of NAV.

Within Vicinity, we have, for example, 100 items that MRP says “make right now.”  We need to add the NAV tables/fields for Customer/Orders/Shorted items to the existing Vicinity query, so that we can see “100 items that we need to make right now – but here’s the 10 that are holding up existing Customer Orders.”

——————————————-

[Jeanmarie Curley]

Hi Kevin – sorry but this is out of my expertise- I am just a user! ��

 

——————————————-

[David Stephenson]

Hi ?,

I’m not sure which version of NAV/BC you’re using, but I know in our #NAV2016? version if you’re wanting to see that information, you’ll need to look at the report in the Microsoft Dynamics NAV Development Environment.

It’s similar in BC, but you need to use Visual Studio Code and/or Report Builder to see the info.

If you’re just wanting to see what fields are available/being pulled on a page, you’ll want to “Zoom” (Ctrl-Alt-F1) on a Header or Order Line to see that information.
Luckily, Zooming works the same in all of the versions of NAV from 2009 to BC (as far as I know)

——————————
David Stephenson
IT Manager
Mity Inc.
——————————
——————————————-

[Kevin Brennan]

Hi David,

Thanks!  This is very helpful.   We will pursue that “path to the goal.”   (I know I’ve seen Report Builder.  Not sure if we have Visual Studio, but can get if we need…)

Regards,

-Kevin

——————————————-