Unleashing the Power of Joined Queries in NetSuite: Combining Results for Enhanced Insights

Introduction

NetSuite, a powerful cloud-based business management suite, empowers organizations to streamline their operations. One notable feature that contributes to this efficiency is the ability to utilize NetSuite tools to join multiple search query results. Joining query output allows users to combine the results of two or more queries, offering a consolidated view of data. In this blog post, we’ll delve into the benefits of using this method, scenarios where they prove invaluable, and demonstrate a practical example of joining two different search query results in NetSuite.

Benefits of Joining Queries

1. Data Consolidation: Joining query results facilitates the aggregation of data from disparate sources or searches, providing a unified dataset. This consolidation simplifies data analysis and reporting.
2. Efficient Insights: By combining search results, users gain a comprehensive understanding of related data points. This enhances decision-making by providing a holistic view of information.
3. Reduced Manual Effort: Instead of manually combining and analyzing separate datasets, joined queries automate the process, saving time and minimizing the risk of errors.

When to Use Joining Queries in NetSuite

1. Comparative Analysis: When you need to compare data across different record types or transactions, joined queries offer a seamless solution.
2. Top Results Aggregation: For scenarios where obtaining the top results based on specific criteria from multiple searches is crucial, joined queries prove highly beneficial.
3. Integrated Reporting: Joined query result sets are particularly useful when creating integrated reports that span across various modules or record types within NetSuite.

Demonstrating Joined Queries with Top 5 Sales Orders and Quotes

Now, let’s walk through a practical example to illustrate the power of joined queries. In this scenario, we’ll retrieve the top 5 sales orders and the top 5 quotes, both sorted by the highest amount in descending order. The resulting data will be rendered in an HTML table with stylized column headers.

/**
 * @NApiVersion 2.x
 * @NScriptType Suitelet
 */
define(['N/search', 'N/ui/serverWidget'], function (search, serverWidget) {

    function onRequest(context) {
        // Create an HTML string for rendering the table
        var html = '<style>th {background-color: silver; color: black;}</style>';
        html += '<table border="1"><tr>';

        // Execute two search queries and combine results into a single array
        var salesOrderQuery = search.create({
            type: 'salesOrder',
            columns: [
              search.createColumn({name: "recordtype"}),
              search.createColumn({name: "entity"}),
              search.createColumn({name: "tranid"}),
              search.createColumn({name: "trandate"}),
              search.createColumn({name: "total", sort: search.Sort.DESC }),
            ],
            title: 'Top 5 Sales Orders',
            filters: ['mainline', 'is', 'T'],
            isPublic: true
        });
        
        var quoteQuery = search.create({
            type: 'estimate',
            columns: [
                      search.createColumn({name: "recordtype"}),
                      search.createColumn({name: "entity"}),
                      search.createColumn({name: "tranid"}),
                      search.createColumn({name: "trandate"}),
                      search.createColumn({name: "total", sort: search.Sort.DESC }),
                     ],
            title: 'Top 5 Quotes',
            filters: ['mainline', 'is', 'T'],
            isPublic: true
        });

        var salesOrderResults = salesOrderQuery.run().getRange({ start: 0, end: 5 });
        var quoteResults = quoteQuery.run().getRange({ start: 0, end: 5});

        // Combine results into a single array
        var combinedResults = combineResults(salesOrderResults, quoteResults);
            log.debug('combinedResults.length', combinedResults.length);

        // Add column headers to the HTML string
        for (var key in combinedResults[0]) {
            html += '<th>' + key + '</th>';
        }

        html += '</tr>';

        // Add rows to the HTML string
        for (var i = 0; i <= combinedResults.length; i++) {
            var result = combinedResults[i];

            html += '<tr>';
            
            // Add field values to the HTML string
            for (var key in result) {
                html += '<td>' + result[key] + '</td>';
            }

            html += '</tr>';
        }

        html += '</table>';

        // Respond with the HTML string
        context.response.write(html);
    }

    function combineResults(query1, query2) {
        var results = [];

        query1.forEach(function (result) {
            var resultObject = {};
            result.columns.forEach(function (column) {
                resultObject[column.name] = result.getValue(column);
            });
            results.push(resultObject);
        });

        query2.forEach(function (result) {
            var resultObject = {};
            result.columns.forEach(function (column) {
                resultObject[column.name] = result.getValue(column);
            });
            results.push(resultObject);
        });

        return results;
    }

    return {
        onRequest: onRequest
    };

});

The results of the two joined queries are then displayed at runtime when the suitelet is executed. We can see that the joined search query results are able to return combined results of each individual query in a single rendered output. This is something that would not be possible using a single standard saved search within NetSuite.

Conclusion

Joining query result sets in NetSuite offer a powerful mechanism for consolidating and analyzing data. Whether you need to compare records, aggregate top results, or create integrated reports, the ability to join multiple search query results enhances your capabilities within the NetSuite platform, including NetSuite analytics. The demonstrated example of retrieving and combining the top 5 sales orders and quotes showcases the practical application of joined queries, providing a foundation for more advanced data analysis and reporting in NetSuite.

About Us

We are a NetSuite Solutions Partner and reseller with 30+ years of combined experience. We specialize in implementation, optimization, integration, rapid project recovery and rescue as well as custom development to meet any business need. If you would like more information on NetSuite or are in need of consultation for your project or implementation, feel free to contact us.

To Contact Us Click Here