Validate matching fields for join

kkeller

Forums

I am currently working on a script, which contains one step where two databases are joined. I was hoping that someone may have a way to validate that my 2 fields to match on, are identical and able to be matched on. I currently have parts of the script that validate 'numeric' or 'date' fields, but am unaware how to validate the match. Thanks in advance for any help!.

Brian Element Thu, 09/08/2016 - 09:02

Hi Kyle,

What you need to do is get the field type before performing the join and then comparing the two field types to make sure they are the same. Here is some example code that does this. This uses the sample files for the example.

Sub Main
	Call performJoin()
End Sub

Function performJoin()
	Dim db As database
	Dim task As task
	Dim iFieldType1 As Integer
	Dim iFieldType2 As Integer
	iFieldType1 = getFieldType("Sample-Detailed Sales.IMD", "CUSTNO")
	iFieldType2 = getFieldType("Sample-Customers.IMD", "CUSTNO")
	If iFieldType1 <> iFieldType2 Then
		MsgBox "The join fields are of different types, the join cannot be performed"
		Exit Function
	End If

	
	Set db = Client.OpenDatabase("Sample-Detailed Sales.IMD")
	Set task = db.JoinDatabase
	task.FileToJoin "Sample-Customers.IMD"
	task.IncludeAllPFields
	task.AddSFieldToInc "COMPANY"
	task.AddMatchKey "CUSTNO", "CUSTNO", "A"
	dbName = "Detailed Sales with Customer Info.IMD"
	task.PerformTask dbName, "", WI_JOIN_ALL_IN_PRIM
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function


Function getFieldType(sFilename As String, sFieldname As String) As Integer
	
	Dim db As database
	Dim table As table
	Dim field As field
	
	Set db = Client.OpenDatabase(sFilename)

		Set table = db.TableDef
			
			Set field = table.GetField(sFieldname)
			
				getFeildType = field.Type 
			
			Set field = Nothing
		
		Set table = Nothing
	
	Set db = Nothing
End Function

 

kkeller Sat, 09/10/2016 - 10:03

Hi Brian,

Perfect sample! I was able to work it in with my original script and everything is running as it should. Thanks for your help!