suiteql to run query and return value

AI generated answer (using internal Slack threads):

To run a SuiteQL query and return a value, you can use the

N/query

module in SuiteScript. Here is an example of how you can use it:

javascript
var query = require('N/query');

var suiteQL = "SELECT Transaction.ID, Transaction.TranID AS ItemFullfillmentID, Transaction.TranDate AS FulfillmentDate, Transaction.Entity AS CustomerID, BUILTIN.DF( Transaction.Entity ) AS CustomerName, SalesOrder.TranID AS SalesOrderNumber, Transaction.OtherRefNum AS CustomerPONumber, BUILTIN.DF( Transaction.ShippingAddress ) AS ShipToAddress, REPLACE( BUILTIN.DF( Transaction.Status ), 'Item Fulfillment : ', '' ) AS Status FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.MainLine = 'T' ) INNER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = TransactionLine.CreatedFrom ) WHERE ( Transaction.Type = 'ItemShip' ) AND ( Transaction.TranDate >= TO_DATE( '2021-09-01', 'YYYY-MM-DD' ) ) AND ( Transaction.Void = 'F' )";

var result = query.runSuiteQL({query: suiteQL});

result.results.forEach(function(row){
    log.debug({
        title: 'Row Data',
        details: row
    });
});

In this example, the

runSuiteQL

function is used to execute the SuiteQL query. The results are then iterated over using the

forEach

function, and each row is logged to the console. Please note that SuiteQL has some limitations compared to SQL. For example, it does not support all SQL functions and it has a limit of 5000 records per query. If you need to retrieve more than 5000 records, you can use the

runSuiteQLPaged

function instead. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******