Script to Get the "Field Manipulation" window info

Hi MagiBzh,

Here is a script that will do this for you.  It takes the informatoin from the field manipulation dialog and places it in an IDEA file.  I have also attached the file for you to use.

'****************************************************************************************************
'* Script:	Field_Manipulation_Parameters.iss
'* Author:	Brian Element - brian.element@ideascripting.com
'* Date:		Nov 12, 2017
'* Purpose:	To take the informatoin that is available from the field manipulation dialog and place it into an IDEA database
'* This script is provided without any warranty or guarantee.  Anybody using this script
'* is encouraged to validate the effectiveness and reliability on their own.
'****************************************************************************************************
Option Explicit
Dim sFilename As String  'holds the filename of the file to extract the information
Dim sFieldDetailsFilename As String ' holds the filename of the new file
Type FieldDetails 'holds the information from the field manipulation dialog
	FieldName As String
	FieldType As String
	FieldLength As Integer
	FieldDecimal As Integer
	FieldParameter As String
	FieldDescription As String
End Type

Dim fieldTypes(13) As String 'used to hold the different field types

Dim FieldInfo() As FieldDetails 'create an array to hold the field info

Sub Main
	'populate the field types array
	fieldTypes(WI_VIRT_CHAR) = "Virtual Character"
	fieldTypes(WI_VIRT_NUM) = "Virtual Numeric"
	fieldTypes(WI_VIRT_DATE) = "Virtual Date"
	fieldTypes(WI_CHAR_FIELD) = "Character Field"
	fieldTypes(WI_NUM_FIELD) = "Numeric Field"
	fieldTypes(WI_DATE_FIELD) = "Date Field"
	fieldTypes(WI_EDIT_NUM) = "Editable Numeric"
	fieldTypes(WI_EDIT_CHAR) = "Editable Character"
	fieldTypes(WI_EDIT_DATE) = "Editable Date"
	fieldTypes(WI_MULTISTATE) = "Multistate"
	fieldTypes(WI_BOOL) = "Boolean"
	fieldTypes(WI_TIME_FIELD) = "Time Field"
	fieldTypes(WI_EDIT_TIME) = "Editable Time"
	fieldTypes(WI_VIRT_TIME) = "Virtual Time"
	
	Call getFile() 'get the file that the field information will be extracted from
	If sFilename <> "" Then 'if no file selected exit script
		Call getFieldInfo() 'get the field info
		Call createFieldManipulationDB() 'create the IDEA file to insert the field info
		Call addFieldInfoToDB() 'insert the field info
		client.refreshFileExplorer
		MsgBox "Script Complete"
	Else
		MsgBox "No file selected, script ending"
	End If
End Sub

'used to insert the field info into the new database
Function addFieldInfoToDB()
	Dim db As database
	Dim table As table
	Dim rec As record
	Dim rs As recordSet
	Dim i As Integer
	
	Set db = client.OpenDatabase(sFieldDetailsFilename)
		Set table = db.tabledef
			table.protect = false
			Set rs = db.RecordSet
				For i = 0 To UBound(FieldInfo)
					Set rec = rs.newRecord
						rec.SetCharValue "FIELD_NAME", FieldInfo(i).FieldName 
						rec.SetCharValue "TYPE", FieldInfo(i).FieldType
						rec.SetNumValue "LENGTH", FieldInfo(i).FieldLength 
						rec.SetNumValue "DECIMALS", FieldInfo(i).FieldDecimal
						rec.SetCharValue "PARAMETER", FieldInfo(i).FieldParameter 
						rec.SetCharValue "DESCRIPTION", FieldInfo(i).FieldDescription
						rs.appendRecord rec
					Set rec = Nothing	
				Next i
			Set rs = Nothing
			table.protect = true
		Set table = Nothing
		db.close
	Set db = Nothing
	'when updated the database the database has to be closed and then opened again for the information to appear
	client.OpenDatabase(sFieldDetailsFilename)
End Function

'get the file to perform the analysis on
Function getFile()
	Dim obj As Object
	
	Set obj = client.CommonDialogs
		sFilename = obj.FileExplorer()
	Set obj = Nothing
End Function

'get the field info from the file and place it into an array
Function getFieldInfo()
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	
	Set db = client.OpenDatabase(sFilename)
		Set table = db.TableDef
			ReDim FieldInfo(table.count - 1) 'arrays start at 0 and fields start at 1 so subtract 1 so first field starts at 0
			For i = 1 To table.count
				Set field = table.GetFieldAt(i)
					FieldInfo(i - 1).FieldName = field.name
					FieldInfo(i - 1).FieldType = fieldTypes(field.type) 
					FieldInfo(i - 1).FieldLength = field.length
					FieldInfo(i - 1).FieldDecimal = field.decimals
					If field.type <> WI_NUM_FIELD Then 'numeric field gives something different for the parameter so exclude it.
						FieldInfo(i - 1).FieldParameter = field.equation
					End If
					FieldInfo(i - 1).FieldDescription = field.description
				Set field = Nothing
			Next i
		Set table = Nothing
	Set db = Nothing
End Function

'create the IDEA file to hold the information
Function createFieldManipulationDB()
	Dim db As database
	Dim table As table
	Dim field As field
	
	Set table = client.NewTableDef
		Set field = table.NewField
			field.name = "FIELD_NAME"
			field.Type = WI_CHAR_FIELD
			field.Length = 40
			table.AppendField Field
			
		Set field = table.NewField
			field.name = "TYPE"
			field.Type = WI_CHAR_FIELD
			field.Length = 15
			table.AppendField Field
			
		Set field = table.NewField
			field.name = "LENGTH"
			field.Type = WI_NUM_FIELD
			field.Decimals = 0
			table.AppendField Field
			
		Set field = table.NewField
			field.name = "DECIMALS"
			field.Type = WI_NUM_FIELD
			field.Decimals = 0
			table.AppendField Field
			
		Set field = table.NewField
			field.name = "PARAMETER"
			field.Type = WI_CHAR_FIELD
			field.Length = 200
			table.AppendField Field
			
		Set field = table.NewField
			field.name = "DESCRIPTION"
			field.Type = WI_CHAR_FIELD
			field.Length = 200
			table.AppendField Field
			
		Set field = Nothing
		sFieldDetailsFilename = client.UniqueFilename(getFilename() & "- Field Details")
		
		Set db = client.NewDatabase(sFieldDetailsFilename, "", Table)
		Set db = Nothing
	Set table = Nothing
End Function

'get only the filename portion of the selected file (remove the path)
Function getFilename() As String
	Dim tempFilename As String
	tempFilename = Left(sFilename, Len(sFilename) - 4) 'remove extension
	getFilename = iSplit(tempFilename, "", "\", 1, 1)
End Function

Hi Brian,Thanks a lot ! this is exactly what I needed :D
Then I just can make a join between the two tables I need to append, it definitely ease the comparison, especially when I have 30 columns.
Br,
MAI