User determine column to test extraction

scox

Forums

Hi Brian,
Is it possible for the user to enter the column to be tested and the extraction to use the user defined variable instead of hardcoding a column which will need to changed every extraction?
colNum = InputBox("Enter column to be tested (Col1, Col2, Col3 etc...)")
task.AddExtraction dbName, "", "@Isini(""Oper"", colNum)"
These are the two lines specifically. I tried to do it and it shows "Bad equation provided"; however, if i type Col1 instead of colNum, then it works.
Any ideas?
Thanks!

Brian Element Tue, 10/03/2017 - 11:53

Hi scox,

The reason for your problem is that you have included the variable inside your equation, so instead of looking for the item inside your varaible the equation is looking for your variable name, so in your case the equation is looking for a field called colNum instead of Col1.  What you have to do is put the variable outside of your equation so that the variable gets inserted in the equation.  You do that by having the variable outside of the strings.

So your equation becomes:

task.AddExtraction dbName, "", "@Isini(""Oper"", " & colNum & ")"

by doing this you are now inserting the value that is held by your variable into the equation.

Hopefully that makes a bit of sense.

Brian

scox Tue, 10/03/2017 - 12:12

Hi Brian,
Thank you very much, it works! I'll keep that in mind for the future.
 
 

scox Fri, 10/06/2017 - 15:52

Hi Brian,
This is seemingly unrelated but I am trying to have the user input a date and that will be used to populate the appended field. However, this pulls up the name of the field and not the date that the user inputted. Any ideas how to go about this?
I have this so far.
' Append Field
Function AppendField1
Set db = Client.OpenDatabase(filename)
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "AOD"
field.Description = ""
field.Type = WI_VIRT_CHAR
field.Equation = """& userSelAOD &"""
field.Length = 15
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
Thanks for your assistance and I hope you have a great weekend!
scox

Brian Element Sun, 10/08/2017 - 08:23

Hi scox, you will have to supply more of the code as I can't tell what userSelAOD is, that is probably where your problem is but without more of the code I can't help you out here.

scox Mon, 10/09/2017 - 09:38

I have attached the file. I am having issues with pulling in the user entered data in AppendField1 to the field and AppendField3 to the formula.
Thanks for your assistance.
scox
 
Edit: The version I gave you had a few lines added which made the script loop endlessly. I've removed it so i runs through until the error in AppendField 3 appears.

Brian Element Mon, 10/09/2017 - 10:04

Hi Scox,

Thanks for the code, it helps out.  So for AppendField1 you are missing a set of quotes.  It should look like this:

field.Equation = """" & userSelAOD & """"

For AppendField3 you need to have the field name for the field you have created instead of reusing the variable which isn't a field name.  So try this:

field.Equation = "@If(@Age(AS_OF_DATE, " & userSelYear & " ) > 90, " & userSelAmt & " ,0)"

This seems to have got the script working.  Let me know how it goes.

Brian