Array to hold all Field Information

Brian Element's picture
Submitted by Brian Element on Mon, 12/28/2020 - 09:01

I use the Type procedure that allows you to create a user-defined data type containing one or more elements.  I create an element for each of piece of information that is contained in the field then I use this type as an array and populate it with all the field information.  This is an easy way to get all the information on the fields in a database in a quick manner.

Snippet: 

Option Explicit

'create the type definition that will be used to hold the field information
Type Fields
	sName As String
	sDescription As String
	sType As String 'char for character, num for numeric, date for date, time for time
	iLength As Integer
	iDecimals As Integer
	sEquation As String
	bHasActionField As Boolean
	bIsImpliedDecimal As Boolean
	bIsVirtual As Boolean
	iTypeDetail As Integer 'see typeconstant for all types
End Type

Dim tFields() As Fields 'define the array to hold all the field information

Sub Main

	Call getFields("General Ledger-GL")
	Dim msg As String
	Dim i As Integer
	For i = 0 To UBound(tFields)
		msg = "Field Name: " & tFields(i).sName & Chr(13) & Chr(10)
		msg = msg & "Description: " & tFields(i).sDescription & Chr(13) & Chr(10)
		msg = msg & "Type: " & tFields(i).sType & Chr(13) & Chr(10)
		msg = msg & "Length: " & tFields(i).iLength & Chr(13) & Chr(10)
		msg = msg & "Decimals: " & tFields(i).iDecimals & Chr(13) & Chr(10)
		msg = msg & "Equation: " & tFields(i).sEquation & Chr(13) & Chr(10)
		msg = msg & "Has Action Field: " & tFields(i).bHasActionField & Chr(13) & Chr(10)
		msg = msg & "Implied Decimals: " & tFields(i).bIsImpliedDecimal & Chr(13) & Chr(10)
		msg = msg & "Virtual Field: " &  tFields(i).bIsVirtual & Chr(13) & Chr(10)
		msg = msg & "Type Detail: " & tFields(i).iTypeDetail & Chr(13) & Chr(10)
		MsgBox msg
	Next i
	
End Sub

Function getFields(sTempFilename As String)
	Dim db As database
	Dim table As table
	Dim field As field
	Dim count As Integer
	Dim i As Integer
	
	Set db = client.OpenDatabase(sTempFilename)
		Set table = db.TableDef
			count = table.count
			ReDim tFields(count - 1) 
			For i = 1 To count
				Set field = table.GetFieldAt(i)
				tFields(i - 1).sName= field.name
				tFields(i - 1).sDescription = field.description
				If field.IsCharacter Then
					tFields(i - 1).sType = "char" 
				ElseIf field.IsNumeric Then
					tFields(i - 1).sType = "num"
				ElseIf field.IsDate Then
					tFields(i - 1).sType = "date"
				ElseIf field.IsTime Then
					tFields(i - 1).sType = "time"
				End If	
				tFields(i - 1).iLength = field.Length
				tFields(i - 1).iDecimals = field.Decimals
				tFields(i - 1).sEquation = field.Equation 
				tFields(i - 1).bHasActionField = field.HasActionField
				tFields(i - 1).bIsImpliedDecimal = field.IsImpliedDecimal
				tFields(i - 1).bIsVirtual = field.isVirtual
				tFields(i - 1).iTypeDetail = field.Type
			Next i
		Set table = Nothing
	Set db = Nothing
End Function