Fill Down Macro within an IDEAScript

mllestecchino

Forums

Hi all - as always, thanks in advance for the help!
I use the Filldown Utility from the IDEAScripts section of the site all the time. What I'd like to do is incorporate that functionality into another script I've built that does some file cleanup on a standard file I receive frequently. But, I want to skip the dialogue box to select the fields to fill down and instead have a defined list of ~5 fields to run. How would I do that?
Apologies if this has already been answered and I missed it. I saw a solution elsewhere in the forum to pull the data into a new field and filldown - I was hoping handle it the same way as the utility and keep the data in its original column.
Thanks!
-Leslie

Brian Element Mon, 02/25/2019 - 08:15

Hi Leslie,

I am glad you find the utility useful, it is nice to hear that my scripts are getting good use.

What you need to do to use this script in another one is to set-up the variables that you need to run the script, this gets around the need for the dialog.  One place you can look usually in my scripts is where I validate the OKButton1, meaning I am closing the dialog and starting the script.  In this script it looks like this:

So I can see I need a variable to hold the file name and an array to hold the list of fields.  So what I would do in my script is where it says Call menu() I would replace that with setting up the needed variables so I might insert the following lines:

sFilename = "My File.IMD"

ReDim tempListBox2(3) 'assumes 4 items

tempListBox2(0) = "FIELD_1"

tempListBox2(1) = "FIELD_2"

tempListBox2(2) = "FIELD_3"

tempListBox2(3) = "FIELD_4"

Then you can look at what is needed under the section If Not bExitScript then as this is the section that actually does the work.

So what we are doing is instead of calling the menu and getting the variables from the menu we are coding them directly in the code with the same variable names so you don't have to go in and make any changes to the actual code you can just copy it directly to your code.

Hopefully this makes sense, let me know if it doesn't.

Brian

mllestecchino Tue, 02/26/2019 - 10:30

It mostly makes sense, I have a few followups.

  • When you say to replace the Call menu() with defining the variables, you mean under Sub Main? 
  • I can leave the bExitScript in place because eliminating the dialog box means that the boolean will never be true, so it will never trigger. 
  • If I want to flip from the sFilename to using the current database, how would I do that? Since it seems like the rest of the script is written to use the use the filename, I wasn't sure how to unwind that fully. 

I've attached my working copy. Apologies that it's not properly commented, I haven't cleaned it up fully...

Brian Element Tue, 02/26/2019 - 13:02

Hi Leslie,

You will also have to copy over the updateFields() function as it is what actually does the work in the fill down.

Question 1, you are correct, get rid of the Call menu() as it is being replaced by the variables.

Question 2, yes doesn't make a difference.

Question 3, to get the current database you could add the line:

sFilename = Client.CurrentDatabase.Name

If there is no file open the script will give you an error.

mllestecchino Wed, 02/27/2019 - 12:30

Thank you! I've got everything working correctly now. The only other change I had to make was to set the initial value of j to 0 instead of 1 in updateFields() function, otherwise it was skipping the filldown the variable I'd assigned to 0.