I am having an error with my filter with other people located in separate time zones. It was recommended to me to switch to filtering with a UTC date instead of an ISO date and that should fix the issue.
Below is an example of my working filter function:
function openEditForThisUserThisWeek() {
// we already know we can get the current user ID
var userTitle = _spPageContextInfo.userDisplayName;
// forget about moment, you can do whatever you need to do
// with native javascript Date objects
var today = new Date();
// setting hours, minutes, seconds and milliseconds
// to zero essentially sets it to "midnight"
//today.setHours(0,0,0,0);
today.toISOString().slice(0, 10) + "T00:00:00.000Z"
// get monday of this week (at midnight)
// NOTE - as mentioned, if this does not accurately
// account for time zone offset, you might need to
// change this to sunday by changing the "1" to "0"
var monday = new Date(today);
monday.setDate(today.getDate() + (1 - today.getDay()));
// get friday of this week (at midnight - accounts for all of friday)
var friday = new Date(today);
friday.setDate(today.getDate() + (5 - today.getDay()));
// in order to make the REST query, we need ISO strings
var mondayISO = monday.toISOString();
var fridayISO = friday.toISOString();
// when building the filter, for comparison operators,
// "eq" means "equals", "ge" means "greater than or equal to",
// and "le" means "less than or equal to"
var str = fixedEncodeURIComponent(userTitle);
var requestUri = _spPageContextInfo.webAbsoluteUrl + "/_api/web/Lists/getByTitle('Report')/Items?$\
filter=User/Title eq '" + str + "'&$\
select=*";
function fixedEncodeURIComponent(src) {
return encodeURIComponent(src).replace(/[']/g, function (c) {
return '%' + c.charCodeAt(0).toString(16) + '%' + c.charCodeAt(0).toString(16);
});
}
var filter = "$filter=User/Title eq '" + str + "' and Monday ge datetime'" + mondayISO + "' and Friday1 le datetime'" + fridayISO + "'";
console.log(mondayISO);
console.log(fridayISO);
// use the filter as part of the overall query URL
var queryUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Report')/items?" + filter;
console.log(queryUrl);
// don't bother with the "success" parameter, just chain it
$.ajax({
url: queryUrl,
method: 'GET',
headers: {
accept: 'application/json;odata=verbose'
}
}).done(function(response) {
if (response.d.results.length > 0) {
// keep in mind that i am only checking that the
// filter found more than zero results - more than zero
// could be _one_, which is what we are expecting, OR
// it could be MORE THAN ONE, which is _not_ what we are
// expecting, but i'm not caring about that here. if you care
// that there is more than one item for the current user for this
// week, then you will need to add additional code here to deal with
// that eventuality. but here i am just going to take
// the first result and run with it.
var reportEntry = response.d.results[0];
console.log(reportEntry);
openDialog('/sites/Projects/Report/Lists/Report/EditForm.aspx?Id=' + reportEntry.ID);
} else {
// the length of the response array
// must have been zero, which means nothing
// matched the filter parameters, which in turn
// means nothing was found for the current
// user for this week
alert('No Report Item was found for the current user for this week.');
}
}).fail(function (jqXhr, code, err) {
// something went wrong with the GET request
console.log(err);
console.log(code);
console.log(jqXhr);
});
}
When I console log the queryUrl, it is successfull and the following is logged:
eq 'User%20Name' and Monday ge datetime'2022-03-07T05:00:00.000Z' and Friday1 le datetime'2022-03-11T05:00:00.000Z'
When I change the mondayISO and fridayISO dates to the following and tack the filter on the end of my request url:
var mondayUTC = monday.toUTCString();
var fridayUTC = friday.toUTCString();
var filter = "$filter=User/Title eq '" + str + "' and Monday ge datetime'" + mondayUTC + "' and Friday1 le datetime'" + mondayUTC + "'";
I get the following error in log:
The expression \"User/Title eq 'User Name' and Monday ge datetime'Mon, 07 Mar 2022 05:00:00 GMT' and Friday1 le datetime'Fri, 11 Mar 2022 05:00:00 GMT'\" is not valid."
UPDATE (in regards to Denis answer and Dylan's comment)
I still get following alert No Report Item was found for the current user for this week
.
I changed the following as recommended (see original function for full edit):
var today = new Date();
today.toISOString().slice(0, 10) + "T00:00:00.000Z"
var monday = new Date(today);
monday.setDate(today.getDate() + (1 - today.getDay()));
var friday = new Date(today);
friday.setDate(today.getDate() + (5 - today.getDay()));
var mondayISO = monday.toISOString();
var fridayISO = friday.toISOString();
To add a bit more insight, the Monday and Friday1 columns it is filtering for based on those dates are Date & Time Columns with the following calculated values:
Monday: =(2-WEEKDAY(Today)+(Today))
Friday1: =(6-WEEKDAY(Today)+(Today))
UPDATE 03-31-2022
I came across the following article: Timezone Issues when working with dates in SharePoint's Rest Services
The issues discussed is very similar to that of mine, but the function referenced that solved the issue isn't explained well. Where does the self
come from? Also, would the input date in this case be the JavaScript generated date, or the date pulled from SharePoint? Or both?
self.toUtc = function(inputDate) {
var localTime = inputDate.getTime();
var localOffset = inputDate.getTimezoneOffset() * 60000;
var utc = localTime + localOffset;
var retval = new Date(utc);
debugger;
return retval;
};
Monday: "2022-03-07T05:00:00Z" and Friday1: "2022-03-11T05:00:00Z"
atleast that is what it is showing for me. I am wondering, if because his local isT05:00:00Z
if I can set them to 0 through the SharePoint column if that would fix it, just not sure how to do sodatetime'2022-03-07T05:00:00.000Z'
which is also a 5 hour offset. So... maybe the dates are not the part of the filter settings that is making the query not find anything?User/Title
. But it doesn't look like you are$expand
ing theUser
field. To filter by lookup properties you also have to expand the field, so try adding&$expand=User
. BUT - keep in mind that to use$expand=User
you are also probably going to have to add a specific$select=User/Title
, which then in turn means you are going to have to explicitly$select
any other fields you want to retrieve.