Skip to main content

Running the Same Command on Multiple Tables

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!

Brian Element Wed, 04/01/2015 - 17:49

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

bylopez Wed, 04/01/2015 - 18:12

In reply to by Brian Element

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

Brian Element Wed, 04/01/2015 - 19:12

In reply to by bylopez

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

 

bylopez Thu, 04/02/2015 - 00:46

In reply to by Brian Element

Brian,
Quite the contrary, I think you are pretty damn sharp.  Thank you for your help today and your prompt response...you helped me a ton and I am very appreciative.  
Best Regards,
Brian