Fill blank fields

Mike72

Forums

Hi Brian, is there a way to add text into an existing blank field, using a script. I've seen some scripts to fill down a column with the contents from the previous field, but i need to add new text to the blank fields.
 
For example, i have a database with various columns and 1000 records. In column X, 500 fields allready has text Q1, the other 500 fields are blank. In these blank fields I want to add Q2.
 
Thanks, Mike

Brian Element Tue, 06/04/2019 - 07:27

Hi Mike,

I would probably do it this way.  Create a virtual character field and use this equation.

@if(@AllTrim(X) = "", "Q2", X)

The equation looks at your column X, removes any spaces and checks if it is empty, if it is it inserts Q2 if not it uses the contents of column X.  You can then decide to keep the old field or delete.

Let me know if this is what you are looking for.

Brian

klmi Tue, 06/04/2019 - 08:49

You can use the code below which doesn't need an additional field. However you have to decide wheather it is good style to change your original data.
 
Dim oDB As Object
Dim oTable As Object
Dim oField As Object
Dim oRS As Object
Dim oRec As Object
Dim col As Integer
 
Set oDB = Client.CurrentDatabase()
Set oTable = oDB.TableDef
 
Set oRS = oDB.RecordSet()
Set oRec = oRS.ActiveRecord()
 
' jump to first recordset
oRS.ToFirst
oRS.Next
 
' only use first column
col = 1
Set oField = oTable.GetFieldAt(col)
 
' allow to write in field
oField.Protected = False
MsgBox oField.Protected
 
' iterate through rows
For row = 1 To oRS.Count
field_value = oRec.ValueAt(col)
'MsgBox field_value
 
If field_value = "Q1" Then
oRec.SetCharValueAt col, "Q1"
oRs.SaveRecord oRec
Else
oRec.SetCharValueAt col, "Q2"
oRs.SaveRecord oRec
End If
 
' jump to next recordset
oRS.Next
Next row
 
oField.Protected = True
 
Set oDB = Nothing
Set oTable = Nothing
Set oField = Nothing
Set oRS = Nothing
Set oRec = Nothing