Backdated inventory?

navfresher

How to get the backdated inventory of a particular item?I need to print them in a report

From which table i can get it?

kitik

Add a Date Filter in the "Item Ledger Entry" table and add the Quantity field for all records.
Or create a FlowField on the Item table to get you the value.

Salut!

Miklos_Hollender

Date Filter + the Net Change field in Item.

kitik

Thanks Miklos, I didn't see the Net Change field on the table.

Salut!

ssingla

Field No. 70 Net Change Item Table

kitik

I ment that I didn't see the field *before* Miklos post.
Sorry for the misunderstanding, my english knowledge makes me write this :-p

Thanks anyway,
Salut!

navfresher

Miklos Hollender wrote:

Date Filter + the Net Change field in Item.

Thank you...but a request from my side :

can you just explain me what does that "Net change" filed mean?

and also can you explain me the logic behind the solution you gave and how it works?

Kowa

navfresher wrote:

can you just explain me what does that "Net change" filed mean?

"Inventory" and "Net change" are both flowfields in the item table which have similar calcformulas, the only difference is that "Net change" take take a date filter into account. So "Inventory" can only be used to show the stock qty as of today, whereras "Net change" can show you same for any day in the past. Just set a date filter (this is a flowfilter field) like "..31.12.08" ( if your date format is DDMMYY, otherwise of course use your local date format ) to see the inventory at the end of 2008.

You will find a "Net change" field in other master tables too (G/L account etc.)

navfresher

Kowa wrote:

navfresher wrote:

can you just explain me what does that "Net change" filed mean?

"Inventory" and "Net change" are both flowfields in the item table which have similar calcformulas, the only difference is that "Net change" take take a date filter into account. So "Inventory" can only be used to show the stock qty as of today, whereras "Net change" can show you same for any day in the past. Just set a date filter (this is a flowfilter field) like "..31.12.08" ( if your date format is DDMMYY, otherwise of course use your local date format ) to see the inventory at the end of 2008.

You will find a "Net change" field in other master tables too (G/L account etc.)

My heartful thanks for your time,but my real concern is this :

I am making a report where i need to print today's inventory as well as 1 week's past inventory.i can fetch todays date from the request field and i am storing week before date by substracting 7days from the today's date.But,While using some filter i am not able to use that variable which i used to store 7days before date.

Any solution for me?

Kowa

Create an second item record variable (Item2), apply the date filter for the last week to that, use GET with the item no. to fetch the same item and do CALCFIELDS for the "Net change" field with this record and use Item2."Net change" as the source expression in the control for last weeks inventory.

Item2.get(Item."No.");
Item2.setfilter("Date Filter",'..%1',Calcdate('<-7D>'));
Item2.calcfields("Net Change");

bstoyano

Hi
Why are you not using the standard report Inventory valuation and set the proper filters?

Kowa

bstoyano wrote:

Hi
Why are you not using the standard report Inventory valuation and set the proper filters?

If you need both values side by side in one report you need to do some coding. Of course you can use the report and regard last weeks inventory as the starting date, but if you want to create own reports and learn some C/AL techniques that won't help you.

navfresher

Kowa wrote:

Create an second item record variable (Item2), apply the date filter for the last week to that, use GET with the item no. to fetch the same item and do CALCFIELDS for the "Net change" field with this record and use Item2."Net change" as the source expression in the control for last weeks inventory.

Item2.get(Item."No.");
Item2.setfilter("Date Filter",'..%1',Calcdate('<-7D>'));
Item2.calcfields("Net Change");

Hi Kowa,

This code is still printing the inventory on the date filter which i have set and not week's before inventory.What could be the reason?

bstoyano

I am just a consultant, not a developer. My advice is from a user's point of view.

Kowa wrote:

bstoyano wrote:

Hi
Why are you not using the standard report Inventory valuation and set the proper filters?

If you need both values side by side in one report you need to do some coding. Of course you can use the report and regard last weeks inventory as the starting date, but if you want to create own reports and learn some C/AL techniques that won't help you.

Kowa

navfresher wrote:

Hi Kowa,

This code is still printing the inventory on the date filter which i have set and not week's before inventory.What could be the reason?

That code and the modified source expression is really all you need. I have created a simple report so you can see for yourself that it works.

OBJECT Report 50066 Inventory Comparison
{
  OBJECT-PROPERTIES
  {
    Date=24.01.10;
    Time=13:43:35;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table27;
        DataItemTableView=SORTING(No.);
        OnAfterGetRecord=BEGIN
                           Item2.GET(Item."No.");
                           Item2.SETFILTER("Date Filter",'..%1',CALCDATE('<-7D>'));
                           Item2.CALCFIELDS("Net Change");
                         END;

        TotalFields=Net Change,Inventory;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1119300001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[DEU=Artikel;
                                                                    ENU=Item] }
            { 1119300002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1119300003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1119300004;TextBox;17700;423  ;450  ;423  ;CaptionML=DEU=Seite;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1119300005;Label  ;16950;423  ;750  ;423  ;ParentControl=1119300004 }
            { 1119300006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1119300009;Label  ;0    ;0    ;1500 ;846  ;ParentControl=1119300008;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1119300012;Label  ;1650 ;0    ;4500 ;846  ;ParentControl=1119300011;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1119300015;Label  ;6300 ;0    ;1800 ;846  ;ParentControl=1119300014;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
            { 1119300018;Label  ;8250 ;0    ;1800 ;846  ;ParentControl=1119300017;
                                                         VertAlign=Bottom;
                                                         FontBold=Yes;
                                                         MultiLine=Yes }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1119300008;TextBox;0    ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1119300011;TextBox;1650 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
            { 1119300014;TextBox;6300 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Inventory }
            { 1119300017;TextBox;8250 ;0    ;1800 ;423  ;HorzAlign=Right;
                                                         CaptionML=[DEU=Lagerbestand Vorwoche;
                                                                    ENU=Last weeks Inventory];
                                                         DecimalPlaces=0:5;
                                                         SourceExpr=Item2."Net Change" }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      Item2@1119300000 : Record 27;

    BEGIN
    END.
  }
}