Filter Cascading Lookup

Forms SharePoint Online
1

Hi All,

I have 2 lookup fields, 1 is Unit and the other is Sales Person. Sales Person is dependent on Unit, i.e. it is a cascading drop down. This is working well, however I also need to filter Sales Person to only show when Active is Yes. Active is a field with a Yes/No field type.

So I added Active as Extra field and I am doing this code for the Active filtering part but it is not filtering

fd.field('SalesPerson').filter;
fd.field('SalesPerson').filter = "Active eq Yes";

Can you help me please?

2

Dear @Daniel_Privitelli,
For filtering in general, you can refer to this article - Configure cascading lookup fields on a SharePoint form with JavaScript — SharePoint forms

For filtering by Yes/No field, the following condition should work:

fd.field('SalesPerson').filter = "Active eq 1";
3

Hi @Nikita_Kurguzov

Thanks for your reply. However its not working at all.

As if nothing was applied. I do not know why.

4

Dear @Daniel_Privitelli,
Well, hard to say, are you doing it inside all the appropriate events and conditions? If you look into the article, you'll see that it needs to be inside ready(), at the very least:

fd.spRendered(function() {
    fd.field('SalesPerson').ready().then(function() {
      fd.field('SalesPerson').filter = "Active eq 1";
  }
});

If this doesn't work - check console for errors. Also, might need to double check internal names of the fields to make sure they match.

This might also conflict with the other filter that you already have, so I recommend to disable it for now, at least while we test it.

5

Thanks @Nikita_Kurguzov for the fast reply.

Inserted your coding in the spRendered function and disabled all other filters.... still to no avail, very weird because cascading works well but filtering on this Active field is turning out to be a nightmare :upside_down_face: :upside_down_face:

6

Dear @Daniel_Privitelli,
What about console errors - are there any on the form when you open the dropdown? Can you turn off cascading for now and test just the filter?

7

Hi @Nikita_Kurguzov, yes I have turned off the cascading.

I have tried this as well...

  fd.field('SalesPerson').ready().then(function() 
  {
   alert('Before');
   fd.field('SalesPerson').filter = "Active eq 1";
   alert('After');
  })

and the alerts both show.... there must be something in fd.field('SalesPerson').filter = "Active eq 1"; that is not functioning.

Regarding console.... when I press on drop down I get the following:

Error: Error: Idle tracking cookie is missing.
at IdleSessionActivityMonitor.GetCookieValue (activitymonitor.js:365)
at IdleSessionActivityMonitor.InitializeSettingsFromCookie (activitymonitor.js:349)
at LogIssoActivity (activitymonitor.js:130)
LogIssoActivity @ activitymonitor.js:137

8

Dear @Daniel_Privitelli,
Please, try adding the following:

fd.field('SalesPerson').ready().then(function() {
   alert('Before');
   fd.field('SalesPerson').filter = "Active eq 1";
   fd.field('SalesPerson').refresh();
   alert('After');
})

If it doesn't help - double check Internal names, especially for the Active field.

9

Hi @Nikita_Kurguzov.... so this worked like Charm, thanks for your help.........

However now I have a problem.... I re-applied the cascading. So assuming that you need to select a Bus Unit so that Sales Person names show, problem is that without selecting the Bus Unit, all ACTIVE Sales Person are showing. When then selecting the business unit.... the cascading is working but both Active and Inactive ones are showing....

10

Dear @Daniel,
The issue here is that the out of box filtering uses the same JS filter property, so it simply overwrites it. Instead, you'll need to configure lookup filtering, including cascading, with JavaScript - Configure cascading lookup fields on a SharePoint form with JavaScript — SharePoint forms

So, it would look somewhat like this:

function filterSalesPerson(user) {
    var userId = user && user.LookupId || user || null;
    fd.field('SalesPerson').filter = 'Active eq 1 and User/Id eq ' + userId;
    fd.field('SalesPerson').refresh();
}

fd.spRendered(function() {
    fd.field('SalesPerson').ready().then(function() {
        //filter SalesPerson when User changes
        fd.field('User').$on('change', function(value){
            filterSalesPerson(value);
            fd.field('SalesPerson').value = null;
        });

        //filter SalesPerson when form opens
        fd.field('User').ready().then(function(field) {
            filterSalesPerson(field.value);
        });
    });
});

Might need some additional adjustments, just an example, assuming User is a lookup field, and that User has the same Internal Name in the current form and in the source list.

11

Hi @Nikita_Kurguzov ....somehow I managed.... thanks for your help and support

function filterSalesPers(BusUnit) {
    var BusUnitId = BusUnit && BusUnit.LookupId || BusUnit || null;
    fd.field('SalesPerson').filter = 'Business_x0020_Unit/Id eq ' + BusUnitId + 'and Active eq 1';
    fd.field('SalesPerson').refresh();
}

fd.spRendered(function () {
fd.field('SalesPerson').ready().then(function() {

        //filter Products when Category changes
        fd.field('BusUnit').$on('change', function(value){

             var BusCode = 0;
              try{BusCode = fd.field('BusUnit').value.LookupId;}
              catch{BusCode = 0;}

if (BusCode == 0)
{
          fd.field('SalesPerson').disabled = true;
            fd.field('SalesPerson').value = null;
 }
 else
 {
          fd.field('SalesPerson').disabled = false;
            filterSalesPers(value);
            fd.field('SalesPerson').value = null;
 }
 
        });

        //filter Products when form opens
        fd.field('BusUnit').ready().then(function(field) {
            filterSalesPers(field.value);
        });
    });
});
1 Like