Hi All,

I have a little query.
I have a situation If Field1 has no value so move to next record, get the value of field2 & assigned to Field1 of previous record.
I have tried db.next,db.previous to move between the records but its not working.


Thans Again

One way to do this would be to use program variables to hold the values. Order the selection in descending order. Inside the "selectdo", you can use a variable to hold the current value of field2.
--------------
prev.field2 = 0 or blank (depending on its data type)

select xxx.*
from xxx
where {your conditions}
order by xxx._index1 desc [<-- to be done on the correct field/index]
selectdo
if isspace(xxx.field1) [or xxx.field1 = 0, if field1 is numeric] and not isspace(prev.field2) [or prev.field2 <> 0 if it is numeric] then
xxx.field1 = prev.field2
endif
....
....
prev.field2 = xxx.field2
endselect

also note that db.next and db.previous are used to traverse the recordset, and after you use them, the table fields buffer is overwritten with the new values. You cannot get both previous and current values in the buffer at the same time -- you have to traverse the recordset again to get the values.

NVP just made me think of something. What do you have as far as indexes go? Can you identify the next record with a query? You could then use an alias to get field2 and update field1 on the current record. This is how we do serial numbers on some of the tables we use in the A&D environment.

One way to do this would be to use program variables to hold the values. Order the selection in descending order. Inside the "selectdo", you can use a variable to hold the current value of field2.
--------------
prev.field2 = 0 or blank (depending on its data type)

select xxx.*
from xxx
where {your conditions}
order by xxx._index1 desc [<-- to be done on the correct field/index]
selectdo
if isspace(xxx.field1) [or xxx.field1 = 0, if field1 is numeric] and not isspace(prev.field2) [or prev.field2 <> 0 if it is numeric] then
xxx.field1 = prev.field2
endif
....
....
prev.field2 = xxx.field2
endselect

Hi NVP,
I think you are not getting me. Let me explain the scenario again.
I have a price list table having 4 fields.
Fields details are...
1- Price List
2- Item Number
3- Effective Date
4- Expiry Date

Suppose there are 10 records in this table in which some record are not having "Expiry Date".
My problem is that if a record is not having an "Expiry Date", move to next record & assign the "Effective Date" of this record to "Expiry Date" of previous record.
How to move between the records?



Thanks

You can use this:

select *
from <table>
order by <table>._index1
selectdo
if <expiry date> = 0
then
select <each index field of table>:temp.val, <expiry date>:next.val
from <table>
where <table>._index1 > {<index parts>}
order by <table>._index1
as set with 1 rows
selectdo
| Here you can update your previous record
endselect
endif
endselect