Fill Down Utility

Background: 

This script will allow you to fill empty cells within a database from information contained in the previous cell.

Documentation: 

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.

Fill Down 1

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.

Fill Down 2

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.

Fill down 3

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.

fill down 4

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.

Tags:

Comments

Useful scripts! 

Brian Element's picture

Welcome to the site, I am glad you found it useful.

Hi Brian,
I am new to Idea but I have to givea presentation on the @justnumbers function. Could you help me with this?
Thanks :-)

Brian Element's picture

Hi Susan, I just sent you a pm, let me know what you are looking for.

Brian

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!)

Brian Element's picture

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 

Very useful. How can I get the script?

Brian Element's picture

Hello mkivuti,

If you are logged into the site just above the Comments section you should see IDEAScript: along with a link to download the script.  If you are not seeing this let me know and I will figure out what is going on.

Thanks

Brian

In my script, I do not need to prompt the user to select which fields to fill down; I only have one field that needs to be filled down.  Is there a simplified version of the script that I can modified for this purpose?
Thanks!

Brian Element's picture

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

 

Awesome this script saved me some work

Brian Element's picture

Welcome to the site.  Glad that it helped you out.  I use it quite often.

Thanks a lot!!!!
Works well, used previously the excel conditions to populate the rows, now its just a click away!!

Though the script works and auto - fills in the column, filled data is not showing up while doing summarisation nor doing Exclusion test for ducplicates.
please advice