MapBasic - Updating blank rows in column with the value above

Question

I am writing a MapBasic tool to correct some incorrect values in a set of tables. So far I have managed to pull out the values I need in a new column:

What I need is to update any blank rows in the OBSERVATION column with the value above it (the L*** text).

How would I go about doing this in MapBasic?

Answer 1

Something along these lines:

Dim nRowID As Integer,
    sValue As String

Fetch First From MY_TABLE
Do Until EOT(MY_TABLE)
   If MY_TABLE.OBSERVATION = "" Then
      nRowID = MY_TABLE.ROWID
      Update MY_TABLE
         Set OBSERVATION = sValue
         Where ROWID = nRowID
   Else
      sValue = MY_TABLE.OBSERVATION
   End If

   Fetch Next From MY_TABLE
Loop

Note that the loop remembers the last read value from the column OBSERVATION. This also means that the value used might not be taken from the record before – it could be a record before that in case you have multiple row without any value.

Answer 2

First PACK YOUR TABLE! Then I would add a column to your base table called ID and update that with RowID. Once that's done, select out all records which have a blank value in the Observation column into a query table. Then use a custom function to update this query table with the required values. See example code below (untested):

Include "MapBasic.def"

Declare Sub Main
Declare Function GetObservation(ByVal row as Integer) as String

Sub Main()

    Pack Table MyTable Graphic Data    

    Alter Table MyTable(Add ID Integer)
    Update MyTable set ID = RowID   '// add an ID column to your data

    Select * from MyTable where Observation = "" into ToUpdate NoSelect     '// select rows to update
    Select * from MyTable where Observation <> "" into DataSource NoSelect  '// select source rows for the update

    Update ToUpdate set Observation = GetObservation(ID)    '// update Observation using custom function

    Commit table MyTable

End Sub

Function GetObservation(ByVal row as Integer) as String

Dim iRow as Integer

    iRow = row - 1

    Select Observation from DataSource where ID = iRow into temp NoSelect

    If TableInfo(temp, TAB_INFO_NROWS) > 0 then
        Fetch first from temp
        GetObservation = temp.Observation
    Else
        GetObservation = ""
    End if

End Function