I am trying to get lookup values from a different list. I have a column called Location with different values. I want to filter the list where location equals to the value that I put.
Here is my code which I am trying.
var BO = "BRANCH OFFICE";
var FB = "FB";
var user_list = pnp.sp.web.lists.getByTitle("Users");
user_list.items.filter("Location eq'" + BO + "' &&Location eq'" + FB + "'" ).get().then(function(items)
{
var uIds = items.map(function(i) { return i.ID });
if(uIds.length > 0)
{
var userfilterString = "Id ne " + uIds.join(" and Id ne ");
fd.field('Action_x0020_By').filter = userfilterString;
fd.field('Action_x0020_By').refresh();
}
});
This is only filtering the BO part not the other one and also If I swap the places of BO and FB it works for FB but still the lookup doesnt work correctly specially in filtering.
Kindly see the attach lookup image after applying the filter.
"Location eq'" + BO + "' and Location eq'" + FB + "'"
but its giving me empty result but if I change this to
or
I am getting only result of the first filter that is BO not the FB one. But as I mentioned even the first result is not working properly in the lookup specially while looking at the lookup values. It shows
Dear @Ramiz,
Please, check browser's console for errors. And I've just noticed that both filter conditions are for the same field, so it's definitely better to use or:
.filter("Location eq'" + BO + "' or Location eq'" + FB + "'" )
I have put the result in an alert msg and i have checked the console for errors as well. I do see errors there but I am not sure about what the error is actually. Kindly see the attached images.
I have noticed one more thing that I am not getting more than 100 records even for the first filter I have more than 100.
Dear @Ramiz,
I think I see what's going on. You're combining all of the IDs to make some sort of MEGA filter condition? I do not think this will work - there is a limit to how long the request can be, and if it's that huge, it will likely give a 404 error.
You can test this by limiting the number of conditions in this string to like 4-5 values:
Dear @Nikita_Kurguzov Yes. I have list of Users with around 500 records. My requirement is to filter this list based on location which I am trying.
As you said try with less values. I have another field in User list called Designation . I have put the filter on that with value that has only 2 records in the list and I am getting those records as well but the filter is not applying on the lookup instead its removing the 2 records which I have filtered and showing me everything else. Here is my code.
var BO = "BRANCH OFFICE";
var FB = "F5B";
var desg = "Manager";
var user_list = pnp.sp.web.lists.getByTitle("Users");
user_list.items.filter("Designation eq'" + desg + "'" ).get().then(function(items)
{
alert('User');
var uIds = items.map(function(i) { return i.ID });
alert(uIds);
if(uIds.length > 0)
{
var userfilterString = "Id ne " + uIds.join(" and Id ne ");
alert(userfilterString);
fd.field('Action_x0020_By').filter = userfilterString;
fd.field('Action_x0020_By').refresh();
}
});
Apart from these two ids I can see all other values in the lookup. I have the similar code on the same form and that is working fine.
Here is my working code on another lookup which I took from one of your blog.
var Response_Required = "Yes";
var list = pnp.sp.web.lists.getByTitle("Correspondence");
list.items.filter("ResponseRequired eq'" + Response_Required +"'" ).get().then(function(items)
{
var cIds = items.map(function(i) { return i.ID });
if(cIds.length > 0)
{
var filterString = "Id ne " + cIds.join(" and Id ne ");
fd.field('Response_x0020_To').filter = filterString;
fd.field('Response_x0020_To').refresh();
}
});
its removing the 2 records which I have filtered and showing me everything else
What is it supposed to do, if not this? You are specifically filtering out these two, by saying that the value should have ID ne (not equal) 1 and ID ne (not equal) 2...
If you want IDs to be equal, you need to have filter like this:
var filterString = "Id eq " + cIds.join(" or Id eq ");
@Nikita_Kurguzov my apologize about the last query. I am new to this syntax so I just copied the other code that I have and was trying but thanks for the help the designation one is working.
But how can I achieve the other one with multiple filter on single field. My main requirement is that to filter out based on Location. Can you help me on that?
Dear @Ramiz,
You need to design it in a way that avoids using that many conditions. Each one is short, but together they are very long, it's just not possible to send such a long request...
Why do you need to check for each ID individually anyway? Instead of this, think of how to group/filter by some other field or property, which is shared.
You are filtering Action By lookup field, correct? Does this lookup lead to this Users list?
If so, why don't you filter by location field directly, instead of first retrieving each user, getting their ID, and trying to filter by ID? It just doesn't make any sense.
Something like this should do the job:
var BO = "BRANCH OFFICE";
var FB = "FB";
function filterUsers() {
fd.field('ActionBy').filter = "Location eq'" + BO + "' or Location eq'" + FB + "'";
fd.field('ActionBy').refresh();
}
fd.spRendered(function() {
fd.field('ActionBy').ready().then(function() {
//filter ActionBy when form opens
filterUsers();
});
});
You just need to replace ActionBy with the Internal Name of the lookup column.
