mibuso.com

Microsoft Business Solutions online community
It is currently Mon Dec 29, 2014 9:52 am

All times are UTC + 1 hour




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: Form taking long time
PostPosted: Thu Jun 28, 2012 6:59 am 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 7:08 am 
Offline

Joined: Sat Aug 08, 2009 5:28 am
Posts: 162
Location: gujarat
Country: India (in)
Can you please elaborate more & share the code ?

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 7:12 am 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 7:29 am 
Offline

Joined: Sat Aug 08, 2009 5:28 am
Posts: 162
Location: gujarat
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 7:34 am 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 8:13 am 
Offline

Joined: Sat Aug 08, 2009 5:28 am
Posts: 162
Location: gujarat
Country: India (in)
Why not you can also get sum for specific date

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 8:19 am 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 11:53 am 
Offline

Joined: Sat Aug 08, 2009 5:28 am
Posts: 162
Location: gujarat
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 12:31 pm 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 1:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
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
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 2:42 pm 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 2:44 pm 
Offline

Joined: Sat Aug 08, 2009 5:28 am
Posts: 162
Location: gujarat
Country: India (in)
OK so for cost you have to cascsum from value entry

_________________
Uday Mer | MS Dynamics NAV Techno-Functional Consultant


Top
 Profile E-mail  
 
 Post subject: Re: Form taking long time
PostPosted: Thu Jun 28, 2012 2:47 pm 
Offline

Joined: Tue Jan 17, 2012 2:02 pm
Posts: 86
Country: India (in)
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.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: Google [Bot] and 20 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: