Very BASIC VFP Command Question (Filter / Delete)

[kev100]

Folks,

I've got a really big FoxPro table.

I'm only somewhat familiar with FoxPro commands...just enough to get by those times when I need to do some minor editing.

I can open the table up and browse the data using VFP 7.0

When opening the table...a command line window pops up.

I'm needing to do the following via command line (just assuming it would be faster).

-Filter for all the records in the table that have a certain value in a specific field, then delete all those records.

-Then...I understand the table has to be "packed" (?) in order to make the deletions permanate.

Example:
Via command line...
Delete all the records in the table that have "Blue" as the value in the COLOR field.

Any help greatly appreciated.

Thanks

 

[cricket]

First rule: make a backup or better still 2 backups

SELECT the table
SET DELETED ON && will not show deleted records which have not yet been PACKed

The command lines are as follows depending on your precise need:
1. DELETE ALL FOR 'Blue'$Color && deletes every record where 'Blue' occcurs, exactly matching case, but it also deletes records with color 'Blue/Green'
2. DELETE ALL FOR 'BLUE'$UPPER(Color) && deletes every record where 'Blue' occcurs, case independent, but it also deletes records with color 'BLUe/Green'
3. DELETE ALL FOR ALLTRIM(color)='Blue' && deletes every record where color exactly equals 'Blue'
3. DELETE ALL FOR UPPER(ALLTRIM(color))='BLUE' && deletes every record where color exactly equals case independent 'Blue',

When you have issued your command, then BROWSE your table to see if you are happy with the result
If not, issue RECALL ALL to recover all deleted records
When finally happy, PACK the table
Reset SET DELETED to your normal setting

 

[kev100]

Thanks...that's working well and saving lots of time.

One one command, I may have the syntax wrong.

DELETE ALL FOR ALLTRIM(color)='Blue 1'
...will only mark 'Blue 1' records, which works great.

However...

DELETE ALL FOR ALLTRIM(color)='Blue'

....will mark records with 'Blue 1' or Blue 4' etc. as well just 'Blue'

Do I need to use any other commands when only wanting to delete 'Blue' ?

Thanks very much.

 

[kev100]

Thanks much...

That did the trick.

Again...this is going to save a ton of time compared to the way I would have otherwise had to do the same.

Thanks all.

 

[kev100]

Howdy again...

Just wondering about other possible time-saving commands.

Is there any version of the Delete command that essentially means "Delete Everything But [Field and Value(s)]" ?

And, if so...one that allows multiple values?

Something like.....

"Delete all records except those that have the value of Green, Orange, Pink, & Yellow in the COLOR field"

?

Reason being...I've got about about 16 pages (single spaced) worth of values to delete.

But only about 8 pages of "Keeper" values.


Hope this makes sense...

Thanks very much.

 

[Auguy]

Because you have more values to delete than to save you could work backwards. The key here is to make sure there are no deleted records in the table when you start.

First make multiple backups as described above.

SELECT the table

SET DELETED OFF

This command will display any currently deleted records
BROWSE FOR DELETED()

If there are any deleted records and you really don't want them, then issue a PACK command. If these are records you want to keep, then you will have to deal with those records before continuing.

Now that there are no deleted records in table, issue the following command that will delete ALL records.

We can then recall (UNDELETE) only the records we want to keep as follows.

RECALL FOR ALLTRIM(Color) == "Blue" or ALLTRIM(Color) == "Green" or ALLTRIM(Color) == "Red"

This command will "UNDELETE" the specified records.

See Cricket's post above for help in determining which form of ALLTRIM, etc. you should use.

You can combine many "OR's" on one command line

When you are done, browse the file to make sure all records you want to save are not deleted before issuing the PACK command.

Auguy

 

[JRB-Bldr]

Or you could use the either the INLIST() command or the Included ($) operator combined with the NOT operator (!).

* --- Delete ALL for UPPER(ALLTRIM(Color)) NOT In Following List ---
DELETE ALL FOR !INLIST(UPPER(ALLTRIM(COLOR)),"GREEN","BLUE","RED")

* --- Delete ALL for UPPER(ALLTRIM(Color)) NOT Included In Following String ---
DELECT ALL FOR !(UPPER(ALLTRIM(COLOR)) $ "GREEN,BLUE,RED")

Good Luck,

 

[kev100]

Thanks All....those are both very workable routes...

jrbbldr...

Is there an opposite version of those commands?

e.g. Delete all the items that are IN the list?

...(just guessing)...but would it be a matter of simply leaving off the "!" ?

...as in...

DELETE ALL FOR INLIST(UPPER(ALLTRIM(COLOR)),"GREEN","BLUE","RED")

DELECT ALL FOR (UPPER(ALLTRIM(COLOR)) $ "GREEN,BLUE,RED")

?

-Thanks very much

 

[JRB-Bldr]

Yes, not including the NOT operator will cause the command to work in the reverse.

Don't overlook the typo error which I included at no additional charge in my previous post.
DELECT should have been DELETE

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[kev100]

Thanks...

Does this treat the strings as exact (e.g. above posts)..

i.e. will...
DELETE ALL FOR (UPPER(ALLTRIM(COLOR)) $ "GREEN,BLUE,RED")

...only delete records with *EXACTLY* GREEN, BLUE, or RED in the Color field

...or

...will it include GREEN01, BLUE_67, REDa, etc. ?

Thanks much

 

[JRB-Bldr]

As Mike indicates above the command as written would possibly DELETE more than you wanted, but possibly not as much as you might want.

Mike's examples of "BLU" & "REEN" would each be included in the string "BLUE,GREEN,RED" so they too would be deleted.

Mike's modification suggestion above would eliminate these partial-matches from being included.

But your own examples of "GREEN01", "BLUE_67", "REDa" would NOT each be included in the string "BLUE,GREEN,RED" so they would NOT be deleted.

You could try:

DELETE FOR "BLUE" $ ALLTRIM(UPPER(Color)) ;
   OR "RED" $ ALLTRIM(UPPER(Color)) ;
   OR "GREEN" $ ALLTRIM(UPPER(Color))

Since "BLUE" is included in "BLUE_67"
and "GREEN" is included in "GREEN01"
and "RED" is included in "REDa"
you can see how this version of the command would delete "GREEN01", "BLUE_67", & "REDa"

I think are begining to get the general idea of some of the ways that the commands can be varied to get to specifically what you are after.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[bervinitto]

* By Marvin B. Sunga
* =DELMVDT('BAC','BRANCH')
* function: delete records exactly as the first argument from the field of 2nd argument
* Usage: set the procedure to saved file and then execute on command window
* =DELMVDT('Blue','color') && Y = FIELD, X = string

PROCEDURE DELMVDT
PARA X,Y && Y = FIELD, X = string

IF EMPTY(ALIAS())
WAIT WINDOW 'Pls use a table first...'
RETURN
ENDIF
? X
? Y
? upper(PADR(ALLT(&Y),FSIZE(Y),'X'))
? upper(PADR(ALLT(X),FSIZE(Y),'X'))
DELE FOR upper(PADR(ALLT(&Y),FSIZE(Y),'X'));
==upper(PADR(ALLT(X),FSIZE(Y),'X'))

WAIT WIND 'Execution Complete...'
RETU


retu


***********************
* or as a program
* do <program name> with 'bLue','COLOR'


PARA X,Y && Y = FIELD, X = string

IF EMPTY(ALIAS())
WAIT WINDOW 'Pls use a table first...'
RETURN
ENDIF
? X
? Y
DELE FOR upper(PADR(ALLT(&Y),FSIZE(Y),'X'));
==upper(PADR(ALLT(X),FSIZE(Y),'X'))

WAIT WIND 'Deletion Complete'
RETU


* ^_^ God Bless u all!!!


* Marvin B. Sunga
* Home Development Mutual Fund - Employee Provident Plan Inc.
* marvinitto@yahoo.com
* bervinitto@hotmail.com

 

[NasibKalsi]

Kev:

You have all the necessary commands to compute your results.

Following may be helpful -

modi stru to add one a numeric field call it COLORTOT

then
* Field Names are UPPER CASE
* t_, tt_ are temp files
*
select A
use main
copy to t_main
use t_main
modi stru

replace all COLORTOT with 1
index on COLOR to t_color
total on COLOR to tt_color
use tt_color
brow field COLORTOT, COLOR
this will show you quickly what you have and may help you run the commands as necessary.

Also then you can pull all records as mentioned earlier to separate files for each separate COLOR description as you see it in the tt_color table. If record count of tt_color is manageable you can add another Field COLORDEL to tt_color. Replace this field for all the color you want to get rid off with 'DEL'. And

sele a
use t_main
sele b
use tt_color
index on COLOR to t2_color

sele a
set relation to COLOR into b
replace all COLOR with b.COLORDEL for b.COLORDEL = 'DEL'
set relation to
delete for trim(COLOR) = 'DEL'
pack
*
* before this posting I always used 'all for'. Thanks Mike.
*

try it

Nasib Kalsi

 

[joshuaguilty]

I am doing repeating steps like
user c:\folder1\table1
replace field1 with "AAA" for field1="aaa" and field2="Y"
replace field1 with "BBB" for field1="bbb" and field2="Y"
replace field1 with "CCC" for field1="ccc" and field2="Y"
user c:\folder1\table2
replace field1 with "AAA" for field1="aaa" and field2="Y"
replace field1 with "BBB" for field1="bbb" and field2="Y"
replace field1 with "CCC" for field1="ccc" and field2="Y"
...
Could I put them in a script file to do the automatation?
Thanks.

 

[NasibKalsi]

* Extension to Mike's Comment.

* When you have . prompt
* type

modi command myfirst.prg

* then type all your commands here
* to save hit ctrl W (keep the ctrl key down and hit W)
* At this point you have save the file under myfirst.prg
* then simply type

do myfirst

* For testing purposes, just type some simple commands
* in the myfirst.prg
* such as

modi command myfirst.prg

clear
@ 10,10 say 'Testing Script'
@ 11,10 say '--------------'

*
* save this by ctrl W and then run it
*
do myfirst

good luck.


Nasib Kalsi