delete empty fields

[Matsta]

How can you tell if a field is empty and if it is empty how
can you program to delete it.

 

[ramani]

IF EMPTY(myField)
=MESSAGEBOX("My field is empty",16)
ELSE
=MESSAGEBOX("My field is NOT empty",16)
ENDIF

But from your question, it is not clear if you are meaning, if my record is empty.

lEmpty = .t.
FOR I = 1 to FCOUNT()
IF ! EMPTY(FIELD(i))
lEmpty = .f.
ENDIF
ENDFOR

IF lEmty
=MESSAGEBOX("My Record is empty",16)
ELSE
=MESSAGEBOX("My Record is NOT empty",16)
ENDIF

Now, to delete a record..

DELETE Next 1

another way is..

DELETE ALL FOR EMPTY(myKeyField)

SO depending on your requirement, you can do the code.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Matsta]

Thanks that worked perfectly!..Sorry If I wasn't clear but I needed to find and delete if a field(not record)is empty.

Thank you again for your quick response!

 

[Stella740pl]

Matsta,
It seems like you want to delete a field (column) from a table's structure. Am I right? If so,there are several ways to do so. And what do you mean by "empty"? All records have NULL value in it? Or just '' for strings, {//} for date, 0 for numerics, etc.? Let's assume for simplicity thatyou mean NULL. Here is some code that first came to mind (not tested, but should work).

SELECT MyTable
COPY STRUCTURE EXTENDED TO MyStru
USE MyStru IN 0
SELECT MyStru
SCAN
SELECT DISTINCT &Field_Name ;
FROM MyTable ;
WHERE !IsNull() ;
INTO ARRAY tmp
IF _TALLY=0
DELETE IN MyStru
ENDIF
ENDSCAN
PACK
CREATE NewTable FROM MyStru
APPEND FROM DBF("MyTable&quot

This way, you get everything in the new table less the "empty" fields. If you want, you can close and rename them, etc. If you didn't mean NULL, then you will need to check also Field_Type, and compare the values in MyTable to 0, and use Empty() function, where applicable.
You might also want to check alternative solutions, probably using AFields() function and CREATE TABLE FROM ARRAY (SQL command), and, possibly, more. I am not sure
if ALTER TABLE (SQL) can help you delete fields, I don't usually use it.

Stella.

 

[Matsta]

I got the exact same structure as before when I used above
program. I have a simple table with 5 columns in it and I want to first see if they are completely populated or not..if they are not populated I need to delete them from my table.

.. The columns are numeric.

This is what I was doing before and it worked. ButI was looking for a program that would automatically delete if columns were not populated without browsing each time for a manual look.

select 'myfield' from 'mytable' where 'myfield' > 0

**by browsing this would tell me if my field was populated
**if it was I'd leave it as is
**if it wasn't then I'd....

alter table 'mytable' drop column 'myfield'


Thanks!

 

[Stella740pl]

Matsta,
If you want to delete columns where all the values are zeros, then you need to change just one small thing in my example:

SELECT MyTable
COPY STRUCTURE EXTENDED TO MyStru
USE MyStru IN 0
SELECT MyStru
SCAN
SELECT DISTINCT &Field_Name ;
FROM MyTable ;
WHERE &Field_Name.#0 ;
INTO ARRAY tmp
IF _TALLY=0
DELETE IN MyStru
ENDIF
ENDSCAN
PACK
CREATE NewTable FROM MyStru
APPEND FROM DBF("MyTable&quot

Or you can use ALTER TABLE, as you did (even shorter code).
I didn't have time to test this, either.

SELECT MyTable
noFields=AFIELDS(theFields)
FOR i=1 TO noFields
theName=theFields(1)
SELECT DISTINCT &theName ;
FROM MyTable ;
WHERE &theName.#0 ;
INTO ARRAY tmp && check, if there are values that <>0
IF _TALLY=0 && nothing found
ALTER TABLE MyTable DROP COLUMN theName
ENDIF
NEXT i

Oh, and I made a mistake in my first example (though irrelevant in your case). It should have been
... WHERE !IsNull(&Field_Name) ...

Stella

 

[Matsta]

Thanks that worked really good, I went with your first example. I appreciate all of your help!

 

[Stella740pl]

Glad to hear that.

And in case you would want to try the second example, correct this line:
theName=theFields(1)
to
theName=theFields(i,1)

Stella.