How to query date/time field?

manimozhi3
Tera Contributor

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

5 REPLIES 5

Jon Barnes
Kilo Sage

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.


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')


find_real_file.png


Hi Shishir,



Thanks for your reply



How to query the times also and how to convert it to string..



Thanks,


Manimozhi T


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