Option Explicit ' Used to "convert" a single select or relational to a multi ' Translations currently supported (or real soon now) ' : Destination Field type ' : Single multi single multi multi multi ' Source field type : select select relational relational user group '------------------ + ------ ------ ---------- ---------- ------- ------- ' single-select : no yes yes yes no no ' multi-select : no no no yes no no ' single-relational : future no no yes no no ' multi-relational : no future no no no no ' user : no no no no future future ' What we need to do: ' Step 1. make sure field selectable values match ' If source is single/multi selection, iterate the selections. If it's a relational, ' iterate the aux table records. If the destingation is a single/multi selection, ' create selection values for the destination value. If the destination is a ' relational field, then create a new tuple for each selectable item. Replicate as ' many of the fields of the selection value as we can. ' ts_selections.ts_name -> usr_aux.ts_title ' ts_selections.ts_value -> usr_aux.ts_value (if it exists). ' Aux tables destinations should be designed with all fields to default to some value. ' This will prevent errors when the script attempts to create new records without ' completely initializing all aux table fields. ' Step 2. Update existing issue records so that new field issues have same selections ' as old issues. ' ** ********************************************************************* ' ** $Id: //guest/paul_m_thompson/TeamTrack/single-multi-select-relational.transmogrifier.tsc#1 $ ' ** $Change: 7352 $ ' ** $DateTime: 2009/08/17 12:56:11 $ ' ** $Author: paul_m_thompson $ ' ** ********************************************************************* ' Source field TS_ID or DB name. If numeric, is interpreted as field TS_ID, ' otherwise interpreted as field DB name. Field type must be single/multi- ' select or single/multi-relational. Const SOURCE_FIELD=3158 ' Destination field TS_ID or DB name. If numeric, is interpreted as field TS_ID, ' otherwise interpreted as field DB name. Field type must be multi-select or ' single/multi-relational. Const DEST_FIELD=1037 ' ********************************************************************************************* ' These constants can (and should) be imported from System_Manifest_Constants.tsc CONST TS_FIELDS_TABLEID=3 CONST TS_SELECTIONS_TABLEID=12 CONST TS_STATES_TABLEID=13 CONST TS_TRANSITIONS_TABLEID=16 CONST TS_TABLES_TABLEID=18 CONST TS_WORKFLOWS_TABLEID=34 CONST TS_PROJECTS_TABLEID=8 CONST TS_PROJECTSELECTIONS_TABLEID=9 CONST TS_PROJECTTRANSITIONS_TABLEID=24 CONST TS_FLDTYPE_NUMERIC = 100 ' Integer or floating-point field. CONST TS_FLDTYPE_TEXT = 101 ' Text field up to 255 characters. CONST TS_FLDTYPE_DATETIME = 103 ' Date/Time field. CONST TS_FLDTYPE_SELECTION = 104 ' Single selection field. CONST TS_FLDTYPE_BINARY = 105 ' Binary field. CONST TS_FLDTYPE_STATE = 106 ' System-defined state field. CONST TS_FLDTYPE_USER = 107 ' User selection field. CONST TS_FLDTYPE_PROJECT = 108 ' System-defined project field. CONST TS_FLDTYPE_SUMMATION = 109 ' Calculated summation field. CONST TS_FLDTYPE_MULTIPLE_SELECTION = 110 ' Multi-selection selection field. DbVer 10 CONST TS_FLDTYPE_CONTACT = 111 ' Contact selection field. DbVer 10 CONST TS_FLDTYPE_COMPANY = 112 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_INCIDENT = 113 ' Incident selection field. DbVer 10 CONST TS_FLDTYPE_PRODUCT = 114 ' Obsolete. DbVe CONST TS_FLDTYPE_SERVICEAGREEMENT = 115 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_FOLDER = 116 ' Folder link selection field. DbVer 10 CONST TS_FLDTYPE_KEYWORDLIST = 117 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_PRODUCTLIST = 118 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_PROBLEM = 119 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_RESOLUTION = 120 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_MERCHANDISE = 121 ' Obsolete. DbVer 10 CONST TS_FLDTYPE_RELATIONAL = 122 ' Single selection relational field. DbVer 10 CONST TS_FLDTYPE_SUBRELATIONAL = 123 ' Sub-relational selection field. DbVer 10 CONST TS_FLDTYPE_SYSTEM = 124 ' System field. DbVer 10 CONST TS_FLDTYPE_MULTIPLE_RELATIONAL = 125 ' Multi-selection relational field. DbVer 10 CONST TS_FLDTYPE_MULTIPLE_USER = 126 ' Multi-selection field of users. DbVer 57001 CONST TS_FLDTYPE_MULTIPLE_GROUP = 127 ' Multi-selection field of groups. ' ********************************************************************************************* Dim vbQuote , vbCr , vbLf , vbCrLf , vbEol ' ********************************************************************************************* vbQuote=chr(34) vbCr=chr(13) vbLf=chr(10) vbCrLf=vbCr & vbLf vbEol="
" & vbCrLf ' ********************************************************************************************* ' get Field info for source field Dim objApprecSrc , objApprecDest , strMojo Call Ext.LogInfoMsg("*** get source field info '" + CStr(SOURCE_FIELD) + "'" + vbCrLf) strMojo = GetFieldRec(SOURCE_FIELD,objApprecSrc) Call Ext.LogInfoMsg("*** Src Field info=" + strMojo + vbCrLf) Call Ext.LogInfoMsg("*** get dest field info '" + CStr(DEST_FIELD) + "'") strMojo = GetFieldRec(DEST_FIELD,objApprecDest) Call Ext.LogInfoMsg("*** Dest Field info=" + strMojo + vbCrLf) ' The only way to detect if the function couldn't match the field is to check the object value ' returned in the second param for NOTHING. The other "IsXXX()" functions will not tell us! If Not ((objApprecSrc Is Nothing) OR (objApprecDest Is Nothing)) Then Call Ext.LogInfoMsg("*** got AppRecord objects for '" + CStr(objApprecSrc.GetDisplayName) + "' and '" + CStr(objApprecDest.GetDisplayName) + "'") dim str , nSrcFldType , nDestFldType ' If the source Call objApprecSrc.GetFieldValue("FLDTYPE",str) nSrcFldType = CInt(str) Call objApprecDest.GetFieldValue("FLDTYPE",str) nDestFldType = CInt(str) Select Case nSrcFldType Case TS_FLDTYPE_SELECTION Select Case nDestFldType Case TS_FLDTYPE_MULTIPLE_SELECTION Call Ext.LogInfoMsg("single -> multi") Case TS_FLDTYPE_RELATIONAL Call Ext.LogInfoMsg("single -> relational") Case TS_FLDTYPE_MULTIPLE_RELATIONAL Call Ext.LogInfoMsg("single -> multi-relational") Case Else Call Ext.LogErrorMsg("Unsupported conversion") End Select Case TS_FLDTYPE_MULTIPLE_SELECTION Select Case nDestFldType Case TS_FLDTYPE_MULTIPLE_RELATIONAL Call Ext.LogInfoMsg("multi -> multi-relational") Case Else Call Ext.LogErrorMsg("Unsupported conversion") End Select Case TS_FLDTYPE_RELATIONAL Select Case nDestFldType Case TS_FLDTYPE_SELECTION ' future Call Ext.LogInfoMsg("future implementation retrograde : relational -> selection") Case TS_FLDTYPE_MULTIPLE_RELATIONAL Call Ext.LogInfoMsg("relational -> multi-relational") Case Else Call Ext.LogErrorMsg("Unsupported conversion") End Select Case TS_FLDTYPE_MULTIPLE_RELATIONAL Select Case nDestFldType Case TS_FLDTYPE_MULTIPLE_SELECTION ' future Call Ext.LogInfoMsg("future implementation retrograde : multi-relational -> multi-select") Case Else Call Ext.LogErrorMsg("Unsupported conversion") End Select Case Else Call Ext.LogErrorMsg("Unsupported conversion") End Select Else Call Ext.LogErrorMsg("Couldn't get AppRecords for field ID '" + CStr(SOURCE_FIELD) + "' or field ID '" + CStr(DEST_FIELD) + "'") End If ' ********************************************************************************************* ' ********************************************************************************************* ' ********************************************************************************************* ' Find the record in TS_FIELDS with the specified ts_id or ts_dbname. ' Set the second param to the Field's AppRecord object ' Return a string containing groovy info about the field. Function GetFieldRec(vntFldId, byRef objApprec) Dim strWhere , str2 str2 = "" + vbCrLf Set objApprec = Nothing Set objApprec = Ext.CreateAppRecord(-1) ' calling CreateAppRecord on a non-existant table returns a non-empty, non-null valid object ' that isn't usable. If objApprec is Nothing Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) is nothing!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) isn't nothing" ) End If If IsEmpty(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) IsEmpty()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) isn't IsEmpty()" ) End If If IsNull(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) IsNull()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) isn't IsNull()" ) End If If IsObject(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) IsObject()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(BOGUS) isn't IsObject()" ) End If ' We're going to get a record from TS_FIELDS. The normal TeamScript calls for working with ' fields ( CreateObject{'TeamTrack.Field'} , varRecord.GetFieldValue , etc) are for use within ' the context of accessing an issue/item in a primary table. We're going directly to TS_FIELDS ' and therefore have to do a little more work, especially getting the possible selections for ' a single or multi selection field. ' Using an AppRecord to query the TS_FIELDS table works for almost everything, but obviously ' we can't call the Fields object-specific methods like "GetSelectionList()". ' an actual Field object. I can't use the optional "recType" param for ' CreateAppRecord because - as I understand it - that specifies the type of field to be ' **created** (ts_FldType) using the constants TS_FLDTYPE_xxxx defined in the DB Schema for ' TS_FIELDS table. Set objApprec = Ext.CreateAppRecord(TS_FIELDS_TABLEID) If objApprec is Nothing Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) is nothing!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) isn't nothing" ) End If If IsEmpty(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) IsEmpty()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) isn't IsEmpty()" ) End If If IsNull(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) IsNull()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) isn't IsNull()" ) End If If IsObject(objApprec) Then Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) IsObject()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** ext.CreateAppRec(TS_FIELDS) isn't IsObject()" ) End If If IsObject(objApprec) Then Call Ext.LogInfoMsg("*** successfully created AppRecord for fields table '" + CStr(TS_FIELDS_TABLEID) + "'" ) If IsNumeric(vntFldId) Then strWhere="TS_ID=" + CStr(vntFldId) Else strWhere="TS_DBNAME like('" + CStr(vntFldId) + "')" End If Call Ext.LogInfoMsg("*** GetFieldRec: where clause = '" + strWhere + "'") If objApprec.ReadWithWhere(strWhere) Then ' Return the tuple of the specified field in TS_FIELDS Call Ext.LogInfoMsg("*** Yeeha! Found entry for '" + CStr(vntFldId) + "' in TS_FIELDS") dim str , nFldTsId str2 = vbCrLf + "Field Mojo:" + vbCrLf Call objApprec.GetFieldValue("ID",str) nFldTsId = CInt(str) str2 = str2 + " ts_id='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("TABLEID",str) str2 = str2 + " ts_tableid='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("NAME",str) str2 = str2 + " ts_name='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("DBNAME",str) str2 = str2 + " ts_dbname='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("FLDTYPE",str) str2 = str2 + " ts_fldtype='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("LEN",str) str2 = str2 + " ts_len='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("ATTRIBUTES",str) str2 = str2 + " ts_attributes='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("STATUS",str) str2 = str2 + " ts_status='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("PROPERTY",str) str2 = str2 + " ts_property='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("OPTIONS",str) str2 = str2 + " ts_options='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("MASTERID",str) str2 = str2 + " ts_masterid='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("RELATIONID",str) str2 = str2 + " ts_relationid='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("FIELDID",str) str2 = str2 + " ts_fieldid='" + str + "'" + vbCrLf Call objApprec.GetFieldValue("DESCRIPTION",str) str2 = str2 + " ts_description='" + str + "'" + vbCrLf Dim arl , ar ' Field.GetSelectionList() Returns an AppRecordList object Set arl = GetSelectionsAsArl(nFldTsId) 'IS nothing! Isn't empty, isn't null, is object. If arl is Nothing Then Call Ext.LogInfoMsg("*** GetSelectionsAsArl() is nothing!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** GetSelectionsAsArl() isn't nothing" ) End If If IsEmpty(arl) Then Call Ext.LogInfoMsg("*** GetSelectionsAsArl() IsEmpty()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** GetSelectionsAsArl() isn't IsEmpty()" ) End If If IsNull(arl) Then Call Ext.LogInfoMsg("*** GetSelectionsAsArl() IsNull()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** GetSelectionsAsArl() isn't IsNull()" ) End If If IsObject(arl) Then Call Ext.LogInfoMsg("*** GetSelectionsAsArl() IsObject()!!!!!!!!!!!!!!" ) Else Call Ext.LogInfoMsg("*** GetSelectionsAsArl() isn't IsObject()" ) End If If not arl is Nothing Then Call Ext.LogInfoMsg("objApprec isn't nothing!!!") str2 = str2 + vbCrLf + "Selection List:" + vbCrLf + "," ' Iterate the list of TS_SELECTIONS records For Each ar in arl Call ar.GetFieldValue("NAME",str) str2 = str2 + trim(str) + "," next 'why does this line give a "collection required" run-time error? Else Call Ext.LogErrorMsg("Couldn't GetSelectionList() on field object 'objApprec'") End If Else Call Ext.LogErrorMsg("Couldn't find field TS_ID='" + CStr(vntFldId) + "' in table #'" + CStr(TS_FIELDS_TABLEID) + "'") Set objApprec=Nothing End If Else Call Ext.LogErrorMsg("failed to create appRecord for table '" + CStr(TS_FIELDS_TABLEID) + "'" ) Set objApprec=Nothing End If ' Return value GetFieldRec = str2 End Function ' ********************************************************************************************* Function GetSelectionsAsArl(nFldTsId) Set GetSelectionsAsArl = Nothing ' Get all possible selectable values for field specified dim arl , strWhere Set arl = Ext.CreateAppRecordList(TS_SELECTIONS_TABLEID) If IsObject(arl) Then strWhere = "TS_FLDID=" + CStr(nFldTsId) + " AND TS_STATUS=0" Call Ext.LogInfoMsg("GetSelectionsAsArl(" + CStr( nFldTsId) + ") :: where clause=" + strWhere) If arl.ReadWithWhere(strWhere,"TS_ORDERINDEX") Then Call Ext.LogInfoMsg("ReadWithWhere(" + strWhere + ") success on field TS_ID=" + CStr(nFldTsId)) Set GetSelectionsAsArl = arl Else Call Ext.LogErrorMsg("ReadWithWhere(" + strWhere + ") failed on field TS_ID=" + CStr(nFldTsId)) End If Else Call Ext.LogErrorMsg("Failed to create AppRecordList for field TS_ID=" + CStr(nFldTsId)) End if End Function ' *********************************************************************************************