Relationship between [sc_task] and its' variables

tobrien · ‎12-06-2016

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 · ‎12-08-2016

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.

View solution in original post

Chuck Tomasi · ‎12-06-2016

Hi Anthony,

If you already have the GlideRecord object for that task, then the easiest way is to use the format:

gliderecord.variables.variablename

For example:

var name = gr.variables.name; // get the value of the name variable from the gr object.

If you are doing this via an email script, say an approval, this bit of code may be helpful. (change the current.getValue('sysapproval') to the appropriate sys_id of the record you want.)

printVars();

function printVars(){

       var set = new GlideappVariablePoolQuestionSet();

       set.setRequestID(current.getValue('sysapproval'));

       set.load();

       var vs = set.getFlatQuestions();

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

               if(vs.get(i).getLabel() != '') {

                       template.print('       ' +   vs.get(i).getLabel() + " = " + vs.get(i).getDisplayValue() + "\n");

               }

       }

}

tobrien · ‎12-06-2016

Thanks, Mr. Tomasi ---

But in reality I am working "backwards" in the sense that I "know" some piece of data deep in the SNow -- like a serial number that I know was SHIPPED.

I can find an [item_option_new] record containing that serial number -- so *then* its the issue of finding the other data that was collected at the TASK or REQUEST levels of that serial number's shipment. It is this backward looking relationship that I cannot fathom.

I have used the gliderecord.variables.variablename mechanism in the past with great success BUT in this case I do not know a' priori which REQUEST or TASK record I am looking for.

A_Cadaret · ‎12-06-2016

Anthony,

You may need to add a field, either to the record created by the task, or to the task itself to build this relationship. For example, add a request field to the created record, or a "serial number" to the task itself. As far as I know, there's no way to do that oob

tobrien · ‎12-06-2016

Thanks, for the reply, Mr. Caderet --

So you're suggesting that the TASK record points to VARIABLE data (in the sc_item_option or item_option_new tables) but there is no reverse relationship? Too bad, really.

So how does the community do any sort of "asset life cycle" history? One doesn't normally START with the TASK(s), but you know the ASSET (like a serial number), and now you want to find out when it was purchased (proc_po & proc_po_line_item), if it was ever transferred out the original stockroom (alm_transfer_order & alm_transfer_order_line) transferred, and then if it was ever SHIPPED, and then ever returned (RMA)

These last 2 are where I get 'stuck' as they are CATALOG REQUESTS and TASKs. I can't see scouring the TASK table to then find the variables each record points to to see if maybe the VALUE I want is there ...

As I say, I think I am missing something obvious and silly.

tony