Exclude duplicate vaules from lookup

Forms SharePoint Online
1

Hi,
Apologies but I'm only just looking into cascading of lookup columns so just trying to get my head around it. I've a number of lookup columns to another list that I'll need to cascade between. The first column has many duplicate values. How can I exclude the duplicate values from the form?
Many thanks

2

Dear @flowy,
That's not really possible to do, unfortunately. With the lookup field, despite some records having similar value, their ID would actually be unique.

While it might be possible to filter the second field by value, not by ID, it won't be possible to just exclude values from first field, based on duplication. It's best to avoid duplicate values in the source list.

4

Thanks and apologies, my limited knowledge of JS has me slightly confused. I have the one list which needs to filter down through multiple columns based on selection. Some of the column values will obviously have the same value (i.e. same category with multiple products and so on). I'll dig a little deeper and see what I come up with..
Many thanks

5

Dear @flowy,
No, it's completely fine if you have multiple categories, each one with multiple products. It's not good, if you have a Category lookup which leads to Categories list, where you have like:

  1. Chocolate Bars
  2. Cereal
  3. Chocolate Bars
  4. Chocolate Bars
  5. Fruit

This repetition is dangerous, and it's impossible to filter out all items with the same name, but then you'll also have products:

  1. Mars - Chocolate Bars (ID:1)
  2. Snickers - Chocolate Bars (ID:3)
  3. Kit Kat - Chocolate Bars (ID:4)
    etc.

That's what is best to avoid. If this is not the case, then it should be fine! :blush:

1 Like
6

Hi Nikita,

Please can you provide an update to this request?

Is it now possible to incorporate a filter/unique method to the first lookup drop-down without modifying the source list?

I will elaborate my case if I sound a little confusing.

My auto-populate code is doing its job as expected, the only problem is when I select the hospital field, it pulls through every single value from the source list. I only have 11 hospitals which I want to display to the user.

How can I filter the hospital field so that it only shows a unique list of hospitals from the source list?

Thank you!

I am using this code to power my auto-populate functionality:

 //This code will auto-populate all the Org Hierarchy fields
 function populateFields(){
    //Get Hospital
    var OrgL3 = fd.field('Hospital').value.LookupValue;
   //get departments
    var OrgL7 = fd.field('Department').value.LookupValue;
    
  if (OrgL3 && OrgL7){
        var filter = encodeURIComponent("Title eq '" + OrgL3 + "' and Org_x0020_L7 eq '" + OrgL7 + "'");
      
      //filter list and get item values
     pnp.sp.web.lists.getByTitle('OrgHierarchy').items.filter(filter).get().then(function(item){
        fd.field('Speciality').value = item[0].Id;
        fd.field('Directorate').value = item[0].Id;
        fd.field('Division').value = item[0].Id;
        fd.field('Cost_Centre').value = item[0].Id;
       });
  }
    
 }
 fd.field('Department').$on('change', populateFields);
7

Hi Nikita,

please can you provide an update to this request?

8

Dear @DryChips,
There is no way to do it with a Lookup field still. What you can do is use a Common Dropdown field and populate its values from another list, like it's described here - Change drop down field data source — SharePoint forms

You can then retrieve items and filter them to only contain unique ones, like this:

function populateDepartmentCodes(){

    //specify your site URL
    var siteURL = 'https://sitename.sharepoint.com/sites/Main/';
    let web = new Web(siteURL);

    web.lists.getByTitle('Department Codes').items.select('Title').get().then(function(items) {
        var uniqueItems = [];
        items.forEach(function(item){
          var i = uniqueItems.findIndex(x => x.Title == item.Title);
          if(i <= -1){
            uniqueItems.push(item);
          }
        });

        fd.field('DropDown1').widget.setDataSource({
            data: uniqueItems.map(function(i) { return i.Title })
        });

        //set the dropdown with the previously selected value
        fd.field('DropDown1').value = fd.field('DepartmentCode').value;
    });
}

fd.spRendered(function() {

    //call function on from load
    populateDepartmentCodes();

    //fill SharePoint field with the selected value
    fd.field('DropDown1').$on('change', function() {
        fd.field('DepartmentCode').value = fd.field('DropDown1').value;
    });
});
1 Like