Filter Lookup Values with Or Operator

Forms SharePoint Online
#1

Hi,

I have a Plumsail Lookup with the extra field JobNumber.
I am filtering based on that field.
It contains either a number e.g. "2000093" or the text "General".
I'd like General choices to always be available.

Here is my code:

//Filter based on selected JobNumber always including General

function filterSWMS(Jobnumber) {
    var JobNumberSelected = Jobnumber;
    fd.field('SelectSWMS').filter = "substringof('" + JobNumberSelected + "', JobNumber) or substringof('" + General + "', JobNumber)";
    fd.field('SelectSWMS').widget.dataSource.read();
}

Each part of the filter works by itself. However, I can't figure out the correct syntax to use the Or operator.

Thanks Adam

#2

Hello @AdamSmith,

Could you please share the screenshot of your data in the source list so I could have the complete picture.

Thank you!

#3

Hi @mnikitina,

No worries, Thanks for taking a look.

Here is the source List:
image

Users must enter a job number:
image

Then they will select a SWMS document: (Here is current filtered Lookup, I'd like General to be choices too)
image

Not all Jobs have a SWMS document, in that case the user would select the appropriate General document.

The list will become long.

I'm really enjoying pushing forms to the limit!

Adam

#4

@AdamSmith,

Thank you for providing details!

Please try out the below code. Make sure that you are using the internal names of the fields and columns in the code.

function filterSWMS() {
    var jobNumber = fd.field('JobProjectNumber').value;
    fd.field('SelectSWMS').filter = "JobNumber eq 'General' or JobNumber eq " + jobNumber;
    fd.field('SelectSWMS').widget.dataSource.read();
}

fd.spRendered(function() {

     //filter when job number entered 
    fd.field('JobProjectNumber').$on('change', function(){
        fd.field('SelectSWMS').ready().then(function() {
            filterSWMS();
        });
    });

    //filter when form opens
    fd.field('SelectSWMS').ready().then(function() {
        filterSWMS();
    });
    
});
1 Like
#5

@mnikitina Thank you very much!

That produces the desired result.

Adam

1 Like