How to query date/time field?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-10-2018 05:09 AM
I have two date/time fields, callback start date and end date. End date is populated with 2 hours after the start time. When more than 5 incidents with same start date and end date are submitted, it should thrown an alert saying that there are already 5 incidents with the same start date and end date. And also it should clear the start date and end date.
I have created an on change client script (onchange of end date) and script include and I've passed the start and end date values to the script include. I'm unable to query the start and end dates in the script include. I tried querying the incident table with start date and end date. It is not querying the values, since both are date/time fields.
I have tried the same by creating two drop down fields for start and end time and one date field for callback date. It was working fine that time. But based on the requirement, I have changed it into two date/time fields. I have attached the scripts for reference.
Please help with your suggestions.
Thanks in advance,
Manimozhi
- Labels:
-
Incident Management

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-10-2018 03:20 PM
Your query as it is written will only match records that have the exact same start and end date/times. I suspect that will rarely happen. Is that your intention? Or are you only wanting 5 created in the same window. I am having a hard time understanding a business case why you would want this, btw.
Maybe if you explain your use case, I can help you get the script right.
Also, there are some other things wrong with the script include. something like this would improve the logic, but your date query still needs to be addressed:
var gR = new GlideAggregate('incident');
gR.addAggregate('COUNT');
// change to GlideAggregate ^ for performance
gR.addActiveQuery();
// you can use addActiveQuery here ^
gs.log('inside incident table');
//gR.addQuery('u_call_back_date', days);
gR.addQuery('u_callback_start_date', starts);
gR.addQuery('u_callback_end_date', ends);
// we need to work on this part above because I don't think this is what you actually want ^
gR.query();
if(gR.next()) {
var count = gR.getAggregate('COUNT');
}
return count >= 5;
// this will now return a true or false, instead of yes or no.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-11-2018 06:52 PM
I agree with Jon to use GlideAggregate,
I was trying to form a encoded query to filter the record based upon, for example, opened_at date but it forms the query like below, but was not able to convert this query as a string. we might think to convert the below encoded query as a string and then use the same in glideaggregate query.
opened_atON2018-02-10@javascript:gs.dateGenerate('2018-02-10','start')@javascript:gs.dateGenerate('2018-02-10','end')
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-12-2018 04:59 AM
Hi Shishir,
Thanks for your reply
How to query the times also and how to convert it to string..
Thanks,
Manimozhi T
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-12-2018 04:56 AM
Hi Jon,
Thanks much for your help
Yes, According to my business case, it should match the same start and end date/times and it should restrict if the record count exceeds 5, when 6th record is submitted. Also, the above script is not working in this case. Please suggest me, if any changes can be done in querying the fields.
Thanks,
Manimozhi T