Date Filter In Item By Location Report

mkpjsr · 2010-08-11

Hi all,

I have a report similar to the form 491 (Item By Location) that shows item's quantity in different location. Now i want to apply date filter to this report so that we can get to know the inventory status between two dates at differents locations.

But the date filter is not working, i have checked date filter field on item card and it does not contain any date, even for a single item.

Can anybody explain me what should I do to filter this report using date filter field of item.
I am attaching the screen shot of the report.

crisnicolas · 2010-08-11

Date Filter is not applied to field Inventory. It is applied to field Net Change. Check the definitions of those two fields

mkpjsr · 2010-08-11

crisnicolas wrote:

Date Filter is not applied to field Inventory. It is applied to field Net Change. Check the definitions of those two fields

Thanx for reply,
i have checked the definitions of these fields and i get to know that the value of both these fields are automatically calculated based on the quantity field of "Item Ledger Entery". I also noticed that both these fields are having same value.
So, now my question is: Can i use "net change" field instead of using inventory field?

crisnicolas · 2010-08-11

If you want to use Date Filter, then yes, you must use Net Change, as Inventory does not take into account dates to calculate the stock of an item

mkpjsr · 2010-08-14

crisnicolas wrote:

If you want to use Date Filter, then yes, you must use Net Change, as Inventory does not take into account dates to calculate the stock of an item

if i am using "Net Change" field and Date Filter, its not showing the correct Inventory Quantity.
Actually i want to show the inventory status for the given Date filter but its showing wrong quantity, i dont know from where that quantity is comming.

any help?????

ssingla · 2010-08-14

Can you specify the date filter given by you.

vaprog · 2010-08-14

On your sample report you have specified a date range. To get an inventory report you generally need a valuation date, not a date range, though. What do you want to get?

To get inventory at (the end of) a particular date you need do specify a Date Filter comprising the date range from beginning to that date i.e.

SETFILTER("Date Filter",'..%1',InventoryDate);
CALCFIELDS("Net Change");

Or more generally spoken, "Net Change" := "inventory at the end of the filtered period" - "inventory at the beginning of the filtered period"

mkpjsr · 2010-08-16

vaprog wrote:
On your sample report you have specified a date range. To get an inventory report you generally need a valuation date, not a date range, though. What do you want to get?

To get inventory at (the end of) a particular date you need do specify a Date Filter comprising the date range from beginning to that date i.e.
SETFILTER("Date Filter",'..%1',InventoryDate);
CALCFIELDS("Net Change");
Or more generally spoken, "Net Change" := "inventory at the end of the filtered period" - "inventory at the beginning of the filtered period"

i have used the code below to calculated the inventory location wise and store in the array
, where should i place the date filter.

CALCFIELDS("Net Change");
Int := 1;
TotalQty := ABS("Net Change");
IF Location.FINDSET THEN REPEAT
   ArrayTitle[Int] := Location.Name;
   SETFILTER("Location Filter",Location.Code);
//   SETFILTER("Date Filter",'%1',ItemDateFilter);
   CALCFIELDS("Net Change");
   ArrayValue[Int] := ABS("Net Change");
   Int += 1;
UNTIL Location.NEXT = 0;

can u please guide

vijay_g · 2010-08-16

try it

SETFILTER("Date Filter",'%1',ItemDateFilter);
IF Location.FINDSET THEN REPEAT
   ArrayTitle[Int] := Location.Name;
   SETFILTER("Location Filter",Location.Code);

mkpjsr · 2010-08-16

vijay_g wrote:

try it

SETFILTER("Date Filter",'%1',ItemDateFilter);
IF Location.FINDSET THEN REPEAT
   ArrayTitle[Int] := Location.Name;
   SETFILTER("Location Filter",Location.Code);

hi,
thanx for the reply i am facing problem with "Date Filter" filed

i have declared ItemdateFilter as -> Date and its not allowing me to have a range of date
like 01.07.10..31.07.10

and if i am giving a single date then nothing is displayed.

vaprog · 2010-08-16

vaprog wrote:

SETFILTER("Date Filter",'..%1',InventoryDate);

vijay_g · 2010-08-17

mkpjsr wrote:

i have declared ItemdateFilter as -> Date and its not allowing me to have a range of date
like 01.07.10..31.07.10

and if i am giving a single date then nothing is displayed.

then why don't you use startdate and enddate variable.
and write

SETRANGE("Date Filter",Startdate,enddate);

mkpjsr · 2010-08-17

vijay_g wrote:
mkpjsr wrote:

i have declared ItemdateFilter as -> Date and its not allowing me to have a range of date
like 01.07.10..31.07.10

and if i am giving a single date then nothing is displayed.

then why don't you use startdate and enddate variable.
and write
SETRANGE("Date Filter",Startdate,enddate);

hi,
i have tried this also but it not working. If i am giving only endDate then its giving the current inventory quantity but when i am giving StartDate its giving wrong quantity, code i have written is:

SETRANGE("Date Filter",StartDate,EndDate);
IF Location.FINDSET THEN REPEAT
   ArrayTitle[Int] := Location.Name;
   SETFILTER("Location Filter",Location.Code);

   CALCFIELDS("Net Change");
   ArrayValue[Int] := "Net Change";
   Int += 1;
UNTIL Location.NEXT = 0;

vijay_g · 2010-08-17

in this case you must fillout both start and end date either if you are running for a single day then you have to fill same date in both start and end date.

mkpjsr · 2010-08-17

vijay_g wrote:

in this case you must fillout both start and end date either if you are running for a single day then you have to fill same date in both start and end date.

if i am putting same date in Startdate and Enddate then also there is no change in the result still i am getting wrong quantity.

vaprog · 2010-08-17

If you don't want to use my suggestion

SETFILTER("Date Filter",'..%1',InventoryDate);

then please use

SETRANGE("Date Filter",0D,InventoryDate);

Anyway, you need to set the filter such that the filterd range corresponds to the formula I have given before:

vaprog wrote:

"Net Change" := "inventory at the end of the filtered period" - "inventory at the beginning of the filtered period"

.
I guarantee this will return the inventory at InventoryDate (according to what the system knows). If you don't get the expected value then please check InventoryDate and check Item Ledger Entry table.
If "inventory at (the end of) a particular date" is not what you want to get, then please define, what you want to get clearly.

If you filter according to vijay_g suggestion as SETRANGE("Date Filter",StartDate,EndDate); then what you get is net change on the location in that periode i.e. inflow - outflow which is equal to "inventory at the EndDate" - "inventory at the StartDate" which is the same as "Net Change".
If you set StartDate to 0D then you'll get inventory at EndDate, since inventory at 0D is 0.

mkpjsr · 2010-08-19

vaprog wrote:
If you don't want to use my suggestion
SETFILTER("Date Filter",'..%1',InventoryDate);
then please use
SETRANGE("Date Filter",0D,InventoryDate);
Anyway, you need to set the filter such that the filterd range corresponds to the formula I have given before:

vaprog wrote:

"Net Change" := "inventory at the end of the filtered period" - "inventory at the beginning of the filtered period"

.
I guarantee this will return the inventory at InventoryDate (according to what the system knows). If you don't get the expected value then please check InventoryDate and check Item Ledger Entry table.
If "inventory at (the end of) a particular date" is not what you want to get, then please define, what you want to get clearly.

If you filter according to vijay_g suggestion as SETRANGE("Date Filter",StartDate,EndDate); then what you get is net change on the location in that periode i.e. inflow - outflow which is equal to "inventory at the EndDate" - "inventory at the StartDate" which is the same as "Net Change".
If you set StartDate to 0D then you'll get inventory at EndDate, since inventory at 0D is 0.

thanx for the suggestion, but in the code

SETFILTER("Date Filter",'..%1',InventoryDate);

from where you are using InventoryDate, is it date Filter from Item table.

vaprog · 2010-08-19

You still have not answerd my question here, so i am still guessing what you want to get ...

vaprog wrote:

On your sample report you have specified a date range. To get an inventory report you generally need a valuation date, not a date range, though. What do you want to get?

Nevertheless, this statement of the same post the above quote is taken from should answer the question from your last post:

vaprog wrote:
To get inventory at (the end of) a particular date you need do specify a Date Filter comprising the date range from beginning to that date i.e.
SETFILTER("Date Filter",'..%1',InventoryDate);
CALCFIELDS("Net Change");

In other words, InventoryDate in my code is any date of your choosing, for which you want to know how many items were on stock.
You said

mkpjsr wrote:

i have declared ItemdateFilter as -> Date

Most likely this will be the value for you to use. But I am still just guessing, not knowing the details of what you are doing and what you want to achieve.

Date Filter In Item By Location Report

Comments

Categories