Removing null/blank items from a lookup field

Forms SharePoint Online
1

Hi,

I have a calculated lookup field in sharepoint, which is formulated to only show the names of instructors if they have a status of "active". I am trying to remove the null/blank values from the lookup.

I have tried:
fd.spRendered(function(vue) {
fd.field('Instructor').ready().then(function() {
fd.field('Instructor').filter = 'Title ne null';
fd.field('Instructor').refresh();
});

(where 'Instructor' is the internal name of the lookup field). I thought this was working initially, but the null items are showing again within the dropdown list. I'd be really grateful for any advice :slight_smile:

2

Dear @traveller,
It should work in theory (I've also just tested it) - please, check console for errors.

In your code sample I see that you're missing the closing brackets, as it should be:

fd.spRendered(function(vue) {
  fd.field('Instructor').ready().then(function() {
    fd.field('Instructor').filter = 'Title ne null';
    fd.field('Instructor').refresh();
  });
});
3

Thanks so much!!
That was the issue :woman_facepalming:

1 Like
4

Hello, I'm facing the same problem in SharePoint 2019. But the code isn't working for me, I', still seeing empty choices in my lookup field (not "null values")

5

Dear @georgeselkassouf,
And what field do you have selected for the Lookup? Is it the Title field or something else?

6

It's another field from another list, should it be from the same list only?

7

Dear @georgeselkassouf,
No, but the code is specifically tailored to filter out empty Title field, not any other field. You can use Internal Name of your selected field, here:
fd.field('Instructor').filter = 'FieldName ne null';

8

I tried it again and it worked. But the lookup I'm using is getting information from a column in another list, how can I reference the internal field name then? I need to filter blank values from a column in another list not the same list.

9

Dear @georgeselkassouf,
What do you mean? Maybe some screenshots would help - because I don't quite get it. Yes, lookup fields are normally used to retrieve information from another list, and this filter query allows you to filter that lookup list for values that you need.

For example, you select Title field in another list - this code will allow you to filter out all blank Titles in that list. What's the issue here? What's the type of column you have selected in lookup field as source?

10

Hello,
I have a lookup column in my list. This lookup column is getting the data from a calculated column in another list which contains some blank values. I need to hide the blank values in my lookup field.

11

Dear @georgeselkassouf,
Yeah, it wouldn't be possible to filter by a Calculated column, as it doesn't store values. Calculated columns take their value from other columns, so you can instead create a filter based on the calculation in that column.

For example, if your Calculated column displays: [Status] + [Category]
You can write a custom filter, that will not show any values with blank Status and Category fields:
fd.field('Instructor').filter = 'Status ne null or Category ne null';

The filter will need to be carefully created to exactly match your Calculated column formula, I can't give an easy answer to cover all potential calculations.

12

It works fine now, thank you!

1 Like