Reorder Fields
I have a table that looks like this:
For any record that has one of the descrip fields empty, I need to fill it with the value of the descrip field in the next column to the right and move the others over, if any.
If only the first field, decriptn is populated, the record gets deleted.
The result will look like this:
What's the best way to do this?
For any record that has one of the descrip fields empty, I need to fill it with the value of the descrip field in the next column to the right and move the others over, if any.
If only the first field, decriptn is populated, the record gets deleted.
The result will look like this:
What's the best way to do this?
ASKER
Thanks Pcelca.
I am out of town with a dead lap top.
I'll get back to this in a couple of days.
I am out of town with a dead lap top.
I'll get back to this in a couple of days.
ASKER
Pcelba,
Below is the actual code I am working with. Instead of blank fields, I am evaluating fields that begin with "--". When I run the code, there is not change to the record.
Also, on the delete I am getting "cannot update the cursor." (VFP5)
Here is the 'before' record and what I need the updated record to look like:
Below is the actual code I am working with. Instead of blank fields, I am evaluating fields that begin with "--". When I run the code, there is not change to the record.
Also, on the delete I am getting "cannot update the cursor." (VFP5)
Here is the 'before' record and what I need the updated record to look like:
lparameter lcThisPath;
lnWeekNum
LOCAL lcPullFile;
lcWeekPullFile, ;
lcWeekPullFileNam ;
lcDir;
lnWeekNum;
lcWeekNum;
lcScrubFile;
lcWeekPullFileNm ;
lnI, lnJ, lcField, lcField2
if empty(lcThisPath)
lcThisPath = "c:\vetdata\_de\vetdata\"
endif
if empty(lnWeekNum)
lnWeekNum = 43
endif
lcWeekNum = "9" + alltrim(right(str(100 + lnWeekNum),2))
*set DELETED ON
lcWeekPullFile = "wk" + lcWeekNum + "_pull.dbf"
lcWeekPullFileNm = "wk" + lcWeekNum + "_pull"
use in select('ckRemindEdit')
use (lcThisPath) + "ckRemindEdit.dbf" in 0
select ckRemindEdit
set deleted off
scan
if deleted('ckRemindEdit')
set deleted on
lcDir = ALLTRIM(ckRemindEdit.DefaultDir)
lcPullFile = lcThisPath + (lcDir) + "\pull\" + (lcWeekPullFile)
select descriptn, descrip2, descrip3, descrip4, descrip5, descrip6, descrip7, descrip8 ;
from (lcPullFile) where ;
descriptn = ckRemindEdit.Descrip or;
descrip2 = ckRemindEdit.Descrip or ;
descrip3 = ckRemindEdit.Descrip or ;
descrip4 = ckRemindEdit.Descrip or ;
descrip5 = ckRemindEdit.Descrip or ;
descrip6 = ckRemindEdit.Descrip or ;
descrip7 = ckRemindEdit.Descrip or ;
descrip8 = ckRemindEdit.Descrip ;
into cursor curPull &&readwrite
select curPull
*brow
scan
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
*IF left(LCfIELD,2) = "--" AND !EMPTY(EVALUATE(lcField2))
if lcfield = "--" AND !EMPTY(EVALUATE(lcField2))
wait window (lcfield)
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH ""
EXIT
ENDIF
NEXT
NEXT
IF EMPTY(descrip2)
DELETE && cannot update cursor
ENDIF
endscan
endif
set deleted off
endscan
ASKER
Any descrip field that begins with "--" needs to be removed and adjoining fields shifted over. Your logic should work I think ..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To simplify:
I just now ran your code as written that you posted.. and realize it works and is what I asked for. I need to make a change from evaluating the blank field to evaluating a field prefixed with "--".
Refer to updated CREATE CURSOR:
Thanks pcelba!
XOXO :)
I just now ran your code as written that you posted.. and realize it works and is what I asked for. I need to make a change from evaluating the blank field to evaluating a field prefixed with "--".
Refer to updated CREATE CURSOR:
CREATE CURSOR cYourTable (descriptn char(20), descrip2 char(20), descrip3 char(20), descrip4 char(20), descrip5 char(20), descrip6 char(20), descrip7 char(20), descrip8 char(20))
INSERT INTO cYourTable VALUES ('The', '--cow', 'dish', 'ran', 'away', 'with', 'the', 'spoon')
INSERT INTO cYourTable VALUES ('Mary', 'had', 'a', 'little', '--dog', 'lamb', '', '')
INSERT INTO cYourTable VALUES ('Humpty', 'dumpty', 'sat', 'on', '--in the', '--corner', 'a', 'wall')
INSERT INTO cYourTable VALUES ('--Jack', '', '', '', '', '', '', '')
brow
SET DELETED ON
LOCAL lnI, lnJ, lcField, lcField2
SELECT cYourTable
SCAN
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
*-- I think I need to change the code here to evaluate lcField = "--" and lcField2 not empty
*--.. still working on the syntax to replace this with new condition:
IF EMPTY(EVALUATE(lcField)) AND !EMPTY(EVALUATE(lcField2))
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH ""
EXIT
ENDIF
NEXT
NEXT
IF EMPTY(descrip2)
DELETE
ENDIF
ENDSCAN
GO TOP
BROWSEThanks pcelba!
XOXO :)
ASKER
I didn't see your last post until after I submitted my previous one. I had the question open when you posted...
I will take a close look at it later today.
Thanks!
I will take a close look at it later today.
Thanks!
My code contains some bugs and your last code is very close to the success. This code should work with both -- and spaces in the place of "unwanted" fields:
CREATE CURSOR cYourTable (descriptn char(20), descrip2 char(20), descrip3 char(20), descrip4 char(20), descrip5 char(20), descrip6 char(20), descrip7 char(20), descrip8 char(20))
INSERT INTO cYourTable VALUES ('The', '--cow', 'dish', 'ran', 'away', 'with', 'the', 'spoon')
INSERT INTO cYourTable VALUES ('Mary', 'had', 'a', 'little', '--dog', 'lamb', '', '')
INSERT INTO cYourTable VALUES ('Humpty', 'dumpty', 'sat', 'on', '--in the', '--corner', 'a', 'wall')
INSERT INTO cYourTable VALUES ('--Jack', '', '', '', '', '', '', '')
brow
SET DELETED ON
LOCAL lnI, lnJ, lcField, lcField2
SELECT cYourTable
SCAN
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
*-- I think I need to change the code here to evaluate lcField = "--" and lcField2 not empty
*--.. still working on the syntax to replace this with new condition:
*-- Updates are below
IF (EMPTY(EVALUATE(lcField)) OR LEFT(EVALUATE(lcField), 2) = '--') AND !EMPTY(EVALUATE(lcField2)) AND LEFT(EVALUATE(lcField2), 2) != '--'
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH ""
EXIT
ENDIF
NEXT
NEXT
IF EMPTY(descrip2) OR LEFT(descrip2, 2) = '--')
DELETE
ENDIF
ENDSCAN
GO TOP
BROWSEASKER
Thanks pcelba
I am still getting "Cannot update the cursor."
Also, I get "Alias not found" when reopening the cursor with your code. The change I made seems to work.
I am still getting "Cannot update the cursor."
Also, I get "Alias not found" when reopening the cursor with your code. The change I made seems to work.
lparameter lcThisPath;
lnWeekNum
LOCAL lcPullFile;
lcWeekPullFile, ;
lcWeekPullFileNam ;
lcDir;
lnWeekNum;
lcWeekNum;
lcScrubFile;
lcWeekPullFileNm ;
lnI, lnJ, lcField, lcField2
if empty(lcThisPath)
lcThisPath = "c:\vetdata\_de\vetdata\"
endif
if empty(lnWeekNum)
lnWeekNum = 43
endif
lcWeekNum = "9" + alltrim(right(str(100 + lnWeekNum),2))
set deleted off
lcWeekPullFile = "wk" + lcWeekNum + "_pull.dbf"
lcWeekPullFileNm = "wk" + lcWeekNum + "_pull"
use in select('ckRemindEdit')
use (lcThisPath) + "ckRemindEdit.dbf" in 0
select ckRemindEdit
set deleted off
scan
if deleted('ckRemindEdit')
lcDir = ALLTRIM(ckRemindEdit.DefaultDir)
lcPullFile = lcThisPath + (lcDir) + "\pull\" + (lcWeekPullFile)
**wait window (lcPullFile) + " -- " + ckRemindEdit.Descrip
select descriptn, descrip2, descrip3, descrip4, descrip5, descrip6, descrip7, descrip8 ;
from (lcPullFile) where ;
descriptn = ckRemindEdit.Descrip or;
descrip2 = ckRemindEdit.Descrip or ;
descrip3 = ckRemindEdit.Descrip or ;
descrip4 = ckRemindEdit.Descrip or ;
descrip5 = ckRemindEdit.Descrip or ;
descrip6 = ckRemindEdit.Descrip or ;
descrip7 = ckRemindEdit.Descrip or ;
descrip8 = ckRemindEdit.Descrip ;
into cursor curPull
*USE (DBF('curPullRO')) AGAIN IN 0 ALIAS curPull && your code: Syntax error
USE (DBF('curPull')) IN 0 AGAIN ALIAS curPullRO && updated USE
SELECT curPull
SET DELETED ON
SCAN
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
IF (EMPTY(EVALUATE(lcField)) OR LEFT(EVALUATE(lcField), 2) = '--') AND !EMPTY(EVALUATE(lcField2)) AND LEFT(EVALUATE(lcField2), 2) != '--'
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH "" &&Cannot update the cursor
EXIT
ENDIF
NEXT
NEXT
IF (EMPTY(descrip2) OR LEFT(descrip2, 2) = '--')
DELETE
ENDIF
ENDSCAN
endif
set deleted off
endscanASKER
I got the code the run using BROWSE instead of a cursor.
REPLACE is working as it should. DELETE deletes a bunch of records that it shouldn't. I haven't yet pursued it further than that.
REPLACE is working as it should. DELETE deletes a bunch of records that it shouldn't. I haven't yet pursued it further than that.
select ckRemindEdit
set deleted on
scan all
if deleted('ckRemindEdit')
lcDir = ALLTRIM(ckRemindEdit.DefaultDir)
lcPullFile = lcThisPath + (lcDir) + "\pull\" + (lcWeekPullFile)
wait window (lcPullFile) + " -- " + ckRemindEdit.Descrip
use in select(lcWeekPullFileNm)
use (lcPullFile) in 0 excl
select (lcWeekPullFileNm)
brow fields pracabbrev, cpmsid, descriptn, descrip2, descrip3, descrip4, descrip5, descrip6, descrip7, descrip8 ;
for alltrim(descriptn) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip2) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip3) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip4) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip5) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip6) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip7) = alltrim(ckRemindEdit.Descrip) ;
or alltrim(descrip8) = alltrim(ckRemindEdit.Descrip) nowait
SCAN
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
IF (EMPTY(EVALUATE(lcField)) OR LEFT(EVALUATE(lcField), 2) = '--') AND !EMPTY(EVALUATE(lcField2)) AND LEFT(EVALUATE(lcField2), 2) != '--'
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH "" &&Cannot update the cursor
EXIT
ENDIF
NEXT
NEXT
IF (EMPTY(descrip2) OR LEFT(descrip2, 2) = '--')
DELETE
ENDIF
ENDSCAN
use
endif
set deleted off
endscan
Notes to the first code: you've switched the RO and RW cursor names so you were still trying to update the RO cursor.
The DELETE should delete just the row which has empty descrip2 or descrip2 = '--' and this deletion happens after the end of loop when it is clear no defined descripX field exists.
The code expects no deleted row at the beginning. You may ensure that by issuing RECALL ALL command.
The DELETE should delete just the row which has empty descrip2 or descrip2 = '--' and this deletion happens after the end of loop when it is clear no defined descripX field exists.
The code expects no deleted row at the beginning. You may ensure that by issuing RECALL ALL command.
ASKER
I had to change the criteria for the delete. I only want to delete the record when descriptn is prefixed by '--' AND descrip2 is empty. I didn't give you that ..
Updateable cursor is happy :).. but the delete does not work.
This code displays the wait window, but does not delete the record:
IF left(descriptn,2) = '--' and EMPTY(descrip2)
wait window "delete " + prrecno &&displays wait window
delete && record is not deleted ?
endif
Also, the pack triggers "the file is open in another work area"
Updateable cursor is happy :).. but the delete does not work.
This code displays the wait window, but does not delete the record:
IF left(descriptn,2) = '--' and EMPTY(descrip2)
wait window "delete " + prrecno &&displays wait window
delete && record is not deleted ?
endif
Also, the pack triggers "the file is open in another work area"
lparameter lcThisPath;
lnWeekNum
LOCAL lcPullFile;
lcWeekPullFile, ;
lcWeekPullFileNam ;
lcDir;
lnWeekNum;
lcWeekNum;
lcScrubFile;
lcWeekPullFileNm ;
lnI, lnJ, lcField, lcField2
close datab all
if empty(lcThisPath)
lcThisPath = "c:\vetdata\_de\vetdata\"
endif
if empty(lnWeekNum)
lnWeekNum = 43
endif
lcWeekNum = "9" + alltrim(right(str(100 + lnWeekNum),2))
set deleted on
lcWeekPullFile = "wk" + lcWeekNum + "_pull.dbf"
lcWeekPullFileNm = "wk" + lcWeekNum + "_pull"
use in select('ckRemindEdit')
use (lcThisPath) + "ckRemindEdit.dbf" in 0
select ckRemindEdit
set deleted off
scan all
if deleted('ckRemindEdit')
lcDir = ALLTRIM(ckRemindEdit.DefaultDir)
lcPullFile = lcThisPath + (lcDir) + "\pull\" + (lcWeekPullFile)
use in select(lcWeekPullFileNm)
use (lcPullFile) in 0 excl
select (lcWeekPullFileNm)
select prrecno, pracabbrev, cpmsid, descriptn, descrip2, descrip3, descrip4, descrip5, descrip6, descrip7, descrip8 ;
from (lcPullFile) where ;
descriptn = ckRemindEdit.Descrip ;
or descrip2 = ckRemindEdit.Descrip ;
or descrip3 = ckRemindEdit.Descrip ;
or descrip4 = ckRemindEdit.Descrip ;
or descrip5 = ckRemindEdit.Descrip ;
or descrip6 = ckRemindEdit.Descrip ;
or descrip7 = ckRemindEdit.Descrip ;
or descrip8 = ckRemindEdit.Descrip ;
into cursor curPullRO
USE (DBF('curPullRO')) AGAIN IN 0 ALIAS curPull excl
SELECT curPull
SCAN
FOR lnI = 2 TO 7
lcField = 'descrip' + ALLTRIM(STR(lnI))
FOR lnJ = lnI+1 TO 8
lcField2 = 'descrip' + ALLTRIM(STR(lnJ))
IF (EMPTY(EVALUATE(lcField)) OR LEFT(EVALUATE(lcField), 2) = '--') AND !EMPTY(EVALUATE(lcField2)) AND LEFT(EVALUATE(lcField2), 2) != '--'
REPLACE (lcField) WITH EVALUATE(lcField2), (lcField2) WITH ""
EXIT
ENDIF
NEXT
NEXT
IF left(descriptn,2) = '--' and EMPTY(descrip2)
wait window "delete " + prrecno &&displays wait window
delete && record is not deleted ?
endif
ENDSCAN
pack && file is open in another work area
use
endif
endscan
The DELETE command should just mark the record for deletion. You may see it as a black mark in the BROWSE on the left side of the row. If you would like to hide such records then issue SET DELETED ON before displaying the data in BROWSE.
PACK on the cursor does not work. If you would like to physically remove records marked for deletion then you have to use COPY TO command or SELECT query which creates another table or cursor, e.g.:
SELECT * FROM CurPull WHERE NOT DELETED() INTO CURSOR CurPull2
PACK on the cursor does not work. If you would like to physically remove records marked for deletion then you have to use COPY TO command or SELECT query which creates another table or cursor, e.g.:
SELECT * FROM CurPull WHERE NOT DELETED() INTO CURSOR CurPull2
ASKER
I know how to look for records marked for deletion and that a pack is required to actually delete them...
I was looking in the source table ("PullFile") for the record marked for deletion instead of the cursor because I didn't know any better... I've used cursors little to none.
How do I get the updated data including the record marked for the deletion to write back to the source? I was assuming it was automatic.
I was looking in the source table ("PullFile") for the record marked for deletion instead of the cursor because I didn't know any better... I've used cursors little to none.
How do I get the updated data including the record marked for the deletion to write back to the source? I was assuming it was automatic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Easy Peasy! Why didn't you just say so in the first place?! ;)
Thank you pcelba :)
Thank you pcelba :)
I did not expect you would like to write the changes back to the original file :-)
You are welcome
You are welcome
Open in new window