How to get the SetValue method to work with a Single Selection field?

[forumadmin]

Question posted 4/15/09 by Joseph Ward
Details:

The Scenario:
I'm trying to use the SetValue method to update the value of a Single Selection field during a transition. The selection field currently has two values in the dropdown, "Yes" and "No", and the script is trying to set the "Yes" value by passing it in as a string.
The Problem:
I do not get an error when the script executes, but the value is not updated in the Single Selection field. The SetValue method works when updating the value of a text field, but it does not seem to work when trying to update the value of a Single Selection field. I've also seen examples of the SetValue method working for Binary/Trinary fields, where a 0 or 1 is passed in instead of a String as the input parameter. For a Single Selection field, do I need to pass something in other than a plain string? Can I use some type of integer to reflect the value's position in the dropdown?
The Code:
Option Explicit
Dim objItem, objFields, objField, NewValue
Set objItem = Shell.Item
Set objFields = objItem.Fields()
Set objField = objFields.FindField("CODE_REVIEW_COMPLETE3")
NewValue = "Yes"
call objfield.SetValue("NewValue")

Any help would be greatly appreciated!!!
Thanks...
Joe

[forumadmin]

Comment posted 4/15/09 by Dana Sanders

If I understand correctly, you should be able to get a number value for yes/no from your TS_Selections table. Then you can set the single selection field to that integer value instead of the string value.

[forumadmin]

Comment posted 4/20/09 by Oliver Kraus

I did something similar with "Item Type" (which is a single selection)
without having tested the code it looks like this:
' Set single selection to the value of variable "string"
Set fldList = Shell.item.Fields()
' get the target single selection
Set siselfldObj = fldList.FindField("Item Type")
' Get a list of valid items of that selection list
Set siselRecList = siselfldObj.GetSelectionList()
' Loop through these items and...
For Each siselRec In siselRecList
' ... get the Name for each item
siselName = siselRec.GetName()
' Check if the name matches
If Not StrComp(string, siselName) Then
' If yes, assign the corresponding TS_ID to the selection field
Call Shell.Item.SetFieldValue( "Item Type", siselRec.GetId() )
is_found = True
End If
Next

[jimmy_lewis]

Hello Joe.

To answer your question, and backup Dana's reply, the answer is yes, you definitely have to use the internal database value for the single selection field.

The logic might look like this:

Option Explicit

Dim objItem, objFields, objField, NewValue

Set objItem = Shell.Item

Set objFields = objItem.Fields()

Set objField = objFields.FindField("CODE_REVIEW_COMPLETE3")

objfield.SetValue(1)

***

Yeah, it's that simple, with whatever conditional logic you might need, of course.

But the hitch is using the correct integer value. I'm certain you don't want to hardcode it.

In Oliver's example, he used the GetId() method to set the value. But, if I undertand correctly, GetId() returns the TS_ID.

Well, to use SetValue() on a selection field, you must have the TS_VALUE (from the TS_SELECTIONS table), which is different from the TS_ID. Check that table and you'll see immediately what I mean.

Which brings me to my question (and the whole reason I'm on the community page today). How do you get the TS_VALUE for each possble selection, and then use it to set the field value for your current record?

Oliver's use of GetSelectionList() seems to be the right direction. But I think something other than GetId() is needed to get the TS_VALUE. (I'm not certain yet if GetFieldValue() is the correct solution or not.)

Whatever that is, the TS_VALUE can then be used with SetValue().

[jeff_malin]

I wrote this as a generic "Get the correct internal selection ID for any given field value" function. Pass it the value you want to set, the field DBName, and the table name in which you're working, and it returns the correct ID from the TS_SELECTIONS table.
function getSelectionID(selectVal, fieldName, tableName)
dim selectRec, where
set selectRec = ext.createAppRecord(ext.TableID("TS_SELECTIONS"))
where = "TS_FLDID = (select ts_id from ts_fields where ts_dbname = '" & fieldName & "' and ts_tableid = " & ext.tableID(tableName) & ")"
where = where & " AND TS_NAME = '" & selectVal & "'"
if selectRec.readWithWhere(where) then
getSelectionID = selectRec.getID()
else
call ext.logInfoMsg("getSelectionID - no selection found, where = " & where)
getSelectionID = 0
end if
end function

[weissmanjr]

When I try Jeff's code as part of a post transition, it tells me,whatever table name I use, that the TS_ID is 0, and thus fails.

I am trying to clone an item from another item, so the table I want is the primary table for the app. Presumably the name is "TS_PRCR" if the table name shown to me is "PRCR", right?

Any ideas?

[jeff_malin]

Table names usually start with USR_ (for primary tables) or U_ (for Aux), though they may have other prefixes depending on if you started from one of the out-of-the-box solutions. Have a look in the Application Administrator at the table, it should show you under the properties. Or do a select * from ts_tables in the database, and look at the DBNAME column.

[weissmanjr]

Thanks, that was it. I am just beginning to script Serena, and am obviously over my head.

[weissmanjr]

Of course why you should have to look in App Admin instead of composer to see the db table name is beyond me.

[jeff_malin]

Actually I misspoke, it looks like you can see this under the table properties in Composer ("Database Table Name") as well.

[weissmanjr]

Sorta. The Database Table Name is "PRCR", and I had no idea that I had to prepend the Prefix. Now I know.

[pbustin]

Jeff, here's your template plugged into my sbmscript, with two validate errors I can't resolve:

' Copy_User_Dept_to_Bus_Unit

' Pre-transition in Submit TS Security Incident

'***************************************************************************

'* Copy User Dept to Business Unit *

'***************************************************************************

'

'***************************************************************************

'* Find the item being transitioned. *

'***************************************************************************

If Ext.ShellHasProp( "Item" ) Then

CopyDept

Else

' There is no current item, so write a message to the event viewer

Call Ext.LogErrorMsg( "TeamScript error: Shell.Item does not exist." )

End If

'***************************************************************************

'* Read and write field. *

'***************************************************************************

Sub CopyDept()

Dim selectVal, BusUnitValue

QUOTE = Chr( 34 ) ' the only way to get a quote in VBScript

'***************************************************************************

'* Read User Dept *

'***************************************************************************

set objField = Shell.Item.Fields().Findfield("User Dept")

objField.GetDisplayValue selectVal

Call Ext.LogInfoMsg( "User Dept = " & selectVal )

'***************************************************************************

'* Write Business Unit. *

'***************************************************************************

function getSelectionID(selectVal, BUSINESS_UNIT, UBR_BADGIR)

dim selectRec, where

set selectRec = ext.createAppRecord(ext.TableID("TS_SELECTIONS"))

where = "TS_FLDID = (select ts_id from ts_fields where ts_dbname = '" & BUSINESS_UNIT & "' and ts_tableid = " & ext.tableID(UBR_BADGIR) & ")"

where = where & " AND TS_NAME = '" & selectVal & "'"

if selectRec.readWithWhere(where) then

getSelectionID = selectRec.getID()

else

call ext.logInfoMsg("getSelectionID - no selection found, where = " & where)

getSelectionID = 0

end if

end function

If Not Shell.Item.SetFieldValue( "BUSINESS_UNIT", getSelectionID ) Then

Call Ext.LogErrorMsg( "Cannot write to Business Unit" )

Exit Sub

End If

set objField = Shell.Item.Fields().Findfield("BUSINESS_UNIT")

objField.GetDisplayValue BusUnitValue

Call Ext.LogInfoMsg( "Business Unit = " & BusUnitValue )

End Sub

[pbustin]

Jeff, below is your template plugged into my sbmscript, followed by two validate errors I can't resolve (and yes, I'm happy to replace varous statements with better-constructed ones if you don't mind repeating [from earlier posts] you suggestions): ' Copy_User_Dept_to_Bus_Unit

' Pre-transition in Submit TS Security Incident

'***************************************************************************
'* Copy User Dept to Business Unit *
'***************************************************************************
'
'***************************************************************************
'* Find the item being transitioned. *
'***************************************************************************
If Ext.ShellHasProp( "Item" ) Then CopyDept
Else
' There is no current item, so write a message to the event viewer
Call Ext.LogErrorMsg( "TeamScript error: Shell.Item does not exist." )
End If

***************************************************************************
'* Read and write field. *
'***************************************************************************
Sub CopyDept()Dim selectVal, BusUnitValue'***************************************************************************
'* Read User Dept *
'***************************************************************************
set objField = Shell.Item.Fields().Findfield("User Dept")
objField.GetDisplayValue selectValCall Ext.LogInfoMsg( "User Dept = " & selectVal )

'***************************************************************************
'* Write Business Unit. *
'***************************************************************************
function getSelectionID(selectVal, BUSINESS_UNIT, UBR_BADGIR)dim selectRec, whereset selectRec = ext.createAppRecord(ext.TableID("TS_SELECTIONS"))
where = "TS_FLDID = (select ts_id from ts_fields where ts_dbname = '" & BUSINESS_UNIT & "' and ts_tableid = " & ext.tableID(UBR_BADGIR) & ")"where = where & " AND TS_NAME = '" & selectVal & "'" if selectRec.readWithWhere(where) then getSelectionID = selectRec.getID()else
call ext.logInfoMsg("getSelectionID - no selection found, where = " & where)
getSelectionID = 0end ifend functionIf Not Shell.Item.SetFieldValue( "BUSINESS_UNIT", getSelectionID ) Then Call Ext.LogErrorMsg( "Cannot write to Business Unit" ) Exit Sub
End Ifset objField = Shell.Item.Fields().Findfield("BUSINESS_UNIT")objField.GetDisplayValue BusUnitValueCall Ext.LogInfoMsg( "Business Unit = " & BusUnitValue )
End Sub Validate result:INVALID SCRIPT "Copy_User_Dept_to_Bus_Unit" (id=0) 2 errors during parsing -------------------- Syntax error -- ERR #25 Line 38> function getSelectionID(selectVal, BUSINESS_UNIT, UBR_BADGIR) -------------------- Syntax error -- ERR #25 Line 49> end function

[pbustin]

I'm seeing the following in the event viewer, with the script below:Event viewer displaysUser Dept = IT getSelectionID - no selection found, where = TS_FLDID = (select ts_id from ts_fields where ts_dbname = '' and ts_tableid = 0) AND TS_NAME = 'IT' Script' Copy_User_Dept_to_Bus_Unit' Pre-transition in Submit TS Security Incident'***************************************************************************'* Copy User Dept to Business Unit *'***************************************************************************' '***************************************************************************'* Find the item being transitioned. *'***************************************************************************If Ext.ShellHasProp( "Item" ) Then CopyDeptElse' There is no current item, so write a message to the event viewer Call Ext.LogErrorMsg( "TeamScript error: Shell.Item does not exist." )End If'***************************************************************************'* Read and write field. *'***************************************************************************Sub CopyDept() Dim deptValue, BusUnitValueQUOTE = Chr( 34 ) ' the only way to get a quote in VBScript'***************************************************************************'* Read User Dept *'***************************************************************************set objField = Shell.Item.Fields().Findfield("User Dept")objField.GetDisplayValue deptValueCall Ext.LogInfoMsg( "User Dept = " & deptValue )'***************************************************************************'* Write Business Unit. *'***************************************************************************call getSelectionID ( deptValue, BUSINESS_UNIT, UBR_BADGIR )If Not Shell.Item.SetFieldValue( "BUSINESS_UNIT", getSelectionID ) Then Call Ext.LogErrorMsg( "Cannot write to Business Unit" ) Exit SubEnd Ifset objField = Shell.Item.Fields().Findfield("BUSINESS_UNIT")objField.GetDisplayValue BusUnitValueCall Ext.LogInfoMsg( "Business Unit = " & BusUnitValue )End Subfunction getSelectionID ( selectVal, fieldName, tableName )dim selectRec, whereset selectRec = ext.createAppRecord(ext.TableID("TS_SELECTIONS"))where = "TS_FLDID = (select ts_id from ts_fields where ts_dbname = '" & fieldName & "' and ts_tableid = " & ext.tableID(tableName) & ")"where = where & " AND TS_NAME = '" & selectVal & "'"if selectRec.readWithWhere(where) then getSelectionID = selectRec.getID()else call ext.logInfoMsg("getSelectionID - no selection found, where = " & where) getSelectionID = 0end ifend function

[pbustin]

With one parameter to pass (the other two are hard-coded), I get a run-time error of the wrong number of arguments:RUNTIME ERROR IN SCRIPT "Copy_User_Dept_to_Bus_Unit 2" (id=266) (called
from pre-transition context) Wrong number of arguments -- ERR
#7 Line 44> End Iffunction:function getSelectionID ( selectVal )dim selectRec, whereset selectRec = ext.createAppRecord(ext.TableID("TS_SELECTIONS"))where = "TS_FLDID = (select ts_id from ts_fields where ts_dbname = 'BUSINESS_UNIT' and ts_tableid = '1003')"where = where & " AND TS_NAME = '" & selectVal & "'"if selectRec.readWithWhere(where) then getSelectionID = selectRec.getID()else call ext.logInfoMsg("getSelectionID - no selection found, where = " & where) getSelectionID = 0end ifend function