How to create entry in "cmn_schedule_span" table through script.

[RAHUL Khanna1]

Hello Guys, 

I am trying to create an entry in "cmn_schedule_span" table through script. But I am facing issue with the start date and end date . 

The dates are not getting properly populated (may be some issue with time zone), also all day is not setting to true .

Can any one of you guide me .

var holidayName = "Test-4";
var schedule = '0e14cbacdb9e27041727f1c51d96193a';
var type = 'exclude';
var show_as = 'busy';
var repeat = 'NULL_OVERRIDE';
var allDay = 'true';  // checked with/without codes
var date = "03-06-2018" // I am creating a date fied on catalog item this would be input for this field 

var gr = new GlideRecord('cmn_schedule_span');
gr.initialize();
gr.setValue('name', holidayName);
gr.setValue('schedule', schedule);
gr.setValue('type', type);
gr.setValue('show_as', show_as);
gr.setValue('all_day', allDay );
gr.setValue('repeat_type', repeat);
gr.start_date_time =date.toString();
gr.end_date_time = date.toString();
p = gr.insert();
gs.log( p); //  showing null most of the time 

[Community Alums]

Hi,

I feel the type of field is not matching for the date and gr.start_date_time , you can try the GlideDateTime API to populate that field.

The date should be in the below format before you assign that to gr.start_date_time

2015-01-01 00:00:00

Use below code to conver the format of date given by user:

var gDate = new GlideDate(); 
gDate.setValue('2015-01-01');
var gDT = new GlideDateTime(gDate); 
gs.info(gDT);


Regards,
Priyanka Soni

[RAHUL Khanna1]

Recently I found that it is a "schedule data range selector " formatter, so any approach for this 

[Etienne2]

Hi all,

Concious that this is a very late reply but in view that I could not find any on-line solutions, thought to post my solution which seens to work, at least for us.

So, the issue I had was with the dates. I tried different Glide date formats and although I could see the date variable having values, a validation BR was somehow finding the dates as blank. That indicates that the format the date being passed across is incorrect.

So what I did was pass the date value as pure text in the following format: 'yyyyMMddT000000'. 'T' being used as a delimiter - that inserted the dates correctly. Old dev school, pass the value as text and let the database/triggers handle the format.

If you are using timezones:

- If you are using timezones, the string format should be 'yyyyMMddT000000Z' (note the Z at the end of the string)

- If you are NOT using timezones use 'yyyyMMddT000000'. In my case adding the Z at the end resulted in changed timings.

Hope this helps.

[Claude D_]

I managed to create a BR that took separate Date fields and Time fields to combine into an acceptable format for my calendar. When I looked at the values, I did see that what Etienne posted is what is in the list view, but using an alert() to display the value via a Client Script for the cmn_schedule_span record displayed something else entirely.

alert() value displayed for start time = TZID=US/Central;2022-01-01 00:00:00

Since my timezone was set to US/Central, that's why mine returned the above value.

Below is an example BR excerpt just for the dates and times portion you are looking for help with. Just change the timezone ID to what is needed.

var startDate = current.start_date; //returned displayed value of Date field, no converting dates to UTC due to no time for Date field to use for conversion
var startTime = current.start_time.getDisplayValue(); //returned displayed value of Time field, removed date portion of value included without .getDisplayValue()
var endDate = current.end_date;
var endTime = current.end_time.getDisplayValue();

var start = 'TZID=US/Central;' + startDate + ' ' + startTime;
var end = 'TZID=US/Central;' + endDate + ' ' + endTime;

createSchedule(start,end);

function createSchedule(start,end){
var gr = new GlideRecord('cmn_schedule_span');
gr.initialize();
gr.start_date_time = start; //'TZID=US/Central;2022-01-01 00:00:00'
gr.end_date_time = end; //'TZID=US/Central;2022-01-01 08:00:00'
gr.insert();
}