Function to fetch minimum date

Comments

• Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2010-01-22

  The easiest is to create an index (key) on that field and do a findfirst. (1)
  
  Other solutions are to browse through the table with a variable (2) or to use the date table as temp table.(3)
  
  If you can spare the index I would go for option 1

  Marije Brummel | Freelance Business Central Specialist

  0
• mkpjsr Member Posts: 587

  2010-01-22

  Mark Brummel wrote:

  The easiest is to create an index (key) on that field and do a findfirst. (1)
  
  Other solutions are to browse through the table with a variable (2) or to use the date table as temp table.(3)
  
  If you can spare the index I would go for option 1

  
  Can u give me some hint on solution (2) and (3)

  0
• Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2010-01-22

  Why do you think solution 1 does not work?
  
  If you have more than let's say a thousand records in your table, solutions 2 and 3 will be dreadfully slow.

  Marije Brummel | Freelance Business Central Specialist

  0
• mkpjsr Member Posts: 587

  2010-01-23

  Mark Brummel wrote:

  Why do you think solution 1 does not work?
  
  If you have more than let's say a thousand records in your table, solutions 2 and 3 will be dreadfully slow.

  
  If the table contains more then one keys then will it work

  0
• matttrax Member Posts: 2,309

  2010-01-25

  mkpjsr wrote:

  If the table contains more then one keys then will it work

  
  Sure it will. In fact, most tables have more than one key. It's very common to need your data sorted a certain way. In your case you want it sorted by some data field. So you add a key / index.
  
  The trade off is that SQL (assuming you're on SQL) will take a slightly, not noticeable, longer amount of time to insert records. This is because it has to keep up with all of the indexes. You are trading a very small amount of time up front for less time down the road when you need to do calculations.

  Matt Traxinger - ArcherPoint

  0
• Marije_Brummel Member, Moderators Design Patterns Posts: 4,262

  2010-01-25

  Native DB needs more time as well.
  
  If you only do this function once a day or something and if you run on SQL you can have the key in NAV but disable it on SQL.

  Marije Brummel | Freelance Business Central Specialist

  0
• jevgjem Member Posts: 17

  2010-01-25

  mkpjsr wrote:

  Can u give me some hint on solution (2) and (3)

  
  //variable MinDate (date)
  
  if MyRec.findfirst then
  repeat
  if (MinDate > MyRec.Date) OR
  (MinDate = 0D) then
  MinDate := MyRec.Date;
  until myRec.next=0;
  
  But this solution is really slow on big amounts of data.

  0
• Belias Member Posts: 2,998

  2010-01-25

  jevgjem wrote:

  mkpjsr wrote:

  Can u give me some hint on solution (2) and (3)

  
  //variable MinDate (date)
  
  if MyRec.findfirst then
  repeat
  if (MinDate > MyRec.Date) OR
  (MinDate = 0D) then
  MinDate := MyRec.Date;
  until myRec.next=0;
  
  But this solution is really slow on big amounts of data.

  even slower with your code!!
  NEVER USE FINDFIRST WITH "REPEAT...UNTIL" STATEMENTS, PLEASE READ ONLINE HELP!

  This function should be used instead of FIND('-') when you only need the first record.
  
  You should only use this function when you explicitly want to find the first record in a table or set. Do not use this function in combination with REPEAT..UNTIL.

  For further informations about WHY don't use it, look here http://www.mibuso.com/howtoinfo.asp?FileID=22 or search mibuso or waldo's blog or microsoft manuals

  -Mirko-
  "Never memorize what you can easily find in a book".....Or Mibuso
  My Blog

  0