Fill Down Utility
This script will allow you to fill empty cells within a database from information contained in the previous cell.
Sometimes you might import a file, such as an excel file in which information is missing from a cell. The reason could be that the Excel spreadsheet was set-up as a report instead of an actual spreadsheet. Within the Report Reader we have the option to grab information from previous rows in cases that the information is missing, unfortunately this is not the case with Excel or other import types. Currently if you have this problem you usually have to bring the spreadsheet into IDEA, export it as a text report then use the Report Reader to populate the missing data, this script will save you from having to do this.
Here is an example of an Excel report that has been brought into IDEA with missing information.
As can be seen there is missing information in the ACCNO, ACCNAME and DATE fields. Once you have the file imported you can run the script.
You first have to select the file, the script automatically selects the file if you have one already open. You then must select the fields you want populated.
In this case we want to use the script on ACCNAME, ACCNO and Date. You select OK and Ok to run the script. This is the final product.
As can be seen the blanks of the three fields have been populated from the information in the previous field.
I want to thank Steve Luciani for suggesting this script.
modification
Hi Brian, really great script. I would like to modify it and get the user to point to a line in the Database (like in Picture 1 line 4 and the write a text from an Inputbox into a certain field in that line). I cannot find any information how to grab the pointer to the record set the user has given in the User interface. Most examples have the following sequence
rs.ToFirst
Set rec = rs.ActiveRecord
rs.Next
which ist the first line (recordnumer = zero)
Is there any chance to get hold of the recordnumber the user has highlighted in the GUI (I know ist depends on the Index!)
Hi macroKV,
Hi macroKV,
Unfortunately there is no way (that I know of) to read the record number that a user has highlighted in IDEA. The best you could do is use an input box or a dialog to have the user enter the record number using the res.GetAt() function.
Sub Main
' Open the database.
Set db = Client.OpenDatabase("Sample-Detailed Sales.IMD")
' Obtain the RecordSet from the database.
Set rs = db.RecordSet
' Obtain the tenth record from the RecordSet.
rs.GetAt(10)
Set rec = rs.ActiveRecord
' Show that the record holds data.
MsgBox "INV_NO = " & rec.GetCharValue("INV_NO")
' Clear the memory.
Set db = Nothing
Set rs = Nothing
Set rec = Nothing
End Sub
Hi Angelique,
Hi Angelique,
Here you go, I extracted the function that actually does the work and changed the call a bit. So now when you call the function just let it know what is the file name and the field name you want this performed on and hopefully it will work for you.
Just copy it over to your script.
Brian
Option Explicit
Sub Main
Call updateFields("One field fill down test-Sheet1.IMD", "FIELD1")
End Sub
Function updateFields(sFilename As String, sFieldname As String)
On Error GoTo ErrorHandler
Dim aCurFieldContents() As String 'array to hold the information from the current field
Dim aPrevFieldContents() As String 'array to hold the information from a previous field, this information will be copied over if the current field is blank.
Dim db As database
Dim table As table
Dim RS As recordSet
Dim rec As record
Dim field As field
Dim i As Long
Dim j As Integer
Dim bContentsChanged As Boolean 'flag to indicate that at least one field was changed, therefore save the record.
ReDim aCurFieldContents(0)
ReDim aPrevFieldContents(0)
Set db = client.opendatabase(sFilename)
Set table = db.TableDef
Set RS = db.RecordSet
RS.ToFirst
table.Protect = False 'unlock the database so changes can be made
For i = 1 To RS.Count 'loop through all the records in the database
'MsgBox i
Set rec = RS.ActiveRecord
RS.Next
If i = 1 Then 'first time through, if first time through grab the first line as previous contents
'MsgBox UBound(tempListBox2
Set field = table.GetField(sFieldname)
If field.IsCharacter Then aPrevFieldContents(0) = rec.GetCharValue(sFieldname)
If field.IsDate Then aPrevFieldContents(0) = rec.GetDateValue(sFieldname)
If field.IsNumeric Then aPrevFieldContents(0) = rec.GetNumValue(sFieldname)
'MsgBox "1"
If field.IsTime Then aPrevFieldContents(0) = rec.GetTimeValue(sFieldname)
'MsgBox aPrevFieldContents(j)
Else
bContentsChanged = False 'set the changed flag to false
Set field = table.GetField(sFieldname) 'have to know what the field type is in order to use the proper Get function
If field.IsCharacter Then aCurFieldContents(0) = rec.GetCharValue(sFieldname)
If field.IsDate Then aCurFieldContents(0) = rec.GetDateValue(sFieldname)
If field.IsNumeric Then aCurFieldContents(0) = rec.GetNumValue(sFieldname)
If field.IsTime Then aCurFieldContents(0) = rec.GetTimeValue(sFieldname)
If Trim(aCurFieldContents(0)) = "" Then 'if the current field is empty then replace it with info from previos field
If field.IsCharacter Then rec.SetCharValue sFieldname, aPrevFieldContents(0)
If field.IsDate Then rec.SetDateValue sFieldname, aPrevFieldContents(0)
If field.IsNumeric Then rec.SetNumValue sFieldname, aPrevFieldContents(0)
If field.IsTime Then rec.SetTimeValue sFieldname, aPrevFieldContents(0)
bContentsChanged = true 'as least one change has been made so save the current record
Else
aPrevFieldContents(0) = aCurFieldContents(0) 'if cell is not blank then use the info for the previous field
End If
'MsgBox i & "-" & bContentsChanged
If bContentsChanged Then
rs.SaveRecord rec 'save the record if at least one change has been made
End If
End If
Next i
table.Protect = True 'protect the table so no further changes can be made.
db.close
Set db = Nothing
Set table = Nothing
Set RS = Nothing
Set rec = Nothing
Set field = Nothing
Exit Function
ErrorHandler:
' Display the error information based on the error code.
' Treat client errors and basic errors differently.
If Client.ErrorCode > 0 Then
' This code is for client errors.
MsgBox "Error Number: " & Client.ErrorCode & Chr$(13) & "Error String: " & Client.ErrorString
Else
' This code is for basic script errors.
MsgBox "Error Number: " & Err.Number & Chr$(13) & "Error String: " & Err.Description
End If
End Function
Unicode issue
Hi. I've been using this script for some time. Lately my company changed our IDEAs to unicode version and this script is no longer available to use with this version. Brian is there any way that I can get it to work on our newest software or is there any functional alternative to this scrip? Thanks in advance.
Copying contents from one field to another
Hi Brian, I am new to IDEA and found the site a very useful one. I have a database where there are two fields. One has old Passport numbers and the other has the current passport numbers. I want to fill the blanks in the new passport number with the ones in the old passport number. Is there a way to do that in IDEA or by using this script?
Thank you
Useful scripts!
Useful scripts!