Fetching two current object details

swapnil15 · ‎11-03-2023

Hello All,

I have a requirement to fetch the details from a field.

Table 'Task' records the time (time_worked) a user has worked on the CASE (CS0132374) and RITM (RITM0013737) as below:

The time worked field gets updated every time a user makes update on RITM/CASE (the time gets added automatically). I want to store the both the time a user has worked on RITM and CASE in two different variable so I can use those variables to process ahead as per requirement. I tried creating a BR with table as task. I am getting the value for Case every time using 'current' but not getting the value of RITM here. Unaware of what method to use so that I can get the value of both records in two different variables at the same time.

-O- · ‎11-06-2023

Based on what you ~~wrote~~ showed, I would create two business rules, one after on Case and an additional after on Requested Item;

The code would be:

(function executeRule (current, previous) {
	var caseUniqueValue = current.getUniqueValue(); // (Only!) In the case BR
	var caseUniqueValue = current.request.parent; // (Only!) In the requested item BR

	var totalTimeWorked = new GlideDuration(getTotalTimeWorked(caseUniqueValue));

	// Use totalTimeWorked to update the entitlement

	function getTotalTimeWorked (caseUniqueValue) {
		var $ga = new GlideAggregate('task');

		$ga.addAggregate('SUM', 'time_worked');

		// Only bother with records that have time recorded
		$ga.addNotNullQuery('time_worked')

		// Include the (parent) case
		$ga.addQuery('sys_id', caseUniqueValue)
			// Also include any requested items the (parent) case may have 
			.addOrCondition('ref_sc_req_item.request.parent', caseUniqueValue);

		$ga.setGroup(false);

		$ga._query();

		if ($ga._next())
			return $ga.getAggregate('SUM', 'time_worked');
		else
			return '0';
	}
})(current, previous);

Note that you would keep only one of the caseUniqueValue variable declarations only in both Business Rules - the one correct for the given table, of course.

Note also that this is lacking the code that updates the entitlement.

The code makes use of the fact that field time_worked is defined on table Task, the parent both of Case and Requested Item.

Thus the select can be executed against it and it will select both the Case and any Requested Items it may contain.

Also to make it optimal, it does an aggregation in database, returning the total already, not needing any resource intensive in-script processing.

View solution in original post

-O- · ‎11-04-2023

Could you share what (code) you have so far?

swapnil15 · ‎11-06-2023

Hello PFB code I used,

When to run: After Insert, Update. Table: sc_req_item

  gs.log("Current Record: " + current.number);
    gs.log('Unique value is: ' + current.getUniqueValue());

    var currentSysId = current.sys_id;

    var RITMRecord = new GlideRecord('sc_req_item');
    RITMRecord.addQuery('sys_id', currentSysId);
    RITMRecord.query();

    if (RITMRecord.next()) {
        timeWorked = RITMRecord.getValue('time_worked');
    } else {
        gs.error('Task record with sys_id ' + currentSysId + ' not found.');
    }

	gs.log('Time Worked: ' + timeWorked);

The logs current record and unique value is always printing the Case Number even though I am on requested item table. I changed the table to tasks and cases, still the logs prints the same multiple times. It is not finding any record after gliding as if and else loop logs are not printed along with time worked log. I reckon this is happening because the updates I am making on RITM are been pushed to the CASE.

PFA:

Aim is to calculate the time user spends of case and RITM. Add both the times, round it and subtract the resultant time form total time of entitlement.

My approach: Use GlideRecord on task table where I get both the records of RITM and CASE together. Use time_worked field to get the its value. Add that value, round up, convert to hours and finally subtract it from already defined 'total time' field on entitlement table.

Note: The further process of adding, rounding, converting and subtracting from total time can be achieved easily once the time worked on RITM and CASE is obtained in two different variables.

-O- · ‎11-06-2023

So if I understand correctly, you want to add up the time worked on a case and requested items for that case.

If so the 1st thing to do is to find out how the two are linked together.

If you look at the 2nd screen-shot you posted, the one that shows the case in a related list to the requested item, you will notice that the Cases related list is a "scripted relation".

In order to have the solution to your problem, you need to go to module "System Definition -> Relationships", find the record where Name is Cases and Table is sc_req_item and post the code in field Query with.

After you/we have that information, it becomes a simple select on table Task, a loop on the selected record, summing up existing duration and updating that on the entitlement table.

But the code in the relationship is needed.

-O- · ‎11-06-2023

Also you need 2 after business rules to solve the problem (running essentially the same code/logic):

- one after for the case table and

- one after for the requested item table.