Good mornign all, would anyone be able to help me ...
# suiteqlp
Peter Church
03/29/2021, 11:52 AMGood mornign all, would anyone be able to help me as to why i am getting an 'internal server error' when i try to process a Suiteql query that contains a sub query with Rest webservices? the query works fine in Tim Dietricks 'SuiteQL Query Tool' and it also works fine without a sub query but as soon as i try a query with a sub query in rest web services i am getting the following error; ...Would really appreciate any help🙂
d
Dennis Robinett
03/29/2021, 11:53 AMyou can send queries through postman?
Dennis Robinett
03/29/2021, 11:54 AMi guess i didn't know that endpoint existed
z
Zoran Roncevic
03/29/2021, 12:36 PM{{restservices}} is? your restlet or rest netsuite service
t
tdietrich
03/29/2021, 2:03 PM@Peter Church It could be a permissions problem.
The subquery hits the Item table. Is it possible that the account you are using to make the SuiteTalk REST call doesn't have permissions on that table?
p
Peter Church
03/29/2021, 2:06 PMHello Tim, I wondered the same, so to test this I tried querying the items table directly with out a sub query and it works fine
Peter Church
03/29/2021, 2:07 PMmessage has been deleted
Peter Church
03/29/2021, 2:13 PM@Dennis Robinett yes you can send SuiteQL queries through the Rest API. There is a specific endpoint for this https://XXXXXXX.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql
Peter Church
03/29/2021, 2:14 PMAnd yes you can use postman for this... works well🙂
d
Dennis Robinett
03/29/2021, 2:15 PMRegarding permissions, have you tried running the query in Tim's suitelet using the same role as the one doing the rest request.
p
Peter Church
03/29/2021, 2:16 PM@Zoran Roncevic {{restservices}} = https://XXXXXXX.suitetalk.api.netsuite.com/services/rest
d
Dennis Robinett
03/29/2021, 2:17 PMAlthough I usually get a permission error instead of the generic 500 error.
p
Peter Church
03/29/2021, 2:18 PM@Dennis Robinett No i Havent, tried running the query in Tim's suitelet using the same role as the one doing the rest request, good point!
Peter Church
03/29/2021, 2:21 PMI will try this, but I'm a bit of a nubby in net suite and i'm not sure how to log on to that using that role (ie it is the 'token access user role') rather than my admin role
d
Dennis Robinett
03/29/2021, 2:22 PMMight not be able to if the token access role doesn't have UI access. I normally just assign myself the role and then I can switch to it at the top right of the screen.
p
Peter Church
03/29/2021, 2:25 PM@Dennis Robinett Yes, i have had permission errors too, but this seems different, which is why it makes me think its not just a question of a permission to the item table (i had this happen with another table) this is producing an 'internal server error' which i am quite supprised to see from Netsuite
d
Dennis Robinett
03/29/2021, 2:26 PMI'll try your query on mine.
p
Peter Church
03/29/2021, 2:26 PMThanks @Dennis Robinett Do you mind including the pic again?
d
Dennis Robinett
03/29/2021, 2:27 PMmessage has been deleted
Dennis Robinett
03/29/2021, 2:29 PMwhat are you trying to get out of this query?
p
Peter Church
03/29/2021, 2:35 PMI am trying to get the total sales in USD (not our base currency) here is the full Query
Peter Church
03/29/2021, 2:36 PMSelect SUM(Total_Price/USD_Exchange_Rate) as USD_Total from (Select
Case when TransactionLine.QUANTITY = 0 then 0 Else (-TransactionLine.FOREIGNAMOUNT) * Transaction.EXCHANGERATE End AS Total_Price,
(SELECT
EXCHANGERATE
FROM(
SELECT
EXCHANGERATE,*
FROM
Currencyrate
where BASECURRENCY = 1 and TRANSACTIONCURRENCY =2
and EFFECTIVEDATE = TO_DATE(TO_CHAR ( Transaction.TRANDATE , 'YYYY-MM-DD' ) , 'YYYY-MM-DD' )
order by ID Desc
)
where rownum = 1) AS USD_Exchange_Rate
FROM
TransactionLine INNER JOIN
Transaction ON ( Transaction.ID = TransactionLine.Transaction ) LEFT OUTER JOIN
Customer ON ( Customer.ID = Transaction.Entity ) INNER JOIN
Item ON ( Item.ID = TransactionLine.Item )
LEFT OUTER JOIN
companyaddressbookentityaddress on (Transaction.SHIPPINGADDRESS = companyaddressbookentityaddress.NKEY)
Where ( Transaction.Type IN ( 'CustInvc','CustCred') ) AND ( BUILTIN.DF( Transaction.Status ) not in ('GOA : Facturado','GOA : Cancelado','GOA : Cerrado')) AND
(TransactionLine.ASSEMBLYCOMPONENT = 'F') AND
( Transaction.Voided = 'F') and Transaction.TRANDATE >= To_Date ( '2021-03-01', 'YYYY-MM-DD' ) and Transaction.TRANDATE < To_Date ( '2021-04-01', 'YYYY-MM-DD' ) and transactionline.Item <>8573 and transaction.CUSTBODY_L54_TRANS_INTERNA= 'F'
order by --BUILTIN.DF( Transaction.Status ) desc
Transaction.ID,
TransactionLine.linesequencenumber) as table1
Peter Church
03/29/2021, 2:38 PMWhen i try using tim's suitelet in the token based user roll i get a request to change back to the admin roll
Peter Church
03/29/2021, 2:38 PMmessage has been deleted
Peter Church
03/29/2021, 2:40 PMCould anyone help me as to what i would need to allow be be able to use the suitelet in this role ( I have allowed all users and roles in the suitelet itself
Peter Church
03/29/2021, 5:51 PMHave sorted this now and tested in the token based access role and it works fine in times suitelet but still no go in rest
Peter Church
03/29/2021, 5:51 PM*Tim's suitelet
3 Views