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 3 months 1 week ago #9696

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
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 3 months 1 week ago #9697

  Johan Nel's Avatar Johan Nel Offline Posts: 568
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 3 months 1 week ago #9698

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
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 Jeff Rollo.

Indexing Problem with Logic and Date 3 months 1 week ago #9699

  Johan Nel's Avatar Johan Nel Offline Posts: 568
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 Johan Nel.

Indexing Problem with Logic and Date 3 months 1 week ago #9700

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
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 3 months 1 week ago #9701

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
Spot on! Thank you very much Johan.

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

Indexing Problem with Logic and Date 3 months 1 week ago #9702

  Johan Nel's Avatar Johan Nel Offline Posts: 568

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 3 months 1 week ago #9703

  Johan Nel's Avatar Johan Nel Offline Posts: 568
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 3 months 1 week ago #9704

  Karl-Heinz Rauscher's Avatar Karl-Heinz Rauscher Offline Posts: 372
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 3 months 1 week ago #9705

  Wolfgang Riedmann's Avatar Wolfgang Riedmann Offline Posts: 1788
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

www.riedmann.it - docs.xsharp.it

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

Indexing Problem with Logic and Date 3 months 1 week ago #9706

  Johan Nel's Avatar Johan Nel Offline Posts: 568
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 3 months 1 week ago #9707

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
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 Jeff Rollo.

Indexing Problem with Logic and Date 3 months 1 week ago #9709

  Jeff Rollo's Avatar Topic Author Jeff Rollo Offline Posts: 209
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