Options

Date Filter In Item By Location Report

mkpjsrmkpjsr Member Posts: 587
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.

Comments

  • Options
    crisnicolascrisnicolas Member Posts: 177
    Date Filter is not applied to field Inventory. It is applied to field Net Change. Check the definitions of those two fields
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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?
  • Options
    crisnicolascrisnicolas Member Posts: 177
    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
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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?????
  • Options
    ssinglassingla Member Posts: 2,973
    Can you specify the date filter given by you.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • Options
    vaprogvaprog Member Posts: 1,130
    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"
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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
  • Options
    vijay_gvijay_g Member Posts: 884
    try it
    SETFILTER("Date Filter",'%1',ItemDateFilter);
    IF Location.FINDSET THEN REPEAT
       ArrayTitle[Int] := Location.Name;
       SETFILTER("Location Filter",Location.Code);
    
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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.
  • Options
    vaprogvaprog Member Posts: 1,130
    vaprog wrote:
    SETFILTER("Date Filter",'..%1',InventoryDate);
    
  • Options
    vijay_gvijay_g Member Posts: 884
    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);
    
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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;
    
    
  • Options
    vijay_gvijay_g Member Posts: 884
    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.
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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.
  • Options
    vaprogvaprog Member Posts: 1,130
    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.
  • Options
    mkpjsrmkpjsr Member Posts: 587
    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.
  • Options
    vaprogvaprog Member Posts: 1,130
    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.
Sign In or Register to comment.