mibuso.com
Home› NAV/Navision Classic Client

Form taking long time

abhi1901abhi1901 Member Posts: 102
edited 2012-06-28 in NAV/Navision Classic Client
Good Morning Experts!!!

I have made a form and made a function in it to show the Quantity and Value of Sales Quantity from Item Ledger for certain Period.
I have introduce the periodic update in every 3 min.
All thing are working fine in small DB.

But when I have tested it in Large DB it is taking a long time, means even after 5-6 min it still cant calculate the values.
I have used SETCURRENTKEY but still the problem is same.

Can you can suggest me any other way to sort of the problem.

Comments

  • udayrmerudayrmer Member Posts: 171
    Can you please elaborate more & share the code ?
    Uday Mer | MS Dynamics NAV Techno-Functional Consultant
  • abhi1901abhi1901 Member Posts: 102
    Like this I have Made 3 functions
    //
    Fun 1
    FindToday()
    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",TODAY);
    IF ILE.FINDSET THEN REPEAT
    ILE.CALCFIELDS(ILE."Cost Amount (Actual)");
    IF ILE."Entry Type"=ILE."Entry Type"::Purchase THEN BEGIN
    PurchQtyToday+=ILE.Quantity;
    PurchValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Sale THEN BEGIN
    SaleQtyToday+=ILE.Quantity;
    SaleValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Shipment")THEN BEGIN
    TSQtyToday+=ILE.Quantity;
    TSValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Receipt")THEN BEGIN
    TRQtyToday+=ILE.Quantity;
    TSValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Output THEN BEGIN
    OutQtyToday+=ILE.Quantity;
    OutValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Consumption THEN BEGIN
    ConQtyToday+=ILE.Quantity;
    ConValueToday+=ILE."Cost Amount (Actual)";
    END;
    UNTIL ILE.NEXT=0;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",TODAY);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKPYMTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankPayToday:=BankLedgerEntry.COUNT;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",TODAY);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKRCPTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankRcptToday:=BankLedgerEntry.COUNT;
    //
    Fun 1

    But it is taking a large amount of time to calculate because of the no. of entries are very large.
  • udayrmerudayrmer Member Posts: 171
    My suggestion is to use calcsum for this key, this will drastically put down your execution time.
    Uday Mer | MS Dynamics NAV Techno-Functional Consultant
  • abhi1901abhi1901 Member Posts: 102
    udayrmer wrote:
    My suggestion is to use calcsum for this key, this will drastically put down your execution time.


    I can't use CalcSum because in other functions I am calculating the quantity for specific date, So it will not going to work.

    Is there any other process.
  • udayrmerudayrmer Member Posts: 171
    Why not you can also get sum for specific date
    Uday Mer | MS Dynamics NAV Techno-Functional Consultant
  • abhi1901abhi1901 Member Posts: 102
    udayrmer wrote:
    Why not you can also get sum for specific date

    //
    First Loop to calculate Today's Stock
    FindToday()
    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",TODAY);
    IF ILE.FINDSET THEN REPEAT
    ILE.CALCFIELDS(ILE."Cost Amount (Actual)");
    IF ILE."Entry Type"=ILE."Entry Type"::Purchase THEN BEGIN
    PurchQtyToday+=ILE.Quantity;
    PurchValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Sale THEN BEGIN
    SaleQtyToday+=ILE.Quantity;
    SaleValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Shipment")THEN BEGIN
    TSQtyToday+=ILE.Quantity;
    TSValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Receipt")THEN BEGIN
    TRQtyToday+=ILE.Quantity;
    TSValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Output THEN BEGIN
    OutQtyToday+=ILE.Quantity;
    OutValueToday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Consumption THEN BEGIN
    ConQtyToday+=ILE.Quantity;
    ConValueToday+=ILE."Cost Amount (Actual)";
    END;
    UNTIL ILE.NEXT=0;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",TODAY);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKPYMTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankPayToday:=BankLedgerEntry.COUNT;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",TODAY);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKRCPTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankRcptToday:=BankLedgerEntry.COUNT;
    //
    First Loop to calculate Today's Stock

    //
    Second Loop to calculate yesterday's Stock
    FindBeforeToday()
    YesterDay:=CALCDATE('<-1D>',TODAY);

    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",YesterDay);
    IF ILE.FINDSET THEN REPEAT
    ILE.CALCFIELDS(ILE."Cost Amount (Actual)");
    IF ILE."Entry Type"=ILE."Entry Type"::Purchase THEN BEGIN
    PurchQtyYesterday+=ILE.Quantity;
    PurchValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Sale THEN BEGIN
    SaleQtyYesterday+=ILE.Quantity;
    SaleValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Shipment")THEN BEGIN
    TSQtyYesterday+=ILE.Quantity;
    TSValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Receipt")THEN BEGIN
    TRQtyYesterday+=ILE.Quantity;
    TSValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Output THEN BEGIN
    OutQtyYesterday+=ILE.Quantity;
    OutValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Consumption THEN BEGIN
    ConQtyYesterday+=ILE.Quantity;
    ConValueYesterday+=ILE."Cost Amount (Actual)";
    END;
    UNTIL ILE.NEXT=0;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",YesterDay);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKPYMTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankPayYesterday:=BankLedgerEntry.COUNT;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETRANGE(BankLedgerEntry."Posting Date",YesterDay);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKRCPTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankRcptYesterday:=BankLedgerEntry.COUNT;
    //
    Second Loop to calculate yesterday's Stock

    //
    Third Loop to calculate Stock between given date filter
    FindDateFilter()
    IF MyFromDate=0D THEN
    MyFromDate:=DMY2DATE(1,1,2010);
    IF MyToDate=0D THEN
    MyToDate:=TODAY;

    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETFILTER(ILE."Posting Date",'%1..%2',MyFromDate,MyToDate);
    IF ILE.FINDSET THEN REPEAT
    ILE.CALCFIELDS(ILE."Cost Amount (Actual)");
    IF ILE."Entry Type"=ILE."Entry Type"::Purchase THEN BEGIN
    PurchQtyDateFilter+=ILE.Quantity;
    PurchValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Sale THEN BEGIN
    SaleQtyDateFilter+=ILE.Quantity;
    SaleValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Shipment")THEN BEGIN
    TSQtyDateFilter+=ILE.Quantity;
    TSValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    IF (ILE."Entry Type"=ILE."Entry Type"::Transfer) AND(ILE."Document Type"=ILE."Document Type"::"Transfer Receipt")THEN BEGIN
    TRQtyDateFilter+=ILE.Quantity;
    TSValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Output THEN BEGIN
    OutQtyDateFilter+=ILE.Quantity;
    OutValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    IF ILE."Entry Type"=ILE."Entry Type"::Consumption THEN BEGIN
    ConQtyDateFilter+=ILE.Quantity;
    ConValueDateFilter+=ILE."Cost Amount (Actual)";
    END;
    UNTIL ILE.NEXT=0;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Posting Date",'%1..%2',MyFromDate,MyToDate);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKPYMTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankPayDateFilter:=BankLedgerEntry.COUNT;

    BankLedgerEntry.RESET;
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Posting Date",'%1..%2',MyFromDate,MyToDate);
    BankLedgerEntry.SETFILTER(BankLedgerEntry."Source Code",'BANKRCPTV');
    IF BankLedgerEntry.FINDFIRST THEN
    BankRcptDateFilter:=BankLedgerEntry.COUNT;
    //
    Third Loop to calculate Stock between given date filter


    I have call these function on Validate trigger of Date filter on my form.
    At same time all function will run together.
    So how can i use CalcSum property. It will create complexity.
  • udayrmerudayrmer Member Posts: 171
    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",TODAY);
    ILE.SETRANGE(ILE."Entry Type",ILE."Entry Type"::Sale);//define as per required filters
    ILE.CALCSUMS(Quantity,"Cost Amount(Actual));
    assign value to appropriate variable,

    execute above same code, by changing filter on posting date
    Uday Mer | MS Dynamics NAV Techno-Functional Consultant
  • abhi1901abhi1901 Member Posts: 102
    udayrmer wrote:
    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",TODAY);
    ILE.SETRANGE(ILE."Entry Type",ILE."Entry Type"::Sale);//define as per required filters
    ILE.CALCSUMS(Quantity,"Cost Amount(Actual));
    assign value to appropriate variable,

    execute above same code, by changing filter on posting date

    ok. thanks.

    But it is showing Error because Cost Amount(Actual) is a Flow field and we can not use flow field in calcsum.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    You are going about this the wrong way, you need to rethink the data structure, and calculate the correct numbers from the correct tables. Please look at Value Entry as a starting point.
    David Singleton
  • abhi1901abhi1901 Member Posts: 102
    abhi1901 wrote:
    udayrmer wrote:
    ILE.RESET;
    ILE.SETCURRENTKEY(ILE."Entry Type",ILE."Posting Date");
    ILE.SETRANGE(ILE."Posting Date",TODAY);
    ILE.SETRANGE(ILE."Entry Type",ILE."Entry Type"::Sale);//define as per required filters
    ILE.CALCSUMS(Quantity,"Cost Amount(Actual));
    assign value to appropriate variable,

    execute above same code, by changing filter on posting date

    ok. thanks.

    But it is showing Error because Cost Amount(Actual) is a Flow field and we can not use flow field in calcsum.


    Ok but also if I will write the code to navigate to Value Entry table and calcsum the cost amount from there then also it will going to take much more time. :-k :-k
  • udayrmerudayrmer Member Posts: 171
    OK so for cost you have to cascsum from value entry
    Uday Mer | MS Dynamics NAV Techno-Functional Consultant
  • abhi1901abhi1901 Member Posts: 102
    David Singleton wrote:
    You are going about this the wrong way, you need to rethink the data structure, and calculate the correct numbers from the correct tables. Please look at Value Entry as a starting point.


    OK you have suggested the answer I am going to replace my coding from ILE to Value Entry and see if that solves my problem or not.
Sign In or Register to comment.