Is there a way to get VFP to index a record...

[GriffMG]

I should really know this, but I can't remember!

I have an index where part of the expression actually does a look-up
in another table and I want to force an index update should that table be
altered... is there an Indexrecord() fuction?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

No, there's no function to force it to update a single record like that (as far as I know). But it shouldn't be necessary. Provided the original table is open at the time that the second table is updated, then the index should be updated as well.

At least, that's what I would assume. Have you actually tried doing that?

By the way, I'm sure some of the folk here will question the wisdom of having an index that depends on values in another table, but no doubt you have a good reason for it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

I do have a good reason, laziness - very old app where new user wants a parent list shown in an order partly
based on the last matching record in a child table. I didn't want to rewrite the whole form, so I added
an index tag based on a UDF that looks up the last matching record in child table and returns the value of a field from there.

I *KNOW* it's bad practice, and I should have used a different technique, but it's been ok, until today when the
index was clearly corrupt (out of date rather than broken) - probably because the child table had been updated
and the parent not so...

I should probably bite the bullet...



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Griff, before biting any bullets, you could perhaps do a controlled test: try to figure out in what circumstances the index gets updated OK, and in what circumstances it fails. (Given that it's worked in the past, but not today.) If you could pin that down, you might be able to avoid having to re-do the entire form. Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Well, any form that works on the child table without also working on the parent table is a candidate of that index to not update. The indexupdate is triggered by an update to the parent record.

I don't know, but you could simply try, even just setting any field of the parent table recods to it's current record would trigger an index update. With further experimenting you will find out, if that only is done on indexes including the altered fields, but I don't think so, I think any record update will cause all index tags for that record to be updated, too, otherwise you couldn't have an index on RAND() or DATETIME() or such functions not related to any table field of the same table or another table, or tsuch indexes would only be written at inserts.

So in short I think updating a record does cause what you search for as an indexrecord() function. Such function does not exist, but you can locate/seek a record, set field1=field1 or use Setfldstate() to set a field as altered without really altering it and then TABLEUPDATE() to update the index.

Bye, Olaf.

PS: Mike is right, I would not suggest to do this, but in the end he's right. If you can fix it that way, you'll have less effort. But this can bite you in any place you only update child data without also updating parent data and this makes it a solution with side effects. Something I won't like to maintain for a long time.

 

[GriffMG]

Mike and Olaf,

Thank you both for your thoughts, I will decide what to do in the new year... The client is in no rush to install
any updates anyway. It is an old app (12 years and a month!) and maintaining it is no real problem - when there
were hundreds of active users it was a bit more work, but there are probably only a couple of dozen 'live' copies
still in use.

There is a logical flag field on the parent table, I can set it and reset it without damaging the data, so I might
try that if the problem recurs

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Griff, be sure to let us know how things work out - and, in particular, if setting and resetting the logical field does the trick. I can't think that I would ever need to know this myself, but other people might well find it of interest.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

Will do

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Hi Griff,

instead of changing the data, SetFldState("boolfieldname",2,"parenttablename") should emulate a change of data, making Tableupdate() trigger an index update.
The only prerequisite is table or row buffering, otherwise you can't set a fields state to "modified", which is the meaning of 2 here.

If "very old app" means FP2.6, then this may not be applicable and switching the boolfield twice should also do the trick. But 12 years ago we had VFP6 and your app could also have been moved to VFP9 already, then SetFldState() is available. It would be like "touch" to file, it would really rewrite the value of the field marked as modified into the file, but it wouldn't change it's value and so you only need one Tableupdate().

Bye, Olaf.

 

[Olaf Doschke]

SetFldState/Tableupdate() works for me, you might try in your VFP version:

*Init
Clear 
Close tables All

Try
   MkDir c:\temp
Catch
   *
EndTry 
Erase c:\temp\test.dbf
Erase c:\temp\testchild.dbf

Create Table c:\temp\test.dbf (iID I autoinc, lBool L)
Create Table c:\temp\testchild.dbf (iID I autoinc, iTestID I, cChildtext C(10)) && iTestID = foreign key to test.iid

Select test
* Index on field of the child table
Index on testchild.cchildtext Tag xChildText

* Close all newly generated tables to reset
Close Tables All

* start testing
Set Multilocks On
CursorSetProp("Buffering",5,0)

* testchild has to be opened first (try reversing, you'll see)
Use c:\temp\testchild.dbf In 0
Use c:\temp\test.dbf In 0

Insert Into test (lBool) values (.T.)
Insert Into testchild (iTestID, cChildtext) Values (1,"aaa")

* first experiment, seek in test:
? "Seeking aaa, expect .T., really get ",Seek("aaa","test","xChildText")
* for comparison: Seek bbb
? "Seeking bbb, expect .F., really get ",Seek("bbb","test","xChildText")

* Now update child data only
Update testchild set cChildtext = "bbb"

* second experiment, seek in test, index not yet updated:
? "Seeking aaa, expect .T., really get ",Seek("aaa","test","xChildText")
* for comparison: Seek bbb
? "Seeking bbb, expect .F., really get ",Seek("bbb","test","xChildText")

* Causing index update in parent table (hopefully)
SetFldState("lBool",4,"test")
TableUpdate(2,.t.,"test")

* third experiment, seek in test, index should have been updated:
? "Seeking aaa, expect .F., really get ",Seek("aaa","test","xChildText")
* for comparison: Seek bbb
? "Seeking bbb, expect .T., really get ",Seek("bbb","test","xChildText")

? "Is everything as expected? Especially the last two Seeks? Then we had success!"

 

[GriffMG]

It's VFP6, no table or row buffering

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Why? Buffering surely is available in VFP6. You just have to use the older parameterization of Tableupdate(.T.,.T.,"test") and remove autoinc.

Bye, Olaf.

 

[GriffMG]

I didn't use it - because it wasn't needed.

Actually, I have never used it. Ever.

It's a bit like Swindon, I know it's there, but never seen the need...

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

It's a bit like Swindon, I know it's there, but never seen the need...

If it wasn't, changing trains between Bristol and Cheltenham would be a bit perilous.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

So you're missing on the good stuff.

Tableupdate() does in one call do...
...update modified records of the buffer to the DBF
...insert new records of the buffer into the DBF
...delete deleted records of the buffer in the DBF
all in one go.

Well, changing switching a bool field twice is easier implemented than changing your application to buffering, but in the long run it's shortening legacy code using scatter/gather memvar, as you don't need this any more, when using the buffers instead.

Bye, Olaf.

 

[Olaf Doschke]

From what I find in wikipedia, Swindon is a very important city for the UK, home of the UK space agency, as shown here:

http://newsthump.com/2011/03/30/swindon-based-uk-space-agency-launches-mission-to-cirencester/

That looks very promising.



Bye, Olaf

 

[TamarGranor]

Not using buffering is doing a lot more work than you need to. Buffering makes data entry forms so much simpler than other techniques.

Tamar

 

[GriffMG]

All I do is present a form, the user fills in the data required, the system validates it and saves it.

Not sure what could be simpler?

Which bit should I take out; the user bit, the validation of the data, or perhaps not save it?

I reckon most system would be more reliable without the user bit.

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Griff,

You say (i) the user enters data, and (ii) the user can save the data. This implies two separate processes. So how exactly do you handle that? Presumably, the user doesn't enter data straight into the underlying table; if they did, there would be no need for a separate "save" step. So do they enter the data into variables? And do you then gather the variables into the record? If so, that's essentially what buffering does, except that buffering makes the whole thing automatic.

Buffering also makes it easy to deal with multi-user issues. It lets you choose between optimistic and pessimistic locking, and it can tell you if an update might cause a multi-user violation, giving you the chance to do something about it.

It's certainly possible to develop an entire application without using buffering, but buffering really does make life a lot easier.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

Hi Mike,

I don't use buffering, I think it stems from exposure to (i.e. I had to look after) a VFP3(?) system that someone else had written
that did use some kind of buffering - but it was a hard system to follow and the tableupdate()s were always failing... so I only use the
default arrangement, no buffering, design a form, fill it with lovely data entry widgets, get the user to type stuff in or pick it or
whatever, do a quick validation and save if all is good and happy. I haven't had a multi-user violation come up in the last couple of
decades, but a lot of my systems are (were) used either mostly single user or in very small workgroups - so maybe I was just lucky.

It's so second nature to me to work this way - I can't see how buffering would help. It's a bit like my blindness to exactly what OOP
gives you that simple structured code doesn't, I'm becoming a dinosaur!

B-)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.