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.
Comments
dkangaroo
Mon, 03/30/2015 - 14:53
Permalink
Useful scripts!
Useful scripts!
Brian Element
Mon, 03/30/2015 - 18:30
Permalink
Welcome to the site, I am
Welcome to the site, I am glad you found it useful.
smurphy0
Wed, 09/28/2016 - 09:36
Permalink
@justnumbers function
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
Wed, 09/28/2016 - 09:58
Permalink
Hi Susan, I just sent you a
Hi Susan, I just sent you a pm, let me know what you are looking for.
Brian
macroKV
Thu, 05/28/2015 - 10:38
Permalink
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!)
Brian Element
Sat, 05/30/2015 - 06:29
Permalink
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.
mkivuti
Tue, 01/26/2016 - 05:39
Permalink
Download Script
Very useful. How can I get the script?
Brian Element
Tue, 01/26/2016 - 07:05
Permalink
Hello mkivuti,
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
Angelique_Charter
Mon, 09/12/2016 - 09:05
Permalink
Can this be simplified?
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
Mon, 09/12/2016 - 13:37
Permalink
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
CKnowles80
Thu, 08/03/2017 - 16:37
Permalink
Thank You!!!
Awesome this script saved me some work
Brian Element
Thu, 08/03/2017 - 18:15
Permalink
Welcome to the site. Glad
Welcome to the site. Glad that it helped you out. I use it quite often.
musfar
Wed, 08/19/2020 - 09:55
Permalink
Awesome !! Superb, YAY!
Thanks a lot!!!!
Works well, used previously the excel conditions to populate the rows, now its just a click away!!
musfar
Thu, 08/20/2020 - 03:47
Permalink
Though the script works and
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
Maciek
Thu, 02/11/2021 - 03:10
Permalink
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.
Brian Element
Thu, 02/11/2021 - 06:49
Permalink
Hi Maciek, if you have access
Hi Maciek, if you have access to passport, go to SmartAnalyzer Apps - IDEA Utilities and install the utilities as the filldown script is now part of the utilities package.
neduindia
Fri, 12/24/2021 - 21:57
Permalink
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
neduindia
Fri, 12/24/2021 - 23:41
Permalink
I used the equation editor
I used the equation editor and got the work done.
The site helped me. Thank you.
Brian Element
Thu, 12/30/2021 - 09:00
Permalink
Hi neduindia, thanks for
Hi neduindia, thanks for letting me know you figured out if. I would have recommended the same, using the equation editor and just creating a new field with an @if statement to check if one is empty.