Hi All, I'm having an issue getting the start and...
# suiteql
j
Hi All, I'm having an issue getting the start and end date from a SuiteQl Query that i'm running against the case table. I'm basically calling a function that executes the query below, then returns a list of results. When ever i try to log or save the date fields, they show up as undefined. Other fields return the expected values. Any pointers as to what i am doing wrong? Thanks
Copy code
var caseList = sqlQueryRunCaseList(startDate, endDate);
var caseListRecord = caseList[c];

var caseNumber = caseListRecord.casenumber;
var status = caseListRecord.status;
var createddate = caseListRecord.startDate;
var closedate = caseListRecord.endDate;
The Query:
Copy code
SELECT
	casenumber, custevent8, custevent_refund_cost, id, title, status, startDate, endDate
FROM
	supportCase
WHERE
	profile=6 
	AND startDate >= '${startDate}'
	AND startDate <= '${endDate}'
w
Are you sure that your query really gives you any results at all? ${dateVariable} looks a bit fishy. When doing date comparison in netsuite, I usually make sure that it compares with a date value. Can you just log.debug('caseList', { caseList }) and see how the full results looks like? You’re not showing the actual execution code, you haven’t missed running asMappedResults() after query.runSuiteQL()?
if the variable startDate is '2023-08-01'
Copy code
startDate >= to_date('${startDate}', 'yyyy-mm-dd')
j
@Watz thanks for the response. the dates in that query are actually parameters from the deployment and are working exactly as expected. I didn't try to print the whole caseList variable. I'll do that an see what it returns. They query also works in the suiteQl query tool, and does return actual dates. I was thinking that i needed to "cast" it, but like you suggest, i'm not really even sure if it is returning anything in the script. I'll report back later today or tomorrow on my findings. thx!
@Watz my function that i call to run my query returns
Copy code
return query.runSuiteQL( { query: sql, params: [] } ).asMappedResults();
So that's fine. If I print the CaseListRecord, the log only says it's an object. If i JSON stringify it like below I get the following. So, the dates are present.
Copy code
var myObj = JSON.stringify(caseListRecord);
I just don't know how to access it. my simple assignment show nothing as well as numerous iterations of of trying. I'm sure there is a simple solution, but i can't seem to find it!
w
Ahh, now I see what you’re missing
All properties from a result out of asMappedResults() are in lowercase only
startdate != startDate
j
@Watz OMFG!!!!! lol... thanks man, this was driving me crazy. Now it works perfectly!
w
It's really annoying that it doesn’t retain the real column name that you assign it. It would’ve looked way nicer in the code.
j
Agreed! At least I'm well versed in the shortcomings of native date functions in javascript now, LOL... thanks again!