Importing Duration from Excel Spreadsheet

[Brian S1]

Hi Everyone,

I am attempting to import call duration from an Excel spreadsheet to a custom table within ServiceNow. I created the transform map to import the records once I load the data, but the duration column is populating as '(empty)'. After searching through many of the forums, I have found others have been able to successfully import data by utilizing the script 'target.duration.setDateNumericValue(source.u_duration*1000);' but I'm unsure where I need to place this within my transform map or if I should create a business rule. I'm very new to JavaScript so any help would be greatly appreciated.

Thank you,

Brian

[Abhishek Pidwa]

Hey Brian,

I can help you in that. You see that Run Script checkbox. Click on that and then inside the function paste the already javascript you have :

target.duration.setDateNumericValue(source.u_duration*1000);

That is it. YOu are done. Make sure that you have the correct name in target as well as source

Please mark this as helpful/correct if this solves your problem.

[Brian S1]

Hi Abhishek,

Thank you for your response. I've checked the 'Run script' checkbox and inserted target.u_callduration.setDateNumericValue(source.u_callduration*1000); into the script window. The duration values are now populating as '0 Seconds' rather than '(empty)' now, so I think it might just be a formatting issue within Excel. How should the duration be formatted within Excel? I've tried 'DD HH:MM:SS', 'HH:MM:SS', and 'SS'.

Thank you,

Brian

[Abhishek Pidwa]

Hey Brian , 

How do you want to calculate the duration field ?

Based on your description since this is the first insert, the javascript is just taking the source u_callduration and multiplying it with 1000. Since source u_callduration is 0 that is why you are getting 0 . Make the u_callduration in your excel to an integer (for e.g. 10 ) and then you should see 10000 in the target table. 

Please mark this as helpful/correct if this solves your problem.

[Abhishek Pidwa]

Ahhhh I see what you are doing exactly. Look at this example provided in the docs. 

Example

var inc = new GlideRecord('incident');
inc.get('17c90efb13418700cc36b1422244b05d');
var timems = inc.calendar_duration.dateNumericValue();
timems = timems + 11*1000; 
inc.calendar_duration.setDateNumericValue(timems)
gs.info(inc.calendar_duration.getValue());

Output

1970-01-01 00:01:38

just replace the variable names and also source and target accordingly. Get the dateNumericValue() from the source table and then add the duration accordingly. Finally assign the target duration to the new value. 

Please mark this as helpful/correct if this solves your problem.