Filter results by value of multiple choice field...

[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

[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";
}

[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?

[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 5694 times

Result.png (2.91 KiB) Viewed 5694 times

[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.

[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.

[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...

[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.

[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 5549 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 5549 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 5549 times

[dominique.beaudin]

the list of items to choose from has an id (contractorDriverID)

list 1.PNG (9.9 KiB) Viewed 5549 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 5549 times

selection list.PNG (23.45 KiB) Viewed 5549 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