SuiteTalk Advanced Search Examples

Question

I've only worked with NetSuite and SuiteTalk for less than one year. I've found examples of basic searches and advanced searches that leverage saved searches, but I had a hard time finding examples of how to perform an advanced search from scratch with criteria and selected columns in the result set. So I'm asking for examples.

Answer 1

I came back to this code a year later. I was coding another advanced search and thought perhaps it was unnecessary to add a blank element to the array definitions. THAT WAS A MISTAKE. It is absolutely necessary! If you omit these, the search works, but returns nothing but nulls for SearchResult.basic and SearchResult.ItemJoin.

            TransactionSearchAdvanced advanced = new TransactionSearchAdvanced()
            {
                columns = new TransactionSearchRow()
                {
                    basic = new TransactionSearchRowBasic()
                    {

                        // new SearchColumnSelectField() is required!!!!!!
                        internalId = new SearchColumnSelectField[] { new SearchColumnSelectField() } 

                    }
                    ,
                    itemJoin = new ItemSearchRowBasic()
                    {

                        // new SearchColumnSelectField() is required!!!!!!
                        externalId = new SearchColumnSelectField[] { new SearchColumnSelectField() } 

                    }

                }
}

Answer 2

I reviewed examples provided in the NSClientERP project downloadable from NetSuite. Here is a simplified example.

        service.searchPreferences = new SearchPreferences();
        service.searchPreferences.bodyFieldsOnly = true;
        service.searchPreferences.returnSearchColumns = true;

        TransactionSearchAdvanced customSearch = new TransactionSearchAdvanced()
        {
            columns = new TransactionSearchRow()
            {
                basic = new TransactionSearchRowBasic()
                {
                    dateCreated = new SearchColumnDateField[] { new SearchColumnDateField() }
                    , tranDate = new SearchColumnDateField[] { new SearchColumnDateField() }
                    , type = new SearchColumnEnumSelectField[] { new SearchColumnEnumSelectField() }
                    , tranId = new SearchColumnStringField[] { new SearchColumnStringField() }
                    , internalId = new SearchColumnSelectField[] { new SearchColumnSelectField() }
                    , entity = new SearchColumnSelectField[] { new SearchColumnSelectField() }
                    , item = new SearchColumnSelectField[] { new SearchColumnSelectField() }
                    , lastModifiedDate = new SearchColumnDateField[] { new SearchColumnDateField() }
                }
                , itemJoin = new ItemSearchRowBasic()
                {
                    itemId = new SearchColumnStringField[] { new SearchColumnStringField() }
                }
            }
            ,
            criteria = new TransactionSearch()
            {
                basic = new TransactionSearchBasic()
                {
                    type = new SearchEnumMultiSelectField()
                    {
                        @operator = SearchEnumMultiSelectFieldOperator.anyOf
                         , operatorSpecified = true
                         , searchValue = new string[] {
                             "_salesOrder"
                         }
                    }
                    , lastModifiedDate = new SearchDateField()
                    {
                        @operator = SearchDateFieldOperator.onOrAfter
                        , operatorSpecified = true
                        , searchValue = DateTime.Now.AddDays(-3)
                        , searchValueSpecified = true
                    }
                }
            }
        };

        Console.WriteLine("Querying NetSuite");

        SearchResult searchResult = service.search(customSearch);

        Console.WriteLine("Query Results: " + searchResult.totalRecords.ToString());
        int total = searchResult.totalRecords;
        int updated = 0;
        bool searchMore = false;


        Console.WriteLine("\nThe search() operation for transaction was run successfully.");
        Console.WriteLine("\n  Total Records = " + searchResult.totalRecords);
        Console.WriteLine("  Total Pages = " + searchResult.totalPages);
        Console.WriteLine("  Page Size = " + searchResult.pageSize);
        Console.WriteLine("  Current Page Index = " + searchResult.pageIndex);

        Console.WriteLine("\n\nHit Enter to list results");
        Console.ReadLine();

        SearchRow[] records = searchResult.searchRowList;

        if (records != null)
        {
            for (int i = 0, j = (searchResult.pageIndex - 1) * searchResult.pageSize; i < records.Length; i++, j++)
            {
                TransactionSearchRow transactionRow = (TransactionSearchRow) records[i];
                TransactionSearchRowBasic transactionRowBasic = transactionRow.basic;
                ItemSearchRowBasic itemRowBasic = transactionRow.itemJoin;
                Console.WriteLine(
                    "\n  Transaction Return Columns Row[" + j + "]: " +
                    "\n    internalId=" + transactionRowBasic.internalId[0].searchValue.internalId +
                    "\n    tranId=" + transactionRowBasic.tranId[0].searchValue +
                    "\n    type=" + transactionRowBasic.type[0].searchValue +
                    (transactionRowBasic.entity == null ? "" : ("\n    customer internalID=" + transactionRowBasic.entity[0].searchValue.internalId)) +
                    (transactionRowBasic.item == null ? "" : ("\n    item=" + transactionRowBasic.item[0].searchValue.internalId)) +
                    (itemRowBasic == null ? "" : ("\n    item.name=" + itemRowBasic.itemId[0].searchValue)) +
                    (transactionRowBasic.dateCreated == null ? "" : ("\n    createdDate=" + transactionRowBasic.dateCreated[0].searchValue.ToString()) +
                    (transactionRowBasic.tranDate == null ? "" : ("\n    tranDate=" + transactionRowBasic.tranDate[0].searchValue.ToString())) +
                    (transactionRowBasic.lastModifiedDate == null ? "" : ("\n    lastModifiedDate=" + transactionRowBasic.lastModifiedDate[0].searchValue.ToString()))));
            }
        }

It is important to set your search preferences prior to performing your advanced search. Without setting returnSearchFields to true, I got no results. Also, you must supply both criteria and columns. In the NSClientERP example, each object starting with the TransactionSearchAdvanced was instantiated, configured and linked to child objects in separate lines of code. I found this very hard to follow. I'm using one line of code that instantiates the entire search object.

Another thing to note is the itemJoin, which joins the Items table to a sales order line item. There are other joins available to you, such as billingTransactionJoin. Leveraging those joins, you can reach into associated tables to get things like the item's display name, inventory information, or tracking numbers when an item ships.

I have been unable to find a way to sort the results, other than filling a dataset and sorting after the fact. If anyone has a better way, please let me know.

Answer 3

Here's an example that actually does the paging correctly.

service.searchPreferences = new SearchPreferences();
service.searchPreferences.bodyFieldsOnly = true;
service.searchPreferences.returnSearchColumns = true;

TransactionSearchAdvanced customSearch = new TransactionSearchAdvanced()
{
    columns = new TransactionSearchRow()
    {
        basic = new TransactionSearchRowBasic()
        {
            dateCreated = new SearchColumnDateField[] { new SearchColumnDateField() }
            ,
            tranDate = new SearchColumnDateField[] { new SearchColumnDateField() }
            ,
            type = new SearchColumnEnumSelectField[] { new SearchColumnEnumSelectField() }
            ,
            tranId = new SearchColumnStringField[] { new SearchColumnStringField() }
            ,
            internalId = new SearchColumnSelectField[] { new SearchColumnSelectField() }
            ,
            entity = new SearchColumnSelectField[] { new SearchColumnSelectField() }
            ,
            item = new SearchColumnSelectField[] { new SearchColumnSelectField() }
            ,
            lastModifiedDate = new SearchColumnDateField[] { new SearchColumnDateField() }
        }
        ,
        itemJoin = new ItemSearchRowBasic()
        {
            itemId = new SearchColumnStringField[] { new SearchColumnStringField() }
        }
    }
    ,
    criteria = new TransactionSearch()
    {
        basic = new TransactionSearchBasic()
        {
            type = new SearchEnumMultiSelectField()
            {
                @operator = SearchEnumMultiSelectFieldOperator.anyOf
                 ,
                operatorSpecified = true
                 ,
                searchValue = new string[] {
                     "_salesOrder"
                 }
            }
            ,
            lastModifiedDate = new SearchDateField()
            {
                @operator = SearchDateFieldOperator.onOrAfter
                ,
                operatorSpecified = true
                ,
                searchValue = DateTime.Now.AddDays(-15)
                ,
                searchValueSpecified = true
            }
        }
    }
};

Console.WriteLine("Querying NetSuite");

SearchResult res = service.search(customSearch);

Console.WriteLine("\nThe advanced search completed.");
Console.WriteLine("\n  Total Records = " + res.totalRecords);
Console.WriteLine("  Total Pages = " + res.totalPages);
Console.WriteLine("  Page Size = " + res.pageSize);
Console.WriteLine("  Current Page Index = " + res.pageIndex);

Console.WriteLine("\n\nHit Enter to list results");
Console.ReadLine();

while (res.searchRowList.Length > 0)
{
    Console.WriteLine($"\n\nProcessing page: {res.pageIndex}");
    Console.WriteLine($"Result count: {res.searchRowList.Length}");

    Console.WriteLine($"\nHit enter to list page {res.pageIndex} results");
    Console.ReadLine();


    foreach (TransactionSearchRow transactionRow in res.searchRowList)
    {
        TransactionSearchRowBasic transactionRowBasic = transactionRow.basic;
        ItemSearchRowBasic itemRowBasic = transactionRow.itemJoin;
        Console.WriteLine(
            "\n    tranId=" + transactionRowBasic.tranId[0].searchValue +
            (itemRowBasic == null ? "" : ("\n    item.name=" + itemRowBasic.itemId[0].searchValue)) 
        );
    }

    Console.WriteLine("\nQuerying NetSuite again...");
    res = service.searchMore(++res.pageIndex);
}

service.logout();
Console.WriteLine("\n\nHit Enter to close this window.");
Console.ReadLine();