REPLACE ALL

using vfp 9.1 on xp sp2

under certain conditions I want to replace the contents of a field for all
records in a table

the command

REPLACE ALL fieldA with "B"

results in only one replacement if the order is set to an index with a
filter.

If I issue

SET ORDER TO

immediatelt preceding the replace it reacts as I would expect. I can't find
any documentation of this behavior. Am I missing something?

Thanks,

Dave Tiffany

Dan
Thu Jun 14 15:14:32 CDT 2007

Scope is always evaluated in the current index order's context. It has been
that way since dBase II. (And you're right, I can't find it in the help
file.)

Be wary of filtered indexes in general. They're not always reliable.

Dan

David Tiffany wrote:
> using vfp 9.1 on xp sp2
>
> under certain conditions I want to replace the contents of a field
> for all records in a table
>
> the command
>
> REPLACE ALL fieldA with "B"
>
> results in only one replacement if the order is set to an index with a
> filter.
>
> If I issue
>
> SET ORDER TO
>
> immediatelt preceding the replace it reacts as I would expect. I
> can't find any documentation of this behavior. Am I missing
> something?
> Thanks,
>
> Dave Tiffany

David
Thu Jun 14 15:50:44 CDT 2007

The filter condition still displays a number of records (more than one), but
only one of them has the replaced data in FiledA. Still a mystery to me.

I'll try to find a way around the filter, but it is real handy when doing a
seek to eliminate unwanted records.

Dave


"Dan Freeman" <spam@microsoft.com> wrote in message
news:e57rfCsrHHA.4020@TK2MSFTNGP05.phx.gbl...
> Scope is always evaluated in the current index order's context. It has
> been that way since dBase II. (And you're right, I can't find it in the
> help file.)
>
> Be wary of filtered indexes in general. They're not always reliable.
>
> Dan
>
> David Tiffany wrote:
>> using vfp 9.1 on xp sp2
>>
>> under certain conditions I want to replace the contents of a field
>> for all records in a table
>>
>> the command
>>
>> REPLACE ALL fieldA with "B"
>>
>> results in only one replacement if the order is set to an index with a
>> filter.
>>
>> If I issue
>>
>> SET ORDER TO
>>
>> immediatelt preceding the replace it reacts as I would expect. I
>> can't find any documentation of this behavior. Am I missing
>> something?
>> Thanks,
>>
>> Dave Tiffany
>
>

RGBean
Thu Jun 14 18:19:43 CDT 2007

David,
Does the index expression include fieldA? Replace ALL and even Scans become
quite erratic if this is true. You essentially are changing your position in
the index, and then it just depends on where you went as to whether FP
decides it's 'done' .

Rick

"David Tiffany" <dtiffany@nospam.com> wrote in message
news:O5te1WsrHHA.500@TK2MSFTNGP02.phx.gbl...
> The filter condition still displays a number of records (more than one),
> but only one of them has the replaced data in FiledA. Still a mystery to
> me.
>
> I'll try to find a way around the filter, but it is real handy when doing
> a seek to eliminate unwanted records.
>
> Dave
>
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:e57rfCsrHHA.4020@TK2MSFTNGP05.phx.gbl...
>> Scope is always evaluated in the current index order's context. It has
>> been that way since dBase II. (And you're right, I can't find it in the
>> help file.)
>>
>> Be wary of filtered indexes in general. They're not always reliable.
>>
>> Dan
>>
>> David Tiffany wrote:
>>> using vfp 9.1 on xp sp2
>>>
>>> under certain conditions I want to replace the contents of a field
>>> for all records in a table
>>>
>>> the command
>>>
>>> REPLACE ALL fieldA with "B"
>>>
>>> results in only one replacement if the order is set to an index with a
>>> filter.
>>>
>>> If I issue
>>>
>>> SET ORDER TO
>>>
>>> immediatelt preceding the replace it reacts as I would expect. I
>>> can't find any documentation of this behavior. Am I missing
>>> something?
>>> Thanks,
>>>
>>> Dave Tiffany
>>
>>
>
>

Cy
Thu Jun 14 19:36:12 CDT 2007

RGBean wrote:
> David,
> Does the index expression include fieldA? Replace ALL and even Scans
> become quite erratic if this is true. You essentially are changing your
> position in the index, and then it just depends on where you went as to
> whether FP decides it's 'done' .
>
> Rick
>
> "David Tiffany" <dtiffany@nospam.com> wrote in message
> news:O5te1WsrHHA.500@TK2MSFTNGP02.phx.gbl...
>> The filter condition still displays a number of records (more than
>> one), but only one of them has the replaced data in FiledA. Still a
>> mystery to me.
>>
>> I'll try to find a way around the filter, but it is real handy when
>> doing a seek to eliminate unwanted records.
>>
>> Dave
>>
>>
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:e57rfCsrHHA.4020@TK2MSFTNGP05.phx.gbl...
>>> Scope is always evaluated in the current index order's context. It
>>> has been that way since dBase II. (And you're right, I can't find it
>>> in the help file.)
>>>
>>> Be wary of filtered indexes in general. They're not always reliable.
>>>
>>> Dan
>>>
>>> David Tiffany wrote:
>>>> using vfp 9.1 on xp sp2
>>>>
>>>> under certain conditions I want to replace the contents of a field
>>>> for all records in a table
>>>>
>>>> the command
>>>>
>>>> REPLACE ALL fieldA with "B"
>>>>
>>>> results in only one replacement if the order is set to an index with a
>>>> filter.
>>>>
>>>> If I issue
>>>>
>>>> SET ORDER TO
>>>>
>>>> immediatelt preceding the replace it reacts as I would expect. I
>>>> can't find any documentation of this behavior. Am I missing
>>>> something?
>>>> Thanks,
>>>>
>>>> Dave Tiffany
>>>
>>>
>>
>>
>
I recall at one point that it was mentioned in the documentation (may
have been 2.0) to never replace values in a field that was ordered on
via index as the results were unpredictable.

--
Cy Welch
Senior Programmer
MetSYS Inc
http://www.metsysinc.com

Bernhard
Fri Jun 15 05:36:35 CDT 2007

Hi David,

> The filter condition still displays a number of records (more than one), but
> only one of them has the replaced data in FiledA. Still a mystery to me.
>
> I'll try to find a way around the filter, but it is real handy when doing a
> seek to eliminate unwanted records.
One way around: use UPDATE SQL command. It does not use the active filter or
active order and also does not change the actual record

UPDATE theTable SET theField = "value"
changes theField in all rows.

An other way around:
prior to the REPLACE command, save the actual settings. After the REPLACE reset
all settings:

lcOrder = order()
lnRecno = recno()
lcFilter = filter()
SET ORDER TO 0
SET FILTER TO
REPLACE ...
GOTO (lnRecno)
SET ORDER TO (lcOrder)
SET FILTER TO &lcFilter

You need more lines of code, if you use relations etc.

Yet another way around:
open the same table in a second workspace without order or filter and do the
REPLACE there:

USE theTable AGAIN in 0 ALIAS secondAlias
* this USE is needed only once
REPLACE ALL theField WITH theValue IN secondAlias

Regards
Bernhard Sander

Villi
Fri Jun 15 08:33:14 CDT 2007

RGBean wrote:

> Does the index expression include fieldA? Replace ALL and even Scans
> become quite erratic if this is true. You essentially are changing
> your position in the index, and then it just depends on where you
> went as to whether FP decides it's 'done' .

Very true, and also without filters or filtered indexes.
--
News Microsoft

David
Fri Jun 15 12:42:57 CDT 2007

That was it. Replacing the data in fielda causes that record to be the last
in the table, so replace must think it's done.

The answer seems to be to set order to, issue the replace and then reset the
order.

Thanks to everyone.

Dave


"RGBean" <rgbean@NOSPAMmelange-inc.com> wrote in message
news:389BD8A8-DD85-461F-A36B-23EE8B9E84EF@microsoft.com...
> David,
> Does the index expression include fieldA? Replace ALL and even Scans
> become quite erratic if this is true. You essentially are changing your
> position in the index, and then it just depends on where you went as to
> whether FP decides it's 'done' .
>
> Rick
>
> "David Tiffany" <dtiffany@nospam.com> wrote in message
> news:O5te1WsrHHA.500@TK2MSFTNGP02.phx.gbl...
>> The filter condition still displays a number of records (more than one),
>> but only one of them has the replaced data in FiledA. Still a mystery to
>> me.
>>
>> I'll try to find a way around the filter, but it is real handy when doing
>> a seek to eliminate unwanted records.
>>
>> Dave
>>
>>
>> "Dan Freeman" <spam@microsoft.com> wrote in message
>> news:e57rfCsrHHA.4020@TK2MSFTNGP05.phx.gbl...
>>> Scope is always evaluated in the current index order's context. It has
>>> been that way since dBase II. (And you're right, I can't find it in the
>>> help file.)
>>>
>>> Be wary of filtered indexes in general. They're not always reliable.
>>>
>>> Dan
>>>
>>> David Tiffany wrote:
>>>> using vfp 9.1 on xp sp2
>>>>
>>>> under certain conditions I want to replace the contents of a field
>>>> for all records in a table
>>>>
>>>> the command
>>>>
>>>> REPLACE ALL fieldA with "B"
>>>>
>>>> results in only one replacement if the order is set to an index with a
>>>> filter.
>>>>
>>>> If I issue
>>>>
>>>> SET ORDER TO
>>>>
>>>> immediatelt preceding the replace it reacts as I would expect. I
>>>> can't find any documentation of this behavior. Am I missing
>>>> something?
>>>> Thanks,
>>>>
>>>> Dave Tiffany
>>>
>>>
>>
>>
>

Gene
Fri Jun 15 13:57:19 CDT 2007

"David Tiffany" <dtiffany@nospam.com> wrote:

>The filter condition still displays a number of records (more than one), but
>only one of them has the replaced data in FiledA. Still a mystery to me.
>
>I'll try to find a way around the filter, but it is real handy when doing a
>seek to eliminate unwanted records.

You could use set filter:
use sometable order tag sometag
set filter to somecondition
seek somekey

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.