Form taking long time
abhi1901
Member Posts: 102
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.
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.
0
Comments
-
Can you please elaborate more & share the code ?Uday Mer | MS Dynamics NAV Techno-Functional Consultant0
-
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.0 -
My suggestion is to use calcsum for this key, this will drastically put down your execution time.Uday Mer | MS Dynamics NAV Techno-Functional Consultant0
-
Why not you can also get sum for specific dateUday Mer | MS Dynamics NAV Techno-Functional Consultant0
-
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.0 -
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 dateUday Mer | MS Dynamics NAV Techno-Functional Consultant0 -
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.0 -
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 Singleton0
-
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 :-k0 -
OK so for cost you have to cascsum from value entryUday Mer | MS Dynamics NAV Techno-Functional Consultant0
-
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions