Import data in Duration type field through Transform Map

[Abhradipa Baner]

Hi ,

I have a Duration type field where I want to import data from excel through Transform Map.

I have written below OnBefore script in Transform Map.

target.duration_day_hh_mm_ss=source.duration_day_hour_mm_ss;

IN excel, I have given below records --

Duration (Day:HH:MM:SS)

12:11:24:43

11-10-23-36

 But , while Importing , No records are getting inserted.

I am really not sure where exactly i need to change to get this working. 

I appreciate all your help.

Regards,

Abhradipa Banerjee

[palanikumar · Accepted solution]

Hi,

You can use this code to convert this:

var duration = source.duration_day_hour_mm_ss.toString();
duration = duration.replace(":"," "); // replace the first occurrence of colon to blank space
var gd = new GlideDuration(duration);
target.duration_day_hh_mm_s.setDateNumericValue(gd.getNumericValue());

[Abhradipa Baner]

Hi palanikumar,

It's is working properly. Thank you for your suggestion.

Regards,

Abhradipa Banerjee

[Abhradipa Baner]

Hi palanikumar,

It's is working for valid duration data ; but if we enter invalid duration data (ex. string field, 12-05-23-54 ) ,then random data is entered in table.

Please check the attachment for list view of random data.

So, I am really not sure where exactly I need to change to ignore invalid data and enter only valid data.

I appreciate all your help.

Regards,

Abhradipa Banerjee

[palanikumar]

Hi,

Your data should be in proper format. If you expect data in two different format then you can update your code as below:

Note: This works only if data is in either of dd-hh-mn-ss or dd:hh:mn:ss format

var duration = source.duration_day_hour_mm_ss.toString();
duration = duration.replaceAll("-",":"); // Replace all occurence of - with :
duration = duration.replace(":"," "); // replace the first occurrence of colon to blank space
var gd = new GlideDuration(duration);
target.duration_day_hh_mm_s.setDateNumericValue(gd.getNumericValue())