SuiteQL example

export const getTransactionLineQuery = (
    nsOrderId: string,
    additionalFields: string[],
) => {
    const columns = [
          "Transaction.id as TransactionId",     
          "Entity.altname as CustomerName",       
          "BUILTIN.DF(Transaction.shippingaddress) AS shippingaddressname",   
          "TO_CHAR(Transaction.createddate, 'YYYY-MM-DD HH24:MI:SS') as CreatedDate",
          "TransactionLine.linesequencenumber as LineNumber", 
          "Item.id as itemId", 
          "Item.displayname as ItemDisplayName", 
          "TransactionLine.Quantity"
    ]
     // When a user installs this Netsuite Pandium integration they can select NetSuite sales order transaction fields from a drop down in the connection settings.  Those selected fields are passed to this function, so we can make a query that will fetch those fields. 
    additionalFields.forEach((field) => {
        columns.push(`transaction.${field}`)
    })

    const joins = [
        'INNER JOIN Transaction ON (Transaction.ID = TransactionLine.Transaction)',
        'INNER JOIN Item ON (Item.ID = TransactionLine.Item)',
        'LEFT OUTER JOIN Entity ON (Entity.ID = Transaction.Entity)',
    ]

    const filters = [`(transaction.id = '${<SALES ORDER ID>}'`)]

    return `SELECT
                ${columns.join(', ')}
            FROM
                TransactionLine
                ${joins.join(' ')}
            WHERE
                ${filters.join(' and ')}`
    
}