Page 1 of 2
Filter results by value of multiple choice field...
Posted: 09 Oct 2017
by pselman
Hi
We have a use case where we need to filter the results shown to only show Company Names where Company Type contains "Supplier".
Company Type is a multiple select choice field so results are returned as an array.
Thanks
Re: Filter results by value of multiple choice field...
Posted: 10 Oct 2017
by pselman
I seem to have got this working now, as follows;
/* Lookup Filtered by Field Value
* Lists items ordered by "Title" A-Z (Ascending Order)
* Returns Items unfiltered by search in Blocks of 50
* Returns Substring of Items filtered by search in blocks of 20 */
function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$Company_x0020_Type&$filter=substringof('Supplier',Company_x0020_Type)&$orderby=Title asc&$top=50";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField}&$orderby={LookupField}&$filter=substringof('" + encodeURIComponent(term) + "',{LookupField})&$top=20";
}
Re: Filter results by value of multiple choice field...
Posted: 10 Oct 2017
by pselman
The only issue with the above is that when a user types into the Supplier field they can get all Company names not just ones where Type = Supplier
Can you advise how to rectify this please?
Re: Filter results by value of multiple choice field...
Posted: 10 Oct 2017
by Nikita Kurguzov
Sadly, there is no way to configure Cross-site Lookup item request for Multiple Choice Fields and Lookups. Cross-site Lookup utilizes OData query to get results and it does not support multiple choice -
https://docs.microsoft.com/en-us/sharep ... -and-users
But I am not saying that there is no way to do it, because there is one. You'll need to include Company Type in select options of the request and then filter the results in Item Format.
So, for example, you need to have something like this in Request Items:
Code: Select all
function (term, page) {
if (!term || term.length == 0) {
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Company_x0020_Type&$orderby=Title asc&$top=10";
}
return "{WebUrl}/_api/web/lists('{ListId}')/items?$select=Id,{LookupField},Company_x0020_Type&$orderby={LookupField}&$filter=startswith({LookupField}, '" + encodeURIComponent(term) + "')&$top=10";
}
And something like this in Item Format:
Code: Select all
function(item) {
if(item["Company_x0020_Type"].results.includes("Supplier")){
return '<span class="csl-option">' + item["{LookupField}"] + '</span>'
}
}
Here is my Source list and final result:
- List.png (13.6 KiB) Viewed 5804 times
- Result.png (2.91 KiB) Viewed 5804 times
Re: Filter results by value of multiple choice field...
Posted: 10 Oct 2017
by pselman
Hi Nikita,
Thanks for the reply; is the solution above to solve users being able to type into the field and get only results where Company Type = Supplier?
It does seem to work as a drop down using the code I put above.
Re: Filter results by value of multiple choice field...
Posted: 10 Oct 2017
by Nikita Kurguzov
No, the users won't be able to select companies that do not match filtering criteria from the dropdown as they wouldn't show.
But! If the user types in full name of the company that does not match the criteria, the result will not show up in dropdown, but it still can be selected as it is already in the data. This is a drawback of this method. It's unlikely to happen, but if you want to be extra careful, it can be rectified.
If you include this code in JavaScript editor on the Form, it will reset the Lookup field each type somebody manually enters inappropriate company name:
Code: Select all
fd.field('Company').change(function(){
if(fd.field('Company').value() &&
!fd.field('Company').control('data')['Company_x0020_Type'].results.includes("Supplier"))
{
fd.field('Company').value('');
}
});
Don't forget to change the name of the Lookup to yours.
Re: Filter results by value of multiple choice field...
Posted: 06 Mar 2018
by dominique.beaudin
Can this be done using a filter from the list? For example, I have a dropdown list (drivers) that have an ID that is their connection to the parent list. For that drop down list I only want to show the drivers that match the parent. So instead of "supplier" above, I would want to use the parent list variable (ID). Is that possible? This is very close...
Re: Filter results by value of multiple choice field...
Posted: 07 Mar 2018
by Nikita Kurguzov
Dear Dominique,
That should definitely be possible if you are using Cross-site Lookup, but we'll need a little more information to help. It's a bit unclear to me, where the Dropdown is located, what do you mean by parent and how they are connected. A few screenshots will help, for example, the form itself, and the list where drivers are stored, so we can better understand the connection.
Re: Filter results by value of multiple choice field...
Posted: 07 Mar 2018
by dominique.beaudin
List 1: List with items to select (source) - this is a list of drivers.
the ContractorDriverID is the field that matches the driver to the main list
- list 1.PNG (9.9 KiB) Viewed 5659 times
List 2: the list where the users selects the driver, it needs to be filtered by the top ID of the form (which matches the ContractorDriverID)
- list 2.PNG (7.62 KiB) Viewed 5659 times
When a new piece of equipment is added/updated the "equipment driver" lookup list (it is a plumsail cross lookup) should only show the Drivers from list 1 where ContractorDriverid = top List id
Does that make sense?
- selection list.PNG (23.45 KiB) Viewed 5659 times
Re: Filter results by value of multiple choice field...
Posted: 07 Mar 2018
by dominique.beaudin
the list of items to choose from has an id (contractorDriverID)
- list 1.PNG (9.9 KiB) Viewed 5659 times
When a new item is added, I would like the list to be filtered based on the id in the list (equipment) it is being added to.
- list 2.PNG (7.62 KiB) Viewed 5659 times
- selection list.PNG (23.45 KiB) Viewed 5659 times
This list is a related list that is filtered by the top level list.
so if the top list id is 123 (customer list), the record that connects the driver will have an id of 123 (driver list) - When a piece of equipment is added/edited I would like to filter the list by the Customer List id so they only see drivers that belong to this customer