fbpx
Welcome, Guest
Username: Password: Remember me
Visual Objects

Please use this forum to post questions about Visual Objects and Vulcan.NET
  • Page:
  • 1

TOPIC: Indexing Problem with Logic and Date

Indexing Problem with Logic and Date 2 weeks 1 day ago #9696

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Hi again. I'm having a problem where I need to create an index (DBFCDX) on a date field (JobDate) where the field JobFin is FALSE. The dbserver is Details. I've tried things such as Setordercondition, LtoC even MYLtoC (as suggested by Jamal in the old forum and another method posted by someone else(Phil? Pierre?) etc

I've used DTOC(_FIELD->JOBDATE) and DTOS etc.

Nothing works. Where am I going wrong please.

Thanks

Jeff

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9697

  lumberjack's Avatar lumberjack Offline Posts: 494
Jeff,

BiggyRat wrote: Hi again. I'm having a problem where I need to create an index (DBFCDX) on a date field (JobDate) where the field JobFin is FALSE. The dbserver is Details. I've tried things such as Setordercondition, LtoC even MYLtoC (as suggested by Jamal in the old forum and another method posted by someone else(Phil? Pierre?) etc
I've used DTOC(_FIELD->JOBDATE) and DTOS etc.
Nothing works. Where am I going wrong please.

Can you post the line of your code and variable settings that you use to create the index with?
______________________
Johan Nel
George, South Africa

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9698

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Sure Johan, thanks.


METHOD OptionsReindex CLASS MainMenu

LOCAL oServer as DBServer
LOcal i

RddSetDefault("DBFCDX")
oServer := DbServer{"Details"}
For i := 1 to oServer:Reccount
oServer:GoTo(i)
IF EmptyRecord() == .T.
oServer:DELETE()
oServer:Skip(-1)
endif
next i

oServer:PACK()

oServer:CreateOrder("CLCode", "Details", "CLCode" )
oServer:CreateOrder("CLCodeName", "Details", "CLCode + ' ' + CLNAME" )
oServer:CreateOrder("JobDate", "Details", "JobDate")
//oServer:CreateOrder("JobDateFin", "Details", "DToC(Field->JobDate) + MyLtoc(Field->JobFin)")
oServer:CreateOrder("JobFin", "Details", "JobFin")
oServer:CreateOrder("JobFinYes", "Details", "JobFin = .T.")
oServer:CreateOrder("JobFinNo", "Details", "JobFin = .F.")
oServer:CreateOrder("JobNumber", "Details", "JobNumber")
oServer:CreateOrder("PONumber", "Details", "PONumber",, .T.)
oServer:CreateOrder("Subby", "Details", "Subby",,)
oServer:CreateOrder("Rego", "Details", "Rego")
oServer:CreateOrder("Invoiced", "Details", "Invoiced")
oServer:CreateOrder("InvFile", "Details", "InvFile")
oServer:CreateOrder("MapFile", "Details", "MapFile")
oServer:CLOSE()

Return Nil

Function MyLtoc(lLogicValue)
return LTOC(lLogicValue)

Please Log in or Create an account to join the conversation.

Last edit: by BiggyRat.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9699

  lumberjack's Avatar lumberjack Offline Posts: 494
Hi Jeff,

BiggyRat wrote:

Can you try this? Not sure if the issue is actually the date index, and not the conditional index.
METHOD OptionsReindex CLASS MainMenu
   RddSetDefault("DBFCDX") // Do you need to set this here?  Why not in your Start() method at program start?
   oServer := DbServer{"Details"}
   For i := 1 to oServer:Reccount 
	   	oServer:GoTo(i)
	   	IF EmptyRecord() == .T.
   			oServer:DELETE()
   			oServer:Skip(-1) // Is this necessary?  Just curios as the :GoTo() will handle movement speed penalty
	   	endif
	   next i
 
	   oServer:PACK()  

   oServer:CreateOrder("CLCode", "Details", "CLCode" ) 
   oServer:CreateOrder("CLCodeName", "Details", "CLCode + ' ' +  CLNAME" )
   oServer:CreateOrder("JobDate", "Details", "DToS(JobDate)")  // Always use DToS() for date indexes  
   //oServer:CreateOrder("JobDateFin", "Details", "DToC(Field->JobDate) + MyLtoc(Field->JobFin)")

 	oServer:CreateOrder("JobDateFin", "Details", "DToS(JobDate) + MyLtoC(JobFin)")       

 	oServer:CreateOrder("JobFin", "Details", "JobFin") 
 	//oServer:CreateOrder("JobFinYes", "Details", "JobFin = .T.") // You sure this is not where the problem is?
  	//oServer:CreateOrder("JobFinNo", "Details", "JobFin = .F.")

 	oServer:SetOrderCondition("JobFin = .T.") // I think this is what you were actually trying to do
 	oServer:CreateOrder("JobFinYes", "Details", "JobFin")
 	oServer:SetOrderCondition("JobFin = .F.") // I think this is what you were actually trying to do
  	oServer:CreateOrder("JobFinNo", "Details", "JobFin")
 	oServer:SetOrderCondition() 


  	oServer:CreateOrder("JobNumber", "Details", "JobNumber")  
  	oServer:CreateOrder("PONumber", "Details", "PONumber",, .T.) 
 	oServer:CreateOrder("Subby", "Details", "Subby",,)
	oServer:CreateOrder("Rego", "Details", "Rego") 
  	oServer:CreateOrder("Invoiced", "Details", "Invoiced")
	oServer:CreateOrder("InvFile", "Details", "InvFile")
	oServer:CreateOrder("MapFile", "Details", "MapFile")  
	oServer:CLOSE()

Return Nil

Function MyLtoc(lLogicValue)
  return LTOC(lLogicValue)
______________________
Johan Nel
George, South Africa

Please Log in or Create an account to join the conversation.

Last edit: by lumberjack.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9700

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Thanks very much Johan, You're very probably right. I'll check it out shortly. The RDDSETDEFAULT you rightly spotted is in my Start() method, but my VO has been doing bizarre things today (most notably telling me a Method doesn't have a RETURN value, even though it clearly DOES, and then when I compile, a whole large section of code is just GONE. From where ever I had been typing to the end of that section of code, That happened here, so I copied my database/index creation code into this method. I just didn't remove it as yet :))

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9701

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Spot on! Thank you very much Johan.

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9702

  lumberjack's Avatar lumberjack Offline Posts: 494

BiggyRat wrote: Spot on! Thank you very much Johan.

Great! You welcome kind sir
______________________
Johan Nel
George, South Africa

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9703

  lumberjack's Avatar lumberjack Offline Posts: 494
Hi Jeff,

BiggyRat wrote: Sure Johan, thanks.


Just some observations in your code:
oServer := DbServer{"Details"}
// Are there a specific reason why you used a for loop and not
While !oServer:Eof
	IF EmptyRecord() == .T.
   		oServer:DELETE()
		oServer:Skip(-1) // Do you have SET DELETED ON/OFF that you skip backwards?
		// Without knowing what EmptyRecord is doing, the skip -1 might end up in an endless loop
	ENDIF
	oServer:Skip(1)
Enddo
oServer:PACK()  
// Before creating the orders, I will actually do a physical delete of the Details.CDX file
//  Otherwise the Index driver need to manage the pages inside the existing CDX for the rebuild.
// There is quite a performance penalty creating the orders in an existing CDX compared to starting
// in an empty CDX
oServer:CreateOrder("CLCode", "Details", "CLCode" ) 
....rest of creates...
oServer:CLOSE()
______________________
Johan Nel
George, South Africa

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9704

  Karl-Heinz's Avatar Karl-Heinz Offline Posts: 302
Hi Johan,

i think the createorder() index expression must be "Dtos(Jobdate)", otherwise the dates are not sorted. BTW: I never use Dtoc(), but Dtos() to build a date index.

oServer:SetOrderCondition("JobFin = .T.") // I think this is what you were actually trying to do
oServer:CreateOrder("JobFinYes", "Details", "Dtos(Jobdate)")  // <--- instead of "JobFin" 
oServer:SetOrderCondition("JobFin = .F.") // I think this is what you were actually trying to do
oServer:CreateOrder("JobFinNo", "Details", "Dtos(Jobdate)") // <---  instead of "JobFin"  
oServer:SetOrderCondition() 

regards
Karl-Heinz

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9705

  wriedmann's Avatar wriedmann Offline Posts: 1631
Hi Jeff,
never use DToC(), but use always DToS().
DToC() depends on your current application setting, and changing that would invalidate all your indexes.
Regarding your IDE problems: I don't know why, but it seems you always have stability problems.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9706

  lumberjack's Avatar lumberjack Offline Posts: 494
Hi Karl-Heinz,

Karl-Heinz wrote: i think the createorder() index expression must be "Dtos(Jobdate)", otherwise the dates are not sorted. BTW: I never use Dtoc(), but Dtos() to build a date index.

Yes you are correct, DtoC() can have strange effects since it is dependent on the DateFormat. All my date index expressions are done with DtoS() when I still use DBF.

I did not omit it intentionally, was a bit of oversight on my side, since in the context I thought it was not the order which caused issues but the "FOR" condition of the subsequent orders that Jeff wanted to create. I will correct my answers to reflect it. Thanks!
______________________
Johan Nel
George, South Africa

Please Log in or Create an account to join the conversation.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9707

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Thanks guys. Up until towards the end (before posting here) I was using DTOS, but thought as it was MYLtoC, maybe I'd have more luck with DtoC (thought maybe something in String/Character conversion perhaps. Just clutching at straws by that stage )

Thank you very much again Johan, Karl-Heinz and Wolfgang. I've taken all your advice and suggestions onboard (as I always do). It's been extremely helpful. Thank you.

Please Log in or Create an account to join the conversation.

Last edit: by BiggyRat.

Indexing Problem with Logic and Date 2 weeks 1 day ago #9709

  BiggyRat's Avatar Topic Author BiggyRat Offline Posts: 168
Thanks guys. Up until towards the end (before posting here) I was using DTOS, but thought as it was MYLtoC, m s the id have more luck with DtoC (thought maybe something in String/Character conversion perhaps. Just clutching at straws by that stage )

Thank you very much again Johan, Karl-Heinz and Wolfgang. I've taken all your advice and suggestions onboard. It's been extremely helpful. Thank you.

Please Log in or Create an account to join the conversation.

  • Page:
  • 1