Hi,
I have a list with 18000+ items.
I have a view filtered to 4000, even if i make a filter to 10 items the report didn't show.
And the browser console reports about threshold limit issue.
Dashboards takes items from all list ? in my case it is setup to this filtered view.
Dear @ixxxl,
Please, open the CAML query and remove the OrderBy part of the query - 5000 row Error on Filtered View with less than 5000 rows
1 Like
i maked this, but still persist error.
System account can see report other users no
Dear @ixxxl,
Is the field you're filtering by indexed? It has to be indexed in order to work with large lists.
@Nikita_Kurguzov
Good day,
Yes, fields are indexed.
Is the report opened from user account?
Dear @ixxxl,
Yes, the data is always pulled dynamically by the current user and the charts are built in real time.
@Nikita_Kurguzov
Thank you.
Still have problem, when filter under 5000 items...
Dear @ixxxl,
What exactly is the problem? You need to understand how CAML filtering works - if your first filter condition returns over 5000 items, you cannot apply another filter condition or order by query, that's just not possible. You need to:
- Only apply one filter condition - this can return over 5000 items. Field must be indexed
- Place first filter which will return less than 5000 items, then apply another filter or order by query. Fields still need to be indexed
- Only apply one CAML filter or none at all, and then use Advanced section code to filter and order the items with JavaScript, there are no limits here - JavaScript is much more versatile
@Nikita_Kurguzov
it seems ,that view that i applied in report doesn't work. it takes all the list. list is more then 77k items.
caml
<View>
<ViewFields>
<FieldRef Name="ID"/>
<FieldRef Name="LinkTitle"/>
<FieldRef Name="Statutul_x0020_Cererii"/>
<FieldRef Name="Echip_x0103__x0020_de_x0020_supo"/>
<FieldRef Name="Specialist"/>
<FieldRef Name="Created"/>
<FieldRef Name="Aprecierea_x0020_specialistului"/>
</ViewFields>
<Query>
<GroupBy Collapse="TRUE" GroupLimit="30">
<FieldRef Name="Statutul_x0020_Cererii" Ascending="FALSE"/>
</GroupBy>
<Where>{Filter}</Where>
</Query>
<Aggregations Value="Off"/>
<RowLimit Paged="TRUE">4999</RowLimit>
</View>
advanced
var handlers = {};
handlers.init = function(data, logger) {
data.items = [
{value: 0, finger: 'Thumb', red: 0, green: 100},
{value: 100, finger: 'Index', red: 160, green: 100},
{value: 160, finger: 'Middle', red: 80, green: 100},
{value: 100, finger: 'Ring', red: 60, green: 100},
{value: 80, finger: 'Pinky', red: 40, green: 100}
];
logger.debug('Data is initialized: ', data);
return true;
}
handlers.requestInit = function (query, logger) {
var hash = location.hash.substr(1);
var echipa = "";
var luna="";
var specialisti="";
var idxLuna = hash.indexOf('luna=');
var idxEchipa = hash.indexOf('&echipa=');
var idxSpecialisti = hash.indexOf('&specialisti=');
var idxAngajat = hash.indexOf('&angajat=');
if (idxLuna !== -1) {
//lungimea expresiei 'luna=' este egala cu 5
//lungimea expresiei '&echipa=' este egala cu 8
//lungimea expresiei '&specialisti=' este egala cu 13
if (idxEchipa !== -1) {
if (idxSpecialisti !== -1) {
luna=hash.substr(5,idxEchipa-5);
echipa=hash.substr(idxEchipa+8,idxSpecialisti-idxEchipa-8);
if (idxAngajat !== -1) {
specialisti=hash.substr(idxSpecialisti+13,idxAngajat-idxSpecialisti-13);
} else {
specialisti=hash.substr(idxSpecialisti+13);
}//if ang
}//if spec
//lungimea expresiei 'echipa=' este egala cu 7
echipa =echipa.replace(/%20/g," ");
// console.log("SpecPana="+specialisti);
specialisti =specialisti.replace(/%20/g," ");
//console.log("SpecDupa="+specialisti);
}}//if-uri
var view = query.get_viewXml();
if (luna != "" && specialisti!="" && specialisti!="null") {
//creez filtru CAML destinat specialistilor din grupa
var arraySpec = specialisti.split(";");
var FilterSpecialisti='';
var s='';
/* for (var i=0;i<arraySpec.length;i++){
s='<Eq><FieldRef Name="Specialist"/><Value Type="User">'+arraySpec[i]+'</Value></Eq>';
if (i==0){
FilterSpecialisti=s;
} else {
FilterSpecialisti='<Or>'+FilterSpecialisti+s+'</Or>';
}//if
}//for
*/
var d=new Date();
d.setMonth(d.getMonth() - 17+parseInt(luna));
var lunaNr=d.getMonth();
lunaNr++;
var dela="";
dela=d.getFullYear()+"-"+lunaNr;
d.setMonth(d.getMonth() +1);
lunaNr=d.getMonth();
lunaNr++;
var panala="";
panala=d.getFullYear()+"-"+lunaNr;
for (var i=0;i<arraySpec.length;i++){
FilterSpecialisti='<And><Eq><FieldRef Name="Specialist"/><Value Type="User">'+arraySpec[i]+'</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">'+dela+'-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">'+panala+'-01T00:00:00Z</Value></Lt></And></And>';
if (i==0){
s=FilterSpecialisti;
} else {
s='<Or>'+s+FilterSpecialisti+'</Or>';
}//if
}//for
var filtru='';
//filtru='<And><And>'+FilterSpecialisti+'<Geq><FieldRef Name="Created"/><Value Type="DateTime">'+dela+'-01T00:00:00Z</Value></Geq></And><Lt><FieldRef Name="Created"/><Value Type="DateTime">'+panala+'-01T00:00:00Z</Value></Lt></And>';
//filtru='<Or><Or><Or><Or><And><Eq><FieldRef Name="Specialist"/><Value Type="User">Viorica V. Mascov</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">2021-5-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">2021-6-01T00:00:00Z</Value></Lt></And></And><And><Eq><FieldRef Name="Specialist"/><Value Type="User">Ion G.Buclis</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">2021-5-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">2021-6-01T00:00:00Z</Value></Lt></And></And></Or><And><Eq><FieldRef Name="Specialist"/><Value Type="User">Mariana M.Verlan</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">2021-5-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">2021-6-01T00:00:00Z</Value></Lt></And></And></Or><And><Eq><FieldRef Name="Specialist"/><Value Type="User">Alina Parvan</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">2021-5-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">2021-6-01T00:00:00Z</Value></Lt></And></And></Or><And><Eq><FieldRef Name="Specialist"/><Value Type="User">Natalia Frunza</Value></Eq><And><Geq><FieldRef Name="Created"/><Value Type="DateTime">2021-5-01T00:00:00Z</Value></Geq><Lt><FieldRef Name="Created"/><Value Type="DateTime">2021-6-01T00:00:00Z</Value></Lt></And></And></Or>';
filtru=s;
view = view.replace('{Filter}', filtru);
console.log("Filtru=" + filtru);
} else {
view = view.replace('{Filter}','<Eq><FieldRef Name="ID"/><Value Type="Counter">1</Value></Eq>');
}
query.set_viewXml(view);
return true;
}
handlers.requestSuccess = function(data, logger) {
for (var i=0; i<data.items.length; i++){
console.log("nr de itemi este="+data.items.length);
//verific daca este cea mai proaspata stare
if (data.items[i].Aprecierea_x0020_specialistului=='["5","5","5","5"]'){
data.items[i].Maxima="Apreciate Maxim";
} else {
data.items[i].Maxima="";
}//else
}//for
return true;
}
handlers.requestError = function(error, logger) {
return $.Deferred().reject(error);
}
handlers.aggregationSuccess = function(data, logger) {
return true;
}
handlers.aggregationError = function(error, logger) {
return $.Deferred().reject(error);
}
handlers.finish = function (data, logger, processor, el) {
logger.debug('Data is processed: ', data);
return true;
}
Dear @ixxxl,
You have many CAML filter conditions, and most likely - they wouldn't work with that many items. You can switch to JavaScript filtering instead - so you don't filter what is requested from a list, instead, you request all the data and then apply filters to the data itself.
I can't write the filter for you, but you can check basic examples of sorting and filtering data items in our example article here - Configure aggregation and sorting
You can modify data.items and data.groups in Advanced section directly