how to query custom transaction using suiteql

AI generated answer (using internal Slack threads):

To query custom transactions using SuiteQL, you can use the

Transaction

table and join it with the

TransactionLine

table. Here is a sample query:

sql
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' )

This query selects various fields from the

Transaction

table and joins it with the

TransactionLine

table on the

Transaction.ID

field. It also joins with another

Transaction

table (aliased as

SalesOrder

) on the

TransactionLine.CreatedFrom

field. The

WHERE

clause filters the results to only include transactions of type 'ItemShip', transactions that are not void, and transactions that have a

TranDate

greater than or equal to '2021-09-01'. Please note that SuiteQL does not support joins in a way comparable to

N/search

when querying file attachments on transactions. However, you can use SuiteScript to join transactions with their file attachments. Here's a tested SuiteScript example:

javascript
const tranId = '123456';

const salesOrderSearchObj = search.create({
    type: 'salesorder',
    filters: [
        ['internalid', 'anyof', tranId],
        'AND',
        ['type', 'anyof', 'SalesOrd'],
        'AND',
        ['mainline', 'is', 'T'],
    ],
    columns: [
        search.createColumn({
            name: 'internalid',
            join: 'file',
            label: 'Internal ID',
        }),
        search.createColumn({
            name: 'name',
            join: 'file',
            label: 'Name',
        }),
        search.createColumn({ name: 'tranid', label: 'Document Number' }),
    ],
});
const searchResultCount = salesOrderSearchObj.runPaged().count;
log.debug('salesOrderSearchObj result count', searchResultCount);
salesOrderSearchObj.run().each(function (result) {
    return true;
});

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