Field names

MagiBzh

Forums

Hi,
I would like to know if it is possible to create a variable that is very general:
for example "Dim a as an integer" with a=any integer or a range of integer.
I would like to do that because I have some field names whose name are pratically the same, only the number at the end change:
"SEGMENT1"
"SEGMENT12"
"SEGMENT13"
I would to know if it is possible to have a variable that can store any integer and have something like:
"SEGMENT"&a
Thanks in advance for the help :)

Brian Element Thu, 07/30/2015 - 11:07

Yes, you should be able to do something like that.

So if you want to access those fields you could use a loop such as

for a = 1 to 13

     do something with "SEGMENT" & a

next a

I use that sometimes, it comes in handy when access a large amount of information.

So hopefully this is what you are looking for, if not let me know.

MagiBzh Fri, 07/31/2015 - 08:21

Thanks Brian for replying so soon.
I understand the big picture, but I do not manage to put in the script :
I want to use "the check if field name exists" in order to know if the fields named "SEGMENT"& a are in the table. If this condition is true then I would like to add those specific fields to the list of fields to inc for the extraction:
 task.AddFieldToInc "CODE_COMBINATION_ID" task.AddFieldToInc "CHART_OF_ACCOUNTS_ID"
task.AddFieldToInc "SEGMENT"&a
I tried to put them directly like this (see just above)in the extraction function, obviously it didn't work: if I put only this line, it will extract only one field named "SEGMENT"&a but I need all the fields named "SEGMENT&a" where a can be a number between 1 and 30.
The second condition is that those fields named "SEGMENT" aren't empty, then add them to the field to Inc. I checked the Identify Blank / Repeat Fields script, that I use a lot by the way ;).
If you could tell me if it is possible to do at least the first condition and give me some hints, it would be great :)
 

Brian Element Fri, 07/31/2015 - 08:51

No problem, see my next post on some code that will help you check if a field exists or not.  So what I did is created an array to hold the field names.  I think tried to open each field with a for next loop.  I also used an on error resume next as IDEA will throw an error if the field does not exist, if it does exist I put it in the array, if it doesn't I reset the error number and try the next one.  So hopefully this will help you out.

If the field is a numeric, date or time you can use the field stats to see if the field is empty, unfortunately we don't have that info for fields that are character.  Glad the Identify Blank / Repeat fields is helping you out.

Brian Element Fri, 07/31/2015 - 08:51 
Option Explicit

Sub Main
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	Dim sFields() As String
	Dim bFirstEntry As Boolean
	
	ReDim sFields(0)
	
	Set db = client.openDatabase("test.IMD")
		Set table = db.TableDef
			For i = 1 To 5
				On Error Resume Next
				Set field = table.GetField("SEGMENT" & i)
				If err.number = 0 Then
					If bFirstEntry Then
						ReDim preserve sFields(UBound(sFields) + 1)
						sFields(UBound(sFields)) = "SEGMENT" & i
					Else
						bFirstEntry = True
						sFields(0) = "SEGMENT" & i
					End If
				Else
					'reset the error number
					err.number = 0
				End If
			Next i
			Set field = Nothing
		Set table = Nothing
	Set db = Nothing
	
	For i = 0 To UBound(sFields)
		MsgBox sFields(i)
	Next i
End Sub

 

abrahma Mon, 11/02/2015 - 09:13

Brian,
How can I display all array elements in one message box? I am looking for a message that will give fieldnames in one message box.
Thanks much
 
 
 

Brian Element Mon, 11/02/2015 - 10:02

Here is some code For you. It first gets the fields from the Open database (If no database is Open it will throw an Error), it stores the field names In an Array called sFields And Then it populates a varaible sMsg that is used To display the list. Hope this helps you out.

Option Explicit

Sub Main
	Dim CRLF As String
	Dim sFilename As String
	Dim sFields() As String
	Dim db As database
	Dim table As table
	Dim field As field
	Dim i As Integer
	Dim count As Integer
	Dim sMsg As String
	CRLF = Chr(10) & Chr(13)
	
	Set db = client.CurrentDatabase
		Set table = db.TableDef
		
			count = table.count
			ReDim sFields(count)
			For i = 1 To count
				Set field = table.GetFieldAt(i)
				sFields(i) = field.name
			Next i
			Set field = Nothing
		Set table = Nothing
	Set db = Nothing
	
	For i = 1 To count
		sMsg = sMsg & sFields(i) & CRLF
	Next i
	
	MsgBox sMsg
End Sub