Inventory Movement Report

Business Central/NAV Developers Forum
1

I am developing Inventory Report from the Item table but finding difficulties to get the information expected. Please help.

Basically the report is only displays 4 columns: Beginning Inventory as of Date Filter, Incoming (Purchase + Positive Adj.), Outgoing (Sales + Negative Adj.) and Ending Inventory.

I added 1 Request Form to let the User enter the Movement Ending Date.

The Beginning Inventory is calculated base on the Date Filter entered; it can be As of Date or certain period of date (for example p1…p6).

I want the system is calculating the Incoming, Outgoing and Ending Inventory based on the day after Date Filter up to the Movement Ending Date. The system is only check the biggest date value of the Date Filter entered.

For example:

  • If the User enter the Date Filter as …12/31/05 and Ending Date : 05/31/06; then, Navision will calculate the movement (column #2, #3 and #4) from 01/01/06 up to 05/31/06.

  • If the User enter the Date Filter 03/01/06…03/30/06 and Ending Date : 05/31/06; then, Navision will calculate the movement (column #2, #3 and #4) from 04/01/06 up to 05/31/06.

What is the command for above request?

I have tried the following coding but they are not giving the information needed:

  1. OnAfterGetRecord()

SETRANGE(“Date Filter”,’%1>’,EndingDate);

CALCFIELDS(“Purchases (Qty.)”,“Positive Adjmt. (Qty.)”,“Negative Adjmt. (Qty.)”,"Sales

(Qty.)",

  1. OnAfterGetRecord()

SETRANGE(“Date Filter”,”Date Filter”+1, EndingDate);

CALCFIELDS(“Purchases (Qty.)”,“Positive Adjmt. (Qty.)”,“Negative Adjmt. (Qty.)”,"Sales

(Qty.)",

Thank you for helping.

ZEN [:)]

2

Is This Similar to the Item Turnover Report w/ some changes?

Report 10146

3

I am not sure the similiarity but the Date Filter on this report is more flexible than Report 10146.

Thanks,

ZEN

4

Dear,

Check the filter that u are taken on Item Dataitem on Date Range Filter. this problem is solved by me. if u want then i will send u the fob of this report.

5

Zen,

I think this is what you are looking for:

Item - OnPreDataItem()
LowDate := GETRANGEMIN(“Date Filter”);
HighDate :=GETRANGEMAX(“Date Filter”);
if AsofDate =0D then
AsOfDate :=TODAY;
StartDate :=CALCDATE(’+1D’,LowDate);

Item - OnAfterGetRecord()
SETRANGE(“Date Filter”,StartDate,AsofDate);
calcfields(“Purchases (Qty.)”,“Sales (Qty.)”,“Positive Adjmt. (Qty.)”,“Negative Adjmt. (Qty.)”);
SETRANGE(“Date Filter”,0D,AsofDate);
calcfields(“Date Based Inventory”);

Note the Inventory field on the item card does not calculate the on-onhand inventory using the date filter, you will need to create a new flow field for this and include all the same filters that are on the “Purchases (qty.)” flow field but sum the same quantity field as the inventory field. Then when you calcualte the field it will show the on hand up to that date. Remember that when calculating the on-hand inventory to start at 0D date.

Hope this helps, if you need more let me know.

6

With Item.rec are you calculating the field Inventory (no Date Filter) or Net Change (Date FIlter)?

7

You are correct jsark- the net change field does have all the filters, and so you can get the on-hand inventory by setting the date filter to 0D…Asof Date. I didn’t see this field, so no need to add another flow field.

Thanks…

8

John,

I am apologized for responding late. Thank you for keep sharing your knowledge.

I tried your advise but I couldn’t find “Date Based Inventory”. Is it a new field that I have to create?

Thanks,

ZEN

9

Hi Zen,

Sorry for the confusion. No you do not need to add a new field, as jsark said you can use the net change field in the item table. Just replace “Date Based Inventory” to this field, it has the proper filters defined to the flow field calculation.

John

10

John,

I couldn’t find “Date Based Inventory” field in our item table.

Is it because of the version related?

ZEN

11

Hi Zen,

There is no field named Date Based Inventory, it was just my example name. You need to use the field named “Net Change”.

John

12

Shame on me John. [:$]

The report is done. Thank you for all the helps. You guys are awesome [Y] (as always).

Thanks again,

[:)] ZEN