Running the Same Command on Multiple Tables
Forums
Hello-
I was wondering if someone could help me develop a script that will perform the same operation (simply creating three virtual fields) on multiple IDEA tables. What I have are different tables with distinct names, however the fields within each table are the same (name and format). I want to run a script to create virtual fields in these tables all at once. As of now, I am running a script and using "find and replace" to change the table names, then running the script over and over again. I was wondering if there is a better way to do this as I have over 1,000 tables to get through.
Thanks!
Hi Brian,
Hi Brian,
Thanks so much for your response. Yes, i will be performing the same function on all the IDEA files within the project folder and don't have to select individual files. It has been some time since I took the scripting course so I can't remember how to set the loop. I am simply creating 3 virtual fields, however as i mentioned i need to do it for a few thousand tables. Here is what I have been using:
Sub Main
Call AppendField() '080588N156-TB3 BAL60L +23C.IMD
Call AppendField1() '080588N156-TB3 BAL60L +23C.IMD
Call AppendField2() '080588N156-TB3 BAL60L +23C.IMD
End Sub
' Append Field
Function AppendField
Set db = Client.OpenDatabase("080588N156-TB3 BAL60L +23C.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "RPT_REF"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@Precno()"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
' Append Field
Function AppendField1
Set db = Client.OpenDatabase("080588N156-TB3 BAL60L +23C.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MAX_PT_RANGE_KPA"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@val(@AllTrim(@Split( MAX_PT_RANGE,"""","" "",1,0)))"
field.Decimals = 2
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
' Append Field
Function AppendField2
Set db = Client.OpenDatabase("080588N156-TB3 BAL60L +23C.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MAX_PT_RANGE_TIME_MSEC"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@val(@AllTrim(@Split( MAX_PT_RANGE,"" "",""msec"",1,0)))"
field.Decimals = 2
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
I truly appreciate any help you can provide.
Thanks,
Brian
How Brian, I think this will
How Brian, I think this will work for you.
Public files()
Sub Main
Dim sPath As String
Dim i As Long
Dim sFilename As String
sPath = Client.WorkingDirectory()
Call findFiles(sPath, files())
For i = 1 To UBound(files())
sFilename = files(i)
Call AppendField(sFilename) '080588N156-TB3 BAL60L +23C.IMD
Call AppendField1(sFilename) '080588N156-TB3 BAL60L +23C.IMD
Call AppendField2(sFilename) '080588N156-TB3 BAL60L +23C.IMD
Next i
End Sub
' Append Field
Function AppendField(sFilename As String)
Set db = Client.OpenDatabase(sFilename)
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "RPT_REF"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@Precno()"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
' Append Field
Function AppendField1(sFilename As String)
Set db = Client.OpenDatabase(sFilename)
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MAX_PT_RANGE_KPA"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@val(@AllTrim(@Split( MAX_PT_RANGE,"""","" "",1,0)))"
field.Decimals = 2
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
' Append Field
Function AppendField2(sFilename As String)
Set db = Client.OpenDatabase(sFilename)
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "MAX_PT_RANGE_TIME_MSEC"
field.Description = ""
field.Type = WI_VIRT_NUM
field.Equation = "@val(@AllTrim(@Split( MAX_PT_RANGE,"" "",""msec"",1,0)))"
field.Decimals = 2
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
Private Function findFiles(path As String, files())
Dim ffile As String
Dim firstbackspace As String
Dim firstbackspacenum As String
Dim importname As String
Dim ext As String
Dim i As Integer
ffile = Dir$(path & "*." & "imd")
ext = "imd"
Do
firstbackspace = strReverse (ffile)
firstbackspacenum = InStr(1,firstbackspace, ".")
importname = Right(ffile, firstbackspacenum - 1)
If Len(importname) = Len(ext) Then
If Not IsNull(ffile) Then
'-------- If one value found return function true and redim array
If (findFiles = False) Then
ReDim files(0)
findFiles = True
Else
ReDim Preserve files(UBound(files) + 1)
End If
files(UBound(files)) = ffile
Else
Exit Do
End If
End If
ffile = Dir
Loop Until Len(ffile) = 0
End Function
Hi Brian and welcome to the
Hi Brian and welcome to the site.
Yes, a script would be perfect for this. So in your case would you be performing this on all the IDEA files within the project or would you be selecting the files? The easiest would be just performing it on all the files as the script could just use a loop for each file. If you are selecting IDEA files then that will entail using a dialog with additional code. Let me know which works for you.
Thanks