Using a column values into a sql query in ideascript

Hi Elías,

Yes I htink i tis doable.  I will have to do a bit of expermenting as I don't often use ODBC and my SQL is a bit rusty.  Let me get back to you on this.

Thanks

Brian

Hello, please I can collaborate with the following: required to build a variable on which to accumulate the same previous result. Thank you.

Hi Everardo,

Here is a sample ODBC import using a SQL statement.  What I have done is created a string field to hold the SQL and I also created three variables sField1, 2 and 3 to hold the fields I want to bring in from the Access database.  I then insert the field names into the SQL statement.

So in your case you would have your SQL statement and you would set-up a variable to hold the item that you want to import, you could also create a loop if you wanted to import multiple items into different IDEA databases.

So hopefully this is a starting point for you and let me know if it helps.

Brian

Sub Main
	Call ODBCImport()	'
End Sub


' File - Import Assistant: ODBC
Function ODBCImport
	Dim sql As String
	Dim sField1 As String
	Dim sField2 As String
	Dim sField3 As String
	
	sField1 = "HISTORYLOG"
	sField2 = "RECORDGUID"
	sField3 = "TASKSTREAM"
	dbName = "Overview1.IMD"
	sql = "SELECT " & sField1 & ", " & sField2 & ", " & sField3 & " FROM " & Chr(34) & "Overview" & Chr(34) & ""
	Client.ImportODBCFile "" & Chr(34) & "Overview" & Chr(34) & "", dbName, FALSE, ";DSN=MS Access Database;DBQ=C:\Users\My Drive\Documents\IDEA\Samples\TestProjectOverview.MDB;DefaultDir=C:\Users\My Drive\Documents\IDEA\Samples;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;", sql
	Client.OpenDatabase (dbName)
End Function

Hey there. Thank you very much for this. I'll have to give it a try and I'll comment you guys for results.

Hi again. I'll tell you what I have done, what are the curent results and what I'm looking for.
I've been collecting some ideascripts and compile a very large script which in resume have the following

'---------------------Begin of the ideascript-------------------
Dim var_usuario, var_password, var_oficina, var_oficina1, var_oficina2
Dim var_fecha_ini, var_fecha_fin

Sub Main

var_usuario = InputBox("Ingrese el usuario de la BD: ")
var_password = InputBox("Ingrese la contraseña de la BD: ")

var_fecha_ini = InputBox("Ingrese la fecha INICIAL en formato AAAAMMDD: ")
var_fecha_fin = InputBox("Ingrese la fecha FINAL en formato AAAAMMDD: ")

var_oficina = InputBox("input_message: ")
var_oficina1 = InputBox("input_message: ")
var_oficina2 = InputBox("input_message: ")

If var_usuario = "" Or var_password = "" Or var_oficina = "" Or var_oficina <= 0 Or var_fecha_ini = "" Or var_fecha_fin ="" Or var_oficina1="" Or var_oficina2= "" Then

MsgBox "validation message"

Else
If var_fecha_fin < var_fecha_ini Then

MsgBox "validation message"
Else
'calling de import function which use odbc
Call ODBCImport() '
end if
end if
end sub ' here finishes main function

Function ODBCImport
dbName = "forma_cobro.IMD"
Client.ImportODBCFile " dbo . rs_threads ", dbName, FALSE, ";DSN=REMOTO;UID=" & var_usuario & ";PWD=" & var_password & ";NA=", "Select from where and table1.cdate between '" & var_fecha_ini & "' and '" & var_fecha_fin & "' And A.op_oficina In ( " & var_oficina & ", " & var_oficina2 & ") " Client.OpenDatabase (dbName)
End Function
'--------------------------------END of the ideascript--------------------------------

As you can see I use input box to assign values to the variables. Then I import an excel file with offices data and use the idea union function correlate-> union> to join data. What I'm looking for is: imagine leave just on var_oficina and use a column from an idea database (*.IMd file) and pass every value to var_oficina as in 'abc','cde',efg',....,'xyz'.

Sub Main
	Dim db As database
	Dim rs As RecordSet
	Dim rec As record
	Dim count As Long
	Dim i As Long
	Set db = Client.OpenDatabase("My File.IMD")
		
		Set rs = db.RecordSet
			count = rs.Count
		
			rs.ToFirst
			For i = 1 To count
				rs.Next
				Set rec = rs.ActiveRecord
				MsgBox rec.GetCharValue("MY_FIELD") 
			Next i
			Set rec = Nothing
		Set rs = Nothing
	Set db = Nothing

End Sub

 Hi Everardo,

I am not 100% sure I understand what you are looking for but it sounds like you want to populate part of your ODBC from an IDEA file.  If so I have posted some code above that will take a file and loop through it.  In this case it will show the contents of the MY_FIELD field.  You can adapt this code by just changing the message box to a variable.  So you would get the item in the IDEA cell and then send it to your ODBC import function.

So hopefully this is what you are looking for.

Thanks

Brian