JET REPORTS - NAVISION - How do I pull through the Inventory column

(0) Share

Report

Posted on by Maryke86

I am trying to build a sales report to have an overview of all information on the sales lines, however I can't find the inventory. Currently we have an issue of invoicing and only then noticing that there is not sufficient stock of a specific item on the order. I am trying to pull through all released sales orders, qty on order not yet invoiced and qty in stock "inventory". When I open the order itself this Inventory column appears on the sales line together with Prod Qty Ready, Shipped, Qty Invoiced, Qty to invoice, Transfer Order No, etc

However, I find everything on the Sales line and Sales Header tables EXCEPT for the inventory. Can anyone tell me which table I can link to the sales line and sales header in order to pull through the inventory on hand and which description I should use? Even if i can pull it separately and do a V-lookup to pull through the current quantities based on the Item No?

Any advice?

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (14)

Answers (0)

Suggested answer

Dallefeld 211 User Group Leader on at

Like (1)

Report

What you are describing is not standard, so you must have a customization to your Sales Order screen. The field(s) that you are looking for is not part of the Sales Line table. You will need to read the Item table to get the fields you describe.

Was this reply helpful? Yes No
Maryke86 10 on at

Like (0)

Report

Hi Kim

Thank you for responding. Its not on the sales order main view. Its when you drill into the order and scroll down to the sales line you find all of the information I am referring to. I've pulled the item table into jet reports, but I don't find the inventory column? Does it possibly have another name? Because I am looking for current inventory on hand or will this be a calculation I need to do?

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

You need to look into Item card. Then On Hand QTY. You do not need to use vlookup if you have jet report. Use NL function the do something like below. The tricky part is to make sure Location on hand match Location of SO. You can add ""67 Location Filter"" to filter in Jet then filter by location.

In my below case Item No is in cell D9 but point it to related cell in your report.

Was this reply helpful? Yes No
Maryke86 10 on at

Like (0)

Report

Thank you so much. I am a beginner with jet reports and Excel formulas, so I am learning as I go. I will look into the NL function this evening. I've never used it before. I watched a video earlier also referring to the NL function. Thank you

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Are you using Table Builder in Jet?

Also, check this page. You can download all these reports. Inventory Location by Quantity on Hand report can give you a very good idea.

insightsoftware.com/.../

Was this reply helpful? Yes No
Maryke86 10 on at

Like (0)

Report

Yes I am using table builder in jet. I used the link you provided and downloaded those reports but when I refresh its not pulling through any of my data. I'm not surr what I am doing wrong.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Those reports have a guideline in their first page that walk you through how run those reports.

for table builder see this video youtu.be/ESANl8QITSU (around 15m 40s) notice how she add Vendor to the table builder. You need to add Table Item to your Table Builder. For you it is a chance that Link does not populate correctly. In this case you need to Link the No (in Item table) into No. in Sales Line. or any field that has the Item No in your report. Then you need to add QTY on Hand field from Item Table. If you use Location then use filter to filter for Location. If you have a filter for Location in your report then filter based on that field.

I hope this help.

Was this reply helpful? Yes No
Maryke86 10 on at

Like (0)

Report

Hi

Doesn't give me a qty on hand option on the item table

Was this reply helpful? Yes No
Maryke86 10 on at

Like (0)

Report

I see the quantity on hand by location you need jet reports 2019 or later. We have 2017

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

I forgot that QTY on Hand is the Flow Field. Select Flow Field on the bottom of that page (under Fields-Item) then you will see that.

Was this reply helpful? Yes No