Hiding blank values from drop down list

Forms
1

Hello,

I have a sharepoint list with a lookup column to another list.

I would like this column to show the values where another column in this lookup list is equal to something. I have tried to creating a new calculated field which contains the values i would like to show and also blanks.

These blanks appear in the sharepoint form drop down list. I have tried to filter these but i cannot do it on a calculated column.

Is there anyway to hide the blank values from a sharepoint column that is a lookup column?

Thank you

2

Hello @jamesmitchell,

Welcome to Plumsail Community!

Column of type 'Calculated' is not supported by OData filter query for the lookup field.

To get rid of the blank values, you can use this code:

fd.field('Lookup').filter = "Title ne null";
fd.field('Lookup').refresh();

Please find more examples in Configure lookup field filters on a SharePoint form with JavaScript article.

3

Hello,

Thank you for the help.

Sorry, I don't understand.

I have a column named 'Subcontractors' that is a lookup column type to a calculated column in a different list called 'ActiveSubcontractors'

I want to use the 'Subcontractors' field in the form but it is showing the blank records (the ones that do not meet the criteria in the calculated column).

How do i hide these blank values from the form? Im not sure what i put into the 'Lookup' field and the filter?

Thank you

4

@jamesmitchell,

I'm sorry, I will be more specific.

You cannot remove empty values by filtering on a calculated column. Calculated column type is not supported by filter query.

That's why you need to filter by another column or columns to remove blank options. Simple example: the calculated column displays Title:
image

One item has no title, that's why I see 0 in the list of options:

image
image958×161 5.49 KB

To remove that 0 value, I can't use the filter like so:

fd.field('Lookup').filter = "Calculated ne 0";

I need to filter by Title column, which is blank:

fd.field('Lookup').filter = "Title ne null";

In your case the filter might be more complex, all depends on the formula you use in the calculated field.