/* The function in this script will convert text pasted into a script window into a format that can be inserted into an SQL statement. This is a handy way to deal with a list of items copied from an Excel sheet that need to be in an SQL statement. The SQL format shown here is for puttin into an IN() statement. If you have line numbering turned on in the script preferences, you will also see the number of items you will add. Need to have log window open to see the output. 3-18-2016 jay.holavarri@hp.com */ Names Default To Here( 1 ); /* fn_scriptBox = Function( {_vv}, //formats the script box output for SQL _yy = _vv << Get Lines; _zz = "'"; For( i = 1, i <= N Items( _yy ), i++, _zz ||= _yy[i] || "','" ); _zz = Left( _zz, Length( _zz ) - 2 ); ); //end fn_scriptbox */ fn_scriptBox = Function( {_vv}, //formats the script box output for SQL _yy = _vv << Get Lines; _zz = "'" || Concat Items (_yy, "','") || "'"; ); sql_statement = " SELECT MY_STUFF FROM MY_TABLE WHERE PART_ID IN (_mylist_) "; myWin = New Window( "Example of Script Box as Text Entry Box", Show Menu( 0 ), Show Toolbars( 0 ), , H List Box( Panel Box( "Past List Box", Text Box( "" ), sb_list = Script Box( "", "", 200, 200 ) ), Button Box( "Show SQL", itemList = fn_scriptbox( sb_list ); Substitute Into(sql_statement,"_mylist_",itemList); //insert into SQL statement show (sql_statement); //puts the list in the log file ); ) ); myWin << Set Window Size(400, 400);