suite query is showing error for invalid search type when attempting to pull values from multiple select field: SELECT e.id FROM transaction t JOIN vendor v ON t.entity = v.id JOIN employee e ON v.custentity_aw_automate_vendor_approver = e.id WHERE t.id = ${record.id}

AI generated answer (using internal Slack threads):

Based on the conversations, it seems like the issue with the first query was due to the use of

UNION

in SuiteQL. The user was trying to get all people that are vendors and employees but kept getting an unexpected error. The solution was to modify the query to use a subquery and then select from that result. The corrected query was:

sql
SELECT t.* FROM ((SELECT id, firstname, lastname, email, isinactive FROM vendor WHERE isperson = 'T') UNION (SELECT id, firstname, lastname, email, isinactive FROM employee)) t

In the second conversation, the user was trying to perform a search for some Sales Orders using SQL but was getting an error. The issue was that the user was using the wrong browser. The correct browser to use was the Analytics Browser. In the third conversation, a user provided a SuiteQL query to help get started with a specific task. The query was:

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

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