Relationship between [sc_task] and its' variables

[tobrien]

Hi,

Can anyone offer a description of the relationship(s) between the tables I have listed?

sc_task

item_option_new

sc_item_option

My goal is to dig out the data presented in a Catalog Task which is the last STAGE of a SHIPPING REQUEST.   This task (as we have it built) has the SHIP_TO_LOCATION, and various DATES, etc.   I have been able to locate and see the data in this manner ==>

var iRec = new GlideRecord('item_option_new');

iRec.addQuery('cat_item', 'ed577dbd4f915200929cefd18110c76f'); //shipping req catalog item

iRec.addQuery('name', 'ship_to_location');

iRec.query();

But this returns ALL ROWS of Name = Ship_To_Location and I see no field in that table that relates it to the specific original REQUEST or its' TASKs

The image shows all of the REFERENCE fields in the "item_option_new" table.

The sc_item_option table looked promising, as I was able to find the 'value' field containing some of the data sought but, again, I cannot see any relationship back to a specific REQUEST or its' TASKs

I feel I am missing something *completely* obvious -- thanks to all replies!

tony

[Julian Hoch · Accepted solution]

A while back, I created the following diagram to visualize how the tables are related for variables. As you can see, you'll have to query Sc_item_variables_task or Sc_item_option_mtom to find the reverse relationship.

[tobrien]

Thanks You, Mr. Hoch -- this has helped Dramatically !

For all those interested, below you will find my example code which starts with a SERIAL NUMBER, and works its way to a REQUEST_ITEM which contains many of the interesting bits of info I am looking for about this serial number. And then further onto the REQUEST which gets me the SHIPPING DATE I have been after.

(Please excuse the rough paste, but I do not know how to make it look like "code" ;<>)

//Find this asset record

var sn = 'SFJC1937F2PN'; //testcase

var pRec = new GlideRecord('alm_asset');

pRec.addQuery('serial_number', sn);

pRec.query();

pRec.next()

gs.log('Asset Rec: ' + pRec.sys_id);

// we're looking for the variable named "hiddenAssetsItems" - a string of comma-separated [alm_asset] sys_ids.

//This variable is in a SHIPPING REQUEST

var iRec = new GlideRecord('item_option_new');

iRec.addQuery('cat_item', 'ed577dbd4f915200929cefd18110c76f'); //a shipping req catalog item type

iRec.addQuery('name', 'hiddenAssetItems'); //the HA 'variable.'

iRec.query();

iRec.next();

//Finds ONE Record

gs.log('item_option_new HA RecCount: ' + iRec.getRowCount());

var scItem = new GlideRecord('sc_item_option');

scItem.addQuery('value', 'CONTAINS', pRec.sys_id); //is our serial number in this text?

scItem.addQuery('item_option_new', iRec.sys_id); // and is in the HIDDENASSETITEMS variable?

scItem.query();

scItem.next();

//Finds ONE Record

gs.log('sc_item_option HA RecCount: ' + scItem.getRowCount());

var vRec = new GlideRecord('sc_item_option_mtom');

vRec.addQuery('sc_item_option', scItem.sys_id); //the sc_item_option record pointer

vRec.query();

vRec.next();

//Finds ONE Record

gs.log('sc_item_option_mtom recs pointing to HIDDENASSETITEMS: ' + vRec.getRowCount());

// These are all of the fields in the sc_req_item table

var rRec = new GlideRecord('sc_req_item');

rRec.get(vRec.request_item); //this particular Request_item

var fields = rRec.getFields();

for (var i = 0; i < fields.size(); i++) {

  var glideElement = fields.get(i);

  gs.log(glideElement.getName() + ' :: ' + rRec.getValue(glideElement.getName()) );

}

//now we can get to the SC_TASK

var tRec = new GlideRecord('sc_task');

tRec.addQuery('request_item', vRec.request_item);

tRec.query();

tRec.next();

//Finds 2 Records -- the 2 STAGEs of this workflow

gs.log('sc_task recs pointing to the REQ_ITEM: ' + tRec.getRowCount());

tRec.next();

gs.log('ShortDescription: ' + tRec.short_description);

//But we should get just the ONE STAGE we're after...

//where short_description = "Move Hardware To Shipping, Prepare for shipment, and Ship";

var tRec = new GlideRecord('sc_task');

tRec.addQuery('request_item', vRec.request_item);

tRec.addQuery('short_description', 'Move Hardware To Shipping, Prepare for shipment, and Ship');

tRec.query();

tRec.next();

//and all of its' fields including the SHIPPING DATE = [closed_at]

var fields = tRec.getFields();

for (var i = 0; i < fields.size(); i++) {

  var glideElement = fields.get(i);

  gs.log(glideElement.getName() + ' :: ' + tRec.getValue(glideElement.getName()) );

}

*** Script: Asset Rec: 6d1fc9164fb8e2003d18a6118110c784

*** Script: item_option_new HA RecCount: 1

*** Script: sc_item_option HA RecCount: 1

*** Script: sc_item_option_mtom recs pointing to HIDDENASSETITEMS: 1

*** Script: skills :: null

*** Script: assigned_to :: null

*** Script: urgency :: 3

*** Script: contract :: null

*** Script: price :: 0

*** Script: escalation :: 0

*** Script: state :: 3

*** Script: reassignment_count :: 0

*** Script: location :: null

*** Script: time_worked ::

*** Script: order :: null

*** Script: due_date :: 2016-09-12 00:00:00

*** Script: number :: RITM0010595

*** Script: billable :: 0

*** Script: upon_approval :: proceed

*** Script: configuration_item :: null

*** Script: sys_tags ::

*** Script: sla_due ::

*** Script: follow_up ::

*** Script: recurring_frequency :: null

*** Script: u_integer_items_available :: null

*** Script: sc_catalog :: null

*** Script: rejection_goto :: null

*** Script: assignment_group :: null

*** Script: request :: bc5881a0134ae600f94636722244b059

*** Script: received :: 0

*** Script: comments_and_work_notes ::

*** Script: opened_at :: 2016-09-20 14:30:04

*** Script: context :: 315881a0134ae600f94636722244b08d

*** Script: wf_activity :: null

*** Script: calendar_duration ::

*** Script: group_list :: null

*** Script: sourced :: 0

*** Script: comments ::

*** Script: u_product_quantity_json :: null

*** Script: priority :: 4

*** Script: sys_updated_by :: dave.keen@wachter.com

*** Script: recurring_price :: 0

*** Script: variables :: null

*** Script: delivery_task :: null

*** Script: backordered :: 0

*** Script: sys_updated_on :: 2016-12-05 12:20:52

*** Script: parent :: null

*** Script: quantity_sourced :: 0

*** Script: quantity :: 1

*** Script: active :: 0

*** Script: opened_by :: 33fdffa813f92600f94636722244b062

*** Script: expected_start ::

*** Script: watch_list :: null

*** Script: company :: 157b3fd74fd28200929cefd18110c79f

*** Script: upon_reject :: cancel

*** Script: work_notes ::

*** Script: sys_created_by :: alexander.drewke@wachter.com

*** Script: cmdb_ci :: null

*** Script: approval_set ::

*** Script: user_input ::

*** Script: sys_created_on :: 2016-09-20 14:30:04

*** Script: contact_type :: phone

*** Script: approval_history ::

*** Script: activity_due ::

*** Script: task_for :: null

*** Script: undefined :: null

*** Script: work_end ::

*** Script: closed_at :: 2016-12-05 12:20:52

*** Script: close_notes :: null

*** Script: variables :: null

*** Script: business_duration ::

*** Script: cat_item :: ed577dbd4f915200929cefd18110c76f

*** Script: knowledge :: 0

*** Script: approval :: requested

*** Script: sys_domain_path :: !!#/

*** Script: sys_mod_count :: 2

*** Script: estimated_delivery ::

*** Script: sys_domain :: 95725c834fa6c20064d523d18110c75f

*** Script: work_start ::

*** Script: correlation_id :: null

*** Script: sys_class_name :: sc_req_item

*** Script: stage :: complete

*** Script: u_integer_items_selected :: null

*** Script: short_description :: Shipping Request

*** Script: impact :: 3

*** Script: description :: null

*** Script: correlation_display :: null

*** Script: closed_by :: fcbc93684fb51200682b433e0210c799

*** Script: made_sla :: 1

*** Script: delivery_plan :: null

*** Script: work_notes_list :: null

*** Script: sc_task recs pointing to the REQ_ITEM: 2

*** Script: ShortDescription: Move Hardware To Shipping, Prepare for shipment, and Ship

*** Script: The particular SC_TASK that closes the Shipping Request ==>

*** Script: skills :: null

*** Script: closed_by :: fcbc93684fb51200682b433e0210c799

*** Script: assigned_to :: null

*** Script: contract :: null

*** Script: u_closure_code :: null

*** Script: state :: 3

*** Script: reassignment_count :: 0

*** Script: escalation :: 0

*** Script: location :: null

*** Script: time_worked ::

*** Script: order :: null

*** Script: u_closure_statement :: null

*** Script: due_date :: 2016-09-20 14:34:54

*** Script: number :: TASK0011655

*** Script: upon_approval :: proceed

*** Script: sys_tags ::

*** Script: sla_due ::

*** Script: follow_up ::

*** Script: u_integer_items_available :: null

*** Script: sc_catalog :: null

*** Script: rejection_goto :: null

*** Script: assignment_group :: 3831913b4fe7c20064d523d18110c741

*** Script: request :: bc5881a0134ae600f94636722244b059

*** Script: comments_and_work_notes ::

*** Script: opened_at :: 2016-09-20 14:34:54

*** Script: request_item :: fc5881a0134ae600f94636722244b059

*** Script: wf_activity :: b53371ea13512200f94636722244b00d

*** Script: calendar_duration ::

*** Script: group_list :: null

*** Script: comments ::

*** Script: priority :: 4

*** Script: sys_updated_by :: dave.keen@wachter.com

*** Script: variables :: null

*** Script: delivery_task :: null

*** Script: sys_updated_on :: 2016-12-05 12:20:50

*** Script: parent :: fc5881a0134ae600f94636722244b059

*** Script: active :: 0

*** Script: opened_by :: 33fdffa813f92600f94636722244b062

*** Script: expected_start :: 2016-09-20 14:34:54

*** Script: watch_list :: null

*** Script: company :: null

*** Script: upon_reject :: cancel

*** Script: work_notes ::

*** Script: sys_created_by :: alexander.drewke@wachter.com

*** Script: cmdb_ci :: null

*** Script: approval_set ::

*** Script: user_input ::

*** Script: sys_created_on :: 2016-09-20 14:34:54

*** Script: contact_type :: phone

*** Script: approval_history ::

*** Script: activity_due ::

*** Script: task_for :: null

*** Script: undefined :: null

*** Script: work_end :: 2016-12-05 12:20:50

*** Script: closed_at :: 2016-12-05 12:20:50

*** Script: close_notes :: null

*** Script: variables :: null

*** Script: business_duration ::

*** Script: knowledge :: 0

*** Script: approval :: not requested

*** Script: sys_mod_count :: 1

*** Script: sys_domain_path :: !!#/

*** Script: calendar_stc :: null

*** Script: sys_domain :: 95725c834fa6c20064d523d18110c75f

*** Script: work_start ::

*** Script: correlation_id :: null

*** Script: sys_class_name :: sc_task

*** Script: u_integer_items_selected :: null

*** Script: short_description :: Move Hardware To Shipping, Prepare for shipment, and Ship

*** Script: impact :: 3

*** Script: description :: Retrieve hardware from Stage and Config, Prepare the shipment, and Ship it !

*** Script: correlation_display :: null

*** Script: urgency :: 3

*** Script: made_sla :: 1

*** Script: delivery_plan :: null

*** Script: work_notes_list :: null

[0:00:00.522] Total Time