Relational Field lookup?

Hello All,

I've got a change management workflow I'm working on. I'm creating a HTML formatted Audit log on the change. However, I want to create an automated rollback of these changes I imagine this being a "quick" transition so my intent is to use AppScript. I'm able to successfully write text/numeric fields, but fields that are stored in the Database as an ID I'm stuck with such as a Relational field. Does anyone have any ideas on how I might be able to take a display name and display value of a field and lookup the associated TS_IDs and to that effect be able to check via the script if the field is a relational field or single selection that would require me to perform this check and set the value using the TS_ID? Below is my current AppScript along with a copy of an Change Request summary where OS version is a relational field stored in the DB using the relational field items TS_ID.


HTML Summary:
Change Request: 121465
Field NameOriginal Value(s):New Value(s):
OS versionServer 2008 R2Server 2012 R2
RAM1612






AppScript:

' ---------------------------------------



Option Explicit ' Require all variables to be declared before use

' ----------------------------------------
'
' ----------------------------------------
Dim rollbackSrc, fldNameDest, fldSearchStr, closeStr
rollbackSrc = "Change Request Summary"
fldNameDest = "Internal Work Notes"
fldSearchStr = "
"
closeStr = "
"

If Ext.ShellHasProp( "Item" ) Then ' Find the item being transitioned:
call tableParser(rollbackSrc, fldNameDest, fldSearchStr, closeStr) ' Item exists, call rollback
Else
Call Ext.LogErrorMsg( "TeamScript error: Shell.Item does not exist." ) ' There is no current item, so write a message to the event viewer
End If
' ----------------------------------------

' ----------------------------------------
' Sub to parse through Change Request Summary
' ----------------------------------------
Sub tableParser(inputFld, destination, fldSearch, closeStr)

Dim QUOTE, fldList, chngSumFld, inputStr 'Declare variables for Sub
QUOTE = Chr( 34 ) 'the only way to get a quote in VBScript

set fldList = Shell.Item.Fields()
set chngSumFld = fldList.FindField(inputFld)

call chngSumFld.GetDisplayValue (inputStr) 'Get the value of the Change Request Summary field

If inputStr = "" then
Exit Sub 'Change Request Summary blank, exit script
Else
Dim intCursor, subStr, lnStart, lnEnd, wrkNotesStr, fldNameStr
intCursor = 1

Do
Call Ext.LogInfoMsg("fldSearch Value: "
  • If you included the field's TS_ID within your audit log, that might make things easier.
  • Just an update for anyone interested. I realized the other night that we're going to want to require a note to explain the reasoning for rollback, that said I started going through the process of creating a Javascript to accommodate parsing through the Change Request Summary. However, I realized that regardless of language I might be SOL as at least one of the fields that could possibly change is a Sub-Relational field. To my knowledge all methods of interacting with fields are not supported with Sub-relational fields.... Unless someone tells me otherwise




    Lynn Mattie wrote:

    If you included the field's TS_ID within your audit log, that might make things easier.


    I had considered doing this, but I thought it might be too "ugly" for the end user; in the end if that solves my issue I have no problem doing it.
  • Sub-relational fields should not be an issue, since their value comes from the relational field. If you roll back the relational field to the original value, the sub-relational fields will change automatically as well.
  • Lynn Mattie wrote:

    Sub-relational fields should not be an issue, since their value comes from the relational field. If you roll back the relational field to the original value, the sub-relational fields will change automatically as well.


    Hmm I'm not sure how that would work for me in this scenario...

    OS is the relational field which has a dependent OS Version field. In this case the OS would be windows, the OS version would be Server 2008 R2 or Server 2012 R2;


    So I have a 1 to many relationship with the parent relational field and the sub-relational field; Meaning that if I rolledback from Windows to Windows I still have to differentiate between the two sub-relational fields correct?
  • You're describing dependent fields, not sub-relational fields. You shouldn't have any problem setting dependent fields, just make sure you set the field they depend upon first.
  • Hmm... double checked, you're right they're just dependent single relational fields (Sorry been fighting this process app for the past month... going a bit crazy from working on it :P ).

    So assuming the solution to my issue would be including the TS_ID Values of the Single Relational selections (e.g. TS_ID 1, 2; where 1 would be "Server 2008 R2" and 2 would be "Server 2012 R2", I'm still stuck on how to get these values as the change Summary table is created via a Javascript which to my knowledge can only pull the Display value, correct?

    -Thanks
    Curtis
  • It is possible to get the internal value, but there is no handy JavaScript API for doing that. It might be easier to write your table with AppScript. Is there any reason you can't just use the Change Action and Change History records?
  • I might be able to use the Change History records if I could figure out how to access them via AppScript. Mainly the issue is about visibility for management. We want to have a quick-at-a-glance view of what's changes for a specific asset, but we don't necessarily care about every field, only asset specific ones (i.e. we don't care about time worked, worknotes, etc in this at-a-glance view)