Weird issue with query module. query.runSuiteQL is working fine while query.runSuiteQLPaged throws Unexpected Error. Has anyone experienced the same? any thoughts?

Hi all, I am building an API integration for a client who has a lot of custom fields on the item record. I don't see these records appearing in the SuitQL response however they do appear in the REST API responses.

Hi, I want to query Purchase Orders and show related Item Receipts. When I join NextTransactionLineLink table with below criteria, query filters out Purchase Orders without Item Receipt. Any idea how this can be achieved? I want to join NextTransactionLineLink table again to show also data from Vendor Bill as well.

LEFT JOIN NextTransactionLineLink NTLIR ON TL.id = NTLIR.previousline AND TL.transaction = NTLIR.previousdoc AND BUILTIN.DF(NTLIR.nextType) = 'Item Receipt'

Anyone know any way through SuiteQL or SavedSearch or …. anything … to get the ORDER of the Item Options found at

/app/common/custom/itemoptions.nl

? This order controls what order they appear in on the transaction item line. I already have a M/R script running to store the connection between the ItemOptions and their Items (the only way I found to do this is to literally do a record.load on each item and retrieve the value in the multiselect

itemoptions

field) so I know from my DB which options below to which items, but now I need to know what order to list them in…. Pinging @tdietrich just in case….

Is there a documentation where Builtin functions filters null results? Below query returns 2336 rows and I can see null results for previousdoc. If I use

BUILTIN.DF(PTL1.previousDoc)

result count is decreased to 1023 with only lines where there is value.

SELECT DISTINCT
  T.tranid,
  PTL1.previousDoc
FROM TransactionLine TL 
LEFT JOIN Transaction T ON T.id = TL.transaction
LEFT JOIN PreviousTransactionLink PTL1 ON TL.transaction = PTL1.nextDoc
WHERE TL.quantity > 0 AND TL.item <> '32607' AND TL.taxLine ='F' AND TL.mainLine = 'F' AND BUILTIN.DF(T.type) = 'Purchase Order' AND TL.subsidiary = '74'

I'm seeking assistance with Oracle NetSuite Integration as we are new to NetSuite, specifically to extract data for the following entities: 1. Customers and contacts 2. Invoices and their PDF versions 3. Credit memos 4. Payments and payment links - to know whether link between invoice and payment or creditmemo and invoice link. We are unsure which table to use for extracting the "Currency Revaluation" adjustment entry. Since Oracle is quite restrictive with its REST API, I've begun learning SuiteQL, but I have many questions. Could you please let me know if anyone can assist us?

Is there a way to grab the "Apply" items from a "Credit Memo" using SuiteQL?

Anyone know if I can retrieve all ids/names of Case Profiles using SuiteQL?

I found this table SuiteScriptDetail that looks very interesting in the context of monitoring script performance. But the table is empty in my account. Anyone know anything about it or actually have some data in the table?

I have a weird sql result. This query works fine.

select t.id, t.createddate, cc.transactionline, cc.standardcost
from transaction t
	inner join TransactionAccountingLineCostComponent cc on cc.transaction = t.id
where t.id = 84064869
	and t.recordtype = 'inventorycostrevaluation'

It returns this:

[
     {
          "id": 84064869,
          "createddate": "4/29/2025",
          "transactionline": 1,
          "expr1": 160.54666055666667
     },
     {
          "id": 84064869,
          "createddate": "4/29/2025",
          "transactionline": 2,
          "expr1": 160.37890055666668
     }
]

If I add a

where

clause condition like this

and cc.transactionline = 2

I get no results. The data is clearly there, why won't it filter on results that are readily available?

Greetings everyone, I'm trying to convert a saved search into an appropriate SuiteQL to import into Power BI via SuiteAnalytics ODBC. I've tried multiple times to do this but I'm constantly receiving syntax errors and I'm suspecting it may not be syntax related. I made sure the DSN is referencing the role where I created the search from so I should have access to the tables. Furthermore I'm suing NetSuite 2.0 and have the DSN set to reference Uppercase schema. I can't even pull in a simple test query on the transaction table. Any suggestions?

I'm looking to pull in the related transactions for a support case in SuiteQL. It seems that there is no direct link that is exposed for querying. Am I missing something? Are there any workarounds?

Does anyone know what table vendor tax ID (EIN) is stored in? I can't seem to find it. I also can't find employee SSN

Has anyone successfully found a way to search the

inventorycostrevaluation

record type?

Anyone have any idea on the best way to pass a date through as a param to be used in a WHERE clause within the SuiteQL Query?

does it possible to get all the tables and record (record catalog) in a file ? assuming that I am building an AI agent to learn those tables...

Hi, does anyone use the "params" when running SuiteQL vs just using string interpolation? It seems far worse with no benefit and even after trying 20 combinations of stringifying strings with commas here and there I can't make the params happy with anything other than basic singular values

Its like an anti feature

Hello, has anyone here been able to reproduce the gross amount field from the netsuite.com connector in the netsuite2.com connector?

I'm using Tim's wonderful sql tool (https://timdietrich.me/netsuite-suitescripts/suiteql-query-tool/ if you haven't seen it yet), and I've encountered something weird if I run the following query in the tool

select CURRENT_TIMESTAMP AS report_date, * FROM accountingperiod

The output I get is as pictured. CURRENT_TIMESTAMP gets resolved to a date, not to a datetime. Is there a way to get full datetime output, similar to e.g. createddate in saved searches?

Hi everyone , is possible to query customtransaction record in suite QL Tools

TIMEOFDAY fields are not supported at least as far as I can see in the docs and conversations with others. However, not entirely true. Whilst they don't appear in lists in Analytics you can, in a query string, reference them such:

BUILTIN_RESULT.TYPE_DATETIME(CUSTOMRECORD_STA_EMPLOYEE_REGISTER.custrecord_sta_emp_regi_shift_start) AS shift_start,

In Analytics, on a Dataset, you can use a formula... I have tried to extract the time difference between two of these fields and this is the only valid formula of maaaaaany that I've tried that "validates"

(
  (EXTRACT(HOUR FROM {custrecord_sta_emp_regi_shift_end}) * 3600 +
   EXTRACT(MINUTE FROM {custrecord_sta_emp_regi_shift_end}) * 60 +
   EXTRACT(SECOND FROM {custrecord_sta_emp_regi_shift_end}))
  -
  (EXTRACT(HOUR FROM {custrecord_sta_emp_regi_shift_start}) * 3600 +
   EXTRACT(MINUTE FROM {custrecord_sta_emp_regi_shift_start}) * 60 +
   EXTRACT(SECOND FROM {custrecord_sta_emp_regi_shift_start}))
)

However, it throws an error: "There was a problem loading your data. Please refine your query or try again later." If I plug this in to a query string and run it using the Query Tool it hates it. Does anyone know any funky way to achieve what I'm attempting? (seconds difference between two TIMEOFDAY fields) Incidentally the output without the formula looks like the attached image. I can of course just write a function in my script to extract the time difference but it seems a shame! 😄

When I run this SQL inside the SQL suiteapp (VERSION 1.2.5 DEVELOPED BY MATT BERNSTEIN), it works and returns the correct result, but when I run this script, it either returns no data or items that it shouldn't return

let sql = `
    SELECT 
        transaction.id,
        transaction.type,
        transaction.trandate,
        transaction.memo,
        transaction.status,
        transaction.custbody19,
        transaction.transferLocation
    FROM 
        transaction 
    WHERE 
        transaction.type = 'TrnfrOrd'
        AND transaction.trandate < ADD_MONTHS(SYSDATE, -7)
`;

    log.debug("SuiteQL SQL", sql);

    let mySuiteQLQuery = query.runSuiteQL({ query: sql });

    let results = mySuiteQLQuery.asMappedResults();

    log.debug("Query Results", results);

Does anyone have any tips to query all of the transaction lines table to back fill fields? It takes almost a full day to pull just a fraction of the data currently

Is anyone using the "EXPORT TO SUITE QL" feature in SuiteAnalytics? Using a role with Analytics Admin Full permissions, I can't find this option in the DataSet screen, like @tdietrich mentioned in the article: https://timdietrich.me/blog/netsuite-suiteanalytics-dataset-suiteql-query/ has the feature been deprecated? thanks all!

Does anyone know a way to get SuiteQL to behave the same within SuiteScript as it does when running through the RestAPI? I naively thought that is my SQL ran in Rest it would also run in SuiteScript but apparently not. In rest I can query for

taxrate1

on

transactionLine

but in SuiteQL the same query gives the following error

Field 'taxrate1' for record 'transactionLine' was not found. Reason: NOT_EXPOSED - Field is marked as internal for channel SEARCH

Assuming it's not possible to get them behave the same does anyone know how I can get taxrate1 at a transaction line level from within SuiteQL?

Hi, Do you guys know if I can pull the value of a script parameter on my script page using SuiteQL?

How do I reference the Subsidiary on a customer record

SELECT t.id AS transaction_id, t.tranid AS transaction_number, t.trandate AS transaction_date, t.class AS sales_channel FROM transaction t WHERE t.type = 'CashSale' AND t.status != 'Voided' AND t.tranid IS NOT NULL ORDER BY t.trandate DESC FETCH FIRST 100 ROWS ONLY; im getting class not found, can someone help me? How i can get the selected class on transaction?

Hi folks, I’m trying to get transactions with amortization schedules and their amounts via the suiteql restapi I can get everything else apart from the TotalAmortized amount. Can’t figure out how I can get this

SELECT
            AmortizationSchedule.ID AS ScheduleID,
            BUILTIN.DF(AmortizationSchedule.Name) AS ScheduleName,
            AmortizationSchedule.Status AS ScheduleStatus,
            AmortizationSchedule.StartDate,
            AmortizationSchedule.TotalAmount AS ScheduleTotalAmount,
            AmortizationSchedule.TotalAmortized AS ScheduleTotalAmortized,
            TransactionLine.LineSequenceNumber,
            TransactionLine.Memo,
            TransactionLine.Amount,
            Transaction.TranID,
            Transaction.Type AS TransactionType,
            BUILTIN.DF(TransactionAccountingLine.Account) AS GLAccount,
            TransactionAccountingLine.Debit,
            TransactionAccountingLine.Credit
        FROM AmortizationSchedule
        INNER JOIN TransactionLine ON 
            (TransactionLine.AmortizationSched = AmortizationSchedule.ID)
        INNER JOIN Transaction ON 
            (Transaction.ID = TransactionLine.Transaction)
        INNER JOIN TransactionAccountingLine ON 
            (TransactionAccountingLine.Transaction = Transaction.ID)
        WHERE Transaction.Trandate >= ADD_MONTHS(CURRENT_DATE, -1)
        ORDER BY Transaction.Trandate DESC, AmortizationSchedule.ID, TransactionLine.LineSequenceNumber

Field 'TotalAmortized' for record 'AmortizationSchedule' was not found. Reason: NOT_EXPOSED - Not available for channel SEARCH