Ideascripting. Pass comma separated values to a SQL Query

idemnos

Forums

Sometime ago I asked on how could I use an IDEA table as the argument for a SQL QUERY, cause  We use to import via ODBC, using a sQL Query like:
 
Select * from A, B where A.ID=B.ID and A.prod_num in ('10','20','30') and B.date between '20180101' and '20180131' 
 
Using the example you gave me sometime ago I use an Idea form where I can input date1 and date2. In this moment I just have to query by prod_nubmer but what if I need to pass at least 5 prod_nums? Can I use a textbox to input more than one of them?
 
Thanks in advance

Brian Element Wed, 04/11/2018 - 07:22

Unfortunately IDEAScript doesn't have a split command like other languages so you have to do it on yourself.  Here is an example of a simple dialog where you would enter values separated by a comma in a text box.  The code then reads through what was entered and it separates each item and places it into an array that you can then use to build your SQL or anything else.  Below is the code and I have attached the script.

 


Option Explicit
Dim sList() As String 'used to hold the items in the text box

Sub Main
	Dim dlg As NewDialog
	Dim button As Integer
	Dim sText As String 'hold complete text from text box
	Dim i As Integer
	Dim sChar As String 'hold each individual character in text box
	Dim sString As String 'used to build the individual items
	Dim bFirstTime As Boolean 'flag for array if first time used
	button = Dialog(dlg) 'call dialog
	sText = dlg.TextBox1 'get the info from the text box
	
	bFirstTime = True
	For i = 1 To Len(sText) 'loop through each character of the text box
		sChar = Mid(sText, i, 1) 'cycle through the characters
		If sChar <> "," Then 'if the character is not a , then add it to the string
			sString = sString & sChar
		Else 'if the character is a string then
			If bFirstTime Then 'if this is the first time then dimension the array
				ReDim sList(0)
				sList(0) = sString 'add string to array
				bFirstTime = False
			Else 'if not first time then add an element to the array
				ReDim preserve sList(UBound(sList) + 1)
				sList(UBound(sList)) = sString'add string to array
			End If
			sString = "" 'reset string to blank
		End If	
	Next i
	
	'grab the last entry
	ReDim preserve sList(UBound(sList) + 1)
	sList(UBound(sList)) = sString
	
	'display the entries
	For i = 0 To UBound(sList)
		MsgBox sList(i)
	Next i
End Sub