Check if field exists

Robert van den…

Forums

Hi, in the script of generate an audit number is a function to check if the fields exists.
http://ideascripting.com/ideascript/generate-audit-number
I want to use that function to create a indicator if the fields exists (variabele "Result"). To reset it at the start of the script I set it to unkown. I placed a line to set Result to "Exists" afters TempFieldname = sTempFieldname & "1". But i can't find the right place for the line Result = "Not exists". 
Now if the field exists I get the result "exists" but when the field not exists I now get "Unkown". Where do I need to add the line Result = "Not exists" so it works properly? I tried serval places but then if the field exists i get the wrong result. This is perhaps because I don't follow the code very well because of my basic knowledge. 
------------------------------------------------------------------------------------------------
Option Explicit 
 
Sub Main
Call CheckIfFieldExists("DESC1", "AUD2018.IMD")
End Sub
 
Function CheckIfFieldExists(sTempFieldname As String, sTempFileName As String) As String
Dim db As database
Dim table As table
Dim field As field
Dim Result As String
Set db = Client.OpenDatabase(sTempFileName)
Set table = db.TableDef
Result = "Unknown"
err.number = 0 'reset in case of a previous error
On Error Resume Next
Do
Set field = table.GetField(sTempFieldname)
If err.number <> 0 Then
CheckIfFieldExists = sTempFieldname
err.number = 0
GoTo endFunction
Else
sTempFieldname = sTempFieldname & "1"
Result = "Exists"
End If
Loop While True
endFunction:
Set field = Nothing
Set table = Nothing
Set db = Nothing
MsgBox Result
Client.CloseDatabase(sTempFileName)
End Function
 

Brian Element Tue, 06/25/2019 - 09:35

Hi Robert,

I reworked the code so it will return an Exists or Not Exists depending if the field is there.  You need to make the result equal to the Function name for it to be returned.  Here is the updated code:


Sub Main
	MsgBox CheckIfFieldExists("MY_FIELD", "My File.IMD")
End Sub

Function CheckIfFieldExists(sTempFieldname As String, sTempFileName As String) As String
	Dim db As database
	Dim table As table
	Dim field As field
	Dim Result As String
	Set db = Client.OpenDatabase(sTempFileName)
	Set table = db.TableDef
	err.number = 0 'reset in case of a previous error
	On Error Resume Next
	Set field = table.GetField(sTempFieldname)
	If err.number <> 0 Then
		Result = "Not Exists"
	Else
		Result = "Exists"
	End If
	
	Set field = Nothing
	Set table = Nothing
	Set db = Nothing
	CheckIfFieldExists = Result
	Client.CloseDatabase(sTempFileName)
End Function

Robert van den… Tue, 06/25/2019 - 10:26

Thanks, now it work perfect. Also in the previous code the variable with the fieldname was changed. There was a 1 added to that variable value after the CheckIfFieldExists was preformed. I'am curious how that happend because I can't find it in the code and with this new version it's gone. I uploaded the script file, and it only happens when the field exists. Can you see how that is happening? Maybe than i will understand it better how to look at this type of code. 

Brian Element Tue, 06/25/2019 - 14:31

Hi Robert,

I cleaned up the code so I removed the portion where it was adding a 1.  What was doing it was the do ... loop

Do
	Set field = table.GetField(sTempFieldname)
	If err.number <> 0 Then
		CheckIfFieldExists = sTempFieldname
		err.number = 0
		GoTo endFunction
	Else
		sTempFieldname = sTempFieldname & "1"
		Result = "Exists"
	End If
Loop While True

What you are looking for in this loop is an error which means that the field does not exist. If you receive an error it adds a 1 to the field name and then tests if the fields exists, if it does it adds another 1 and it keeps going until there is finally an error in which case it exists the loop and returns the new fieldname.

Hope that makes sense.

Brian