0

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:

enter image description here

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?

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.