Really appreciate it, thank you so much for helping me out so quickly, @michoel

Hi. Has anyone had any success in getting around the maximum column names in SuiteQL? Annoyingly some of our custom column names are rather long, and querying for a name such as 'custcol_tss_update_billing_record' returns 'custcol_tss_update_billin#' in the results. As i'm using the results to feed a sublist this is obviously failing, any way around this rather than me coding in some substitutions on the client end? Cheers

Following up from my question from yesterday, I’m still having trouble wrapping my head around how to find the correct names for fields to query on. For example, when doing a query for transactions, I need to add a where clause for the shipmethod. For example, where shipmethod id is 520:

SELECT transaction.id FROM transaction WHERE shipmethod = 520

Using the Schema Browser online I cannot wrap my head around how to map things out to get the select statement to pull in the shipmethod. Any body have experience with this or a good resource about how to read the Scheme Browser to make these connections?

I am confused maybe IN doesn't work like I thought - I have a transaction search where I'm filtering by type using an

IN

clause. e.g.

sql
select id from transaction where type in ('CashSale', 'CustPymnt')

when I have only CashSale in there, it returns many rows. When I add 'CustPymnt' it returns only one row. I thought an IN clause worked like an OR, so I should only see more results not less when adding arguments to the IN clause?

Hey all, does anyone know if there is a table/view that is queryable with SuiteQL that would connect the 

internalid

 of native NetSuite records to the table name for that record? E.g. say I have a custbody field that is List/Record of type Customer. If you were to run 

select fieldvaluetyperecord from customfield where upper(scriptid) = upper('custbody_my_cust_field')

 the 

fieldvaluetyperecord

 returned would be 

-2

 which corresponds to Customer. How do you turn that into the fact that the list of options for this custom field would come from the 

customer

 table/view? For custom lists and custom records you can run 

select scriptid from customlist where internalid = [the fieldvaluetyperecord from prior query]

 and then the 

scriptid

 there would be the “table/view” to query for options for the List/Record field. Same thing for a custom record except you query against the 

customrecordtype

 table/view. I have search a good bit and see no way to do this for the native record types though (which should all have negative 

internalid

). I found a SuiteAnswer on running a little client side suitescript hack to build out the list of all the internalid/name pairs. But this hack still just gives the record “Name” not the table/view name to query against. The point is to be able to start with just a field ID — e.g. 

custbody_my_cust_field

 — and from there via SuiteQL/integration sync out of the account all the valid IDs that could be sent as a value for that custom field. The above can accomplish this when the custom field points at a custom list or custom record. But when it points at a native NetSuite record, I seem to be SOL. If anyone has dabbled in this and has some insight that would be much appreciated.

Started tinkering with running paged queries for the first time but I suspect I'm doing some rookie mistake... not sure why I seem to get data only from the first page

function runPaged(q, params) {
    const results = query.runSuiteQLPaged({ query: q, params: params, pageSize: 1000 });
    let output = [];
    for (let page of results.pageRanges) {
        output.push(...results.fetch({ index: page.index }).data.asMappedResults());
    }
    return output;
}

Has anyone had luck querying budgets? I’m looking to pull uploaded budget numbers by period.

Does anyone know if NetSuite is going to officially release the promise based variant of

query.runSuiteQL

? I can’t find it at the Help Center docs but using

query.runSuiteQL.promise

in client side and server side scripts works

Hi. When I add

ORDER BY

to my queries, I get no result.

SELECT LastName FROM Employee ORDER BY LastName

will give:

{
  "links": [
    {
      "rel": "self",
      "href": "....rest/query/v1/suiteql?limit=1000"
    }
  ],
  "count": 0,
  "hasMore": false,
  "items": [],
  "offset": 0,
  "totalResults": 124
}

If I remove the

ORDER BY

I will get the 124 results. What am I doing wrong?

query runPaged does not allow Order By clause😔.. bug

Hmm. Is there a way I can get pagination without Order By ?

Hey everyone, I am starting with SuiteQL, have you come across any helpful links/documentation?

I am trying to write a CASE statement for a Formula field in a Saved Search, but cannot get the logic right. I need to compare two fields A and B. The search should only bring back records where A and B are different. Field A: Is numeric (dynamic field sources from another search, and only have values ) Field B: Is List/Record (stored value) I presume I need to convert the List/Record value to Number?

Hey Everyone, I am getting this error using SuiteQL:

Search error occurred: Field 'custcol_ihm_order_price' for record 'transaction' was not found.

custcol_ihm_order_price is custom coumn field in the transaction record. var weeklyScheduleQuery = query.create({                 type: "customrecord_ihm_weekly_schedule"             });             var transactionJoin = weeklyScheduleQuery.joinTo({                 fieldId: "custrecord_ihm_ws_sales_order",                 target: query.Type.TRANSACTION             });             weeklyScheduleQuery.columns = [ transactionJoin.createColumn({                     fieldId: "custcol_ihm_order_price"                 }), ];

Lines on transactions require a join in suiteql, you need to get line info from

transactionlines.custcoletc

Is using raw suiteql more performative than using the created queries from the N/query module?

Also does anyone know why the chrome extension for N/query - suiteql was removed?

SuiteQL can be faster than N/query so I've been told. I qualitatively think it is significantly faster than N/search.

I jumped from N/search to SuiteQL because n/query was yucky.

I am trying to work out how to retrieve User Notes tied to transactions (invoices) using SuiteQL. I can query the "note" table, and get the note IDs and the entity id associated with the transaction, but no "note" field (as labeled using the REST API "/note" endpoint), or "memo" field (as labeled when creating a Saved Search). In other words, I can't retrieve the text of the user notes. I have scoured the Records Catalog. I am hoping I am missing something obvious. I'm also trying to retrieve the Author. Has anyone else done something similar? Thanks.

Doubt you will get much help, there just doesnt seem to be much about User Notes exposed to SuiteQL

hi, completely new here . . . finding training resources already, but a question just from querying stuff in datagrip. when i query the transactions table there is a field 

billingaddress

 . . . using the schema/record/connect/analytics browser pages i can't quite correlate how/where it relates . . . in MS SQL there would be a FK and you could see it pointing to address or billingaddress table . . . in this case those tables do not exist (there are customeraddressbook etc) . . . if i use the BUILTIN-DF i can get related data but i can't figure out where it's cominng from and how the docs would tell me that so, how do i figure out where billingaddress "points"? thanks much

How can I get inventory detail from a

TransactionLine

? Specifically an Inventory adjustment record?

I ended up using

InventoryAdjustments

and right joining

TransactionLine

. Do I have access to ISNUMERIC?

Hi I am new to SuiteQL and I wrote a function in my NS script that retrieves items based on their category.

export const getItemsByCategory = ({ categoryIds }) => {
  return query.runSuiteQL({
    query: `SELECT item.id, itemid as name
            FROM item 
            WHERE  custitem_category in (?)`,
    params: categoryIds
  }).asMappedResults()
}

categoryIds is an array of ids. When I pass in one id, it works, but when I pass in 2 ids:

getItemsByCategory({categoryIds:[224,155]})

I get this error:

message: 'Invalid number of parameters. Expected: 1. Provided: 2.',
  object: {
    type: 'error.SuiteScriptError',
    name: 'INVALID_NUMBER_OF_PARAMETERS',
    message: 'Invalid number of parameters. Expected: 1. Provided: 2.',

Can anyone help?

So I ran a SuiteQL query and two multiple select fields returns “RELATIONSHIP FIELD” rather than some data I would have expected. Any idea’s what is going on here?

[
  {
    "owner": 1648,
    "isinactive": "F",
    "custrecord_rfi_trm_fulfilment_number": "RELATIONSHIP FIELD",
    "lastmodified": "17/05/2021",
    "created": "17/05/2021",
    "name": "973",
    "externalid": null,
    "id": 973,
    "custrecord_rfi_trm_ship_nr_det": 836,
    "custrecord_rfi_trm_salesorder": "RELATIONSHIP FIELD"
  }
]

Hi all. Is it safe to move to N/query and away from N/search? Meaning, is this GA? Or is it still beta?

Hi all, I'm wondering if anyone has a direction that you can point me in to start learning suiteql? I'm very comfortable with saved searches and ok with SQL and learning to take it to the next level. Thank you!

zOMG there’s a #suiteql channel

My life is complete