Indexing Problem with Logic and Date

[Anonymous]

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

[lumberjack]

Jeff,

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?

[BiggyRat]

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)

[lumberjack]

Hi Jeff,

Can you try this? Not sure if the issue is actually the date index, and not the conditional index.

Code: Select all

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)

[BiggyRat]

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 )

[BiggyRat]

Spot on! Thank you very much Johan.

[lumberjack]

Spot on! Thank you very much Johan.

Great! You welcome kind sir

[lumberjack]

Hi Jeff,

Sure Johan, thanks.

Just some observations in your code:

Code: Select all

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()

[Karl-Heinz]

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.

Code: Select all

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

[wriedmann]

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