Visual Connector and task.IncludeAllfields

pcallan0

Forums

Hi Brian,
 
It's been quite a while since I've had a question and I hope all has been well with you and yours over the last couple of crazy years.
 
I'm currently working on a script and I have a question on using visual connector.
 
My script is joining several files to a primary file using visual connector and it's working.
 
However, I am using task.IncludeAllfields which is giving me a lot of unnecessary columns in my output file.
 
This makes everything quite slow when dealing with a file with hundreds of thousands of records.
 
 
My problem is that the structure of the "primary" file id0 may not always be constant.
 
This makes it impossible (at least for me) to hard code using task.AddFieldToInclude id1, "FIELDNAME" as I can't predict all the fieldnames in my primary field
 
 
However, the fields in each of the secondary files will always be the same so I can hardcode the specific fields I want from these files
 
I'm wondering is there a way to script visual connector to include all the fields in the primary file without specifiying them and just specify the secondary fields
 
 
As I said, the script does work at the moment but I'm pulling in fields I don't need from the secondary files and creating a larger output than I need.
 
Direct extraction in the next step would deal with that issue but I would prefer to speed up the "join" by reducing the fields
 
Thanks,
 
Phil
 
 
 

Brian Element Mon, 09/05/2022 - 08:15

Hi Phil,

What you could do is place the names of the primary file in an array and use the array to populate your Visual Connector.  The only problem might be if the connection fields are of different names in the primary file.  Here is an example of using an array to get the field list of the primary file:


Dim sFieldList() As String

Sub Main
	Call getFieldList()
	Call RelateDatabase()	'Sample-Detailed Sales.IMD
End Sub

Function getFieldList()
	
	Dim db As database
	Dim field As field
	Dim table As tableDef
	Dim iFields As Integer
	Dim i As Integer
	Dim bFirstTime As Boolean
	
	'get an array that holds all the field names for the primary file
	bFirstTime = True
	Set db = Client.OpenDatabase("Sample-Detailed Sales.IMD")
		Set table = db.TableDef
			iFields = table.Count
			For i = 1 To iFields
				Set field = table.GetFieldAt(i)
				If bFirstTime Then
					bFirstTime = False
					ReDim sFieldList(0) 
					sFieldList(0) = field.name
				Else
					ReDim Preserve sFieldList(UBound(sFieldList) +1)
					sFieldList(UBound(sFieldList)) = field.name
				End If
			Next i
		set table = nothing
	Set db = Nothing
End Function

' File: Visual Connector
Function RelateDatabase
	Set db = Client.OpenDatabase("Sample-Detailed Sales.IMD")
	Set task = db.VisualConnector
	id0 = task.AddDatabase ("Sample-Detailed Sales.IMD")
	id1 = task.AddDatabase ("Sample-Sales Representatives.imd")
	id2 = task.AddDatabase ("Sample-Customers.imd")
	task.MasterDatabase = id0
	task.AppendDatabaseNames = FALSE
	task.IncludeAllPrimaryRecords = FALSE
	task.AddRelation id0, "SALESREP_NO", id1, "SALESREP_NO"
	task.AddRelation id0, "CUSTNO", id2, "CUSTNO"
	'add the fields from the primary file
	For i = 0 To UBound(sFieldList)
		task.AddFieldToInclude id0, sFieldList(i)
	Next i
	task.AddFieldToInclude id1, "SALESREP_NO"
	task.AddFieldToInclude id1, "SALESREP_LAST"
	task.AddFieldToInclude id1, "SALARY"
	task.AddFieldToInclude id2, "CUSTNO"
	task.AddFieldToInclude id2, "COMPANY"
	task.AddFieldToInclude id2, "FIRST_NAME"
	task.AddFieldToInclude id2, "LAST_NAME"
	task.AddFieldToInclude id2, "STATUS"
	task.AddFieldToInclude id2, "CREDIT_LIM"
	dbName = "VisCon.IMD"
	task.OutputDatabaseName = dbName
	task.PerformTask
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
End Function

pcallan0 Mon, 09/05/2022 - 11:18

Hi Brian,
The connection field will always be the customs declaration number so that primary field is constant.
Thanks a million, the script does exactly what I want and it's going to speed up my own considerably.
All the best from ireland,
Phil

Brian Element Tue, 09/06/2022 - 07:35

Hi Phil,

Thanks for letting me know.

All the best from Canada.

Brian