CODEWITHSUNDEEP
searchfilternetsuitesuitescriptsuitescript2.0
MisterFring
MisterFring

Reputation: 41

Operators 'and' & 'or' on filters SuiteScript 2.0 (NetSuite)

I need to make conditions on my filters in my search. I have browsed through a lot of forums without finding functional solutions. After many inconclusive tests, I come to you for help. My code :

function retrieveVendor(vendorValues){

    var subsidiaryIdFilter = SEARCHMODULE.createFilter({
            name: 'internalid',
            join: 'subsidiary',
            operator: SEARCHMODULE.Operator.ANYOF,
            values: vendorValues.companyCode
    });
    var addressFilter = SEARCHMODULE.createFilter({
        name: 'address',
        join: 'vendor',
        operator: SEARCHMODULE.Operator.CONTAINS,
        values: vendorValues.street
    });
    var cityFilter = SEARCHMODULE.createFilter({
        name: 'city',
        join: 'vendor',
        operator: SEARCHMODULE.Operator.CONTAINS,
        values: vendorValues.city
    });
    var zipFilter = SEARCHMODULE.createFilter({
        name: 'zipcode',
        join: 'vendor',
        operator: SEARCHMODULE.Operator.CONTAINS,
        values: vendorValues.postalCode
    });
    
    var vendorIdTest = SEARCHMODULE.create({
        type: SEARCHMODULE.Type.VENDOR_SUBSIDIARY_RELATIONSHIP,
        columns: [
            SEARCHMODULE.createColumn({name:'entityid', join: 'vendor'}),
            SEARCHMODULE.createColumn({name:'internalid', join: 'subsidiary'}),
            SEARCHMODULE.createColumn({name:'address', join: 'vendor'})
            ],
        filters: [
            subsidiaryIdFilter, 'and', [addressFilter, 'or', zipFilter]
        ]
    });
    var nbOfResults = vendorIdTest.runPaged().count;
    var resultArray = [];

    var aa = vendorIdTest.run().each(function(result){
        var obj ={};
        obj['vendorName'] = result.getValue({
            name: 'entityid',
            join: 'vendor'
        });
        obj['idOfSubsidiary'] = result.getValue({
            name: 'internalid',
            join: 'subsidiary'
        });
        obj['vendorAddress'] = result.getValue({
            name: 'address',
            join: 'vendor'
        });
        resultArray.push(obj);
        return true;
    });
    var result = nbOfResults + ' : ' + JSON.stringify(resultArray);
    
    return result;
}

And here are all the syntaxes tried on the 'filters' parameter of my search :

subsidiaryIdFilter, 'and', [addressFilter, 'or', zipFilter]

[subsidiaryIdFilter, 'and', [addressFilter, 'or', zipFilter]]

subsidiaryIdFilter, 'and', (addressFilter, 'or', zipFilter)

subsidiaryIdFilter && (addressFilter, 'or', zipFilter)

subsidiaryIdFilter && (addressFilter || zipFilter)

I have of course searched in the SuiteScript documentation but nothing is mentioned on this subject. Thank you for your help.

Upvotes: 2

Views: 4344

Answers (2)

MisterFring
MisterFring

Reputation: 41

Thanks to the Plugin (https://chrome.google.com/webstore/detail/netsuite-search-export/gglbgdfbkaelbjpjkiepdmfaihdokglp), I found the syntax to put AND and OR between my filters with joins:

var subsidiaryIdFilter = ["subsidiary.internalid", "anyof", vendorValues.companyCode];
var addressFilter = ["vendor.addresslabel", "contains", vendorValues.street];
var cityFilter = ["vendor.city", "contains", vendorValues.city];
var vendorNameFilter = ["vendor.entityid", "contains", vendorValues.name];
var zipFilter = ["vendor.zipcode", "contains", vendorValues.postalCode];
    
var addressSearch = SEARCHMODULE.create({
        type: SEARCHMODULE.Type.VENDOR_SUBSIDIARY_RELATIONSHIP,
        columns: [
                SEARCHMODULE.createColumn({name:'entityid', join: 'vendor', sort : SEARCHMODULE.Sort.ASC})
            ],
        filters: [
            subsidiaryIdFilter, "AND", [zipFilter, "OR", cityFilter, "OR", addressFilter]
        ]
    });

Upvotes: 2

bknights
bknights

Reputation: 15377

It doesn't really work that way. Filters created with search.createFilter are all implicitly ANDed together.

What you need is filter expressions which have a different syntax. You cannot mix filters and filter expressions so:

var vendorIdTest = SEARCHMODULE.create({
    type: SEARCHMODULE.Type.VENDOR_SUBSIDIARY_RELATIONSHIP,
    columns: [
        SEARCHMODULE.createColumn({name:'entityid', join: 'vendor'}),
        SEARCHMODULE.createColumn({name:'internalid', join: 'subsidiary'}),
        SEARCHMODULE.createColumn({name:'address', join: 'vendor'})
        ],
    filters: [
        ['subsidiary', 'anyof', vendorValues.companyCode], 'AND',
        [
            [
                ['vendor.address', 'contains', vendorValues.street ], 'AND',
                ['vendor.city', 'is', vendorValues.city] // i'm guessing you meant to include this
            ], 'OR',
            ['vendor.zipcode', 'contains', vendorValues.postalCode]
        ]
    ]

});

Upvotes: 3

Related Questions