https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hello everyone, Can anyone tell me how I can acces...
# suitescript
j
Hello everyone, Can anyone tell me how I can access the values, “7631” and “McDonalds” and “99.13” from this returned structure: {“values”:{“GROUP(customer.internalid)“[{“value”“7631",“text”:“7631"}],“GROUP(customer.altname)“:”McDonalds”,“SUM(formulacurrency)“:”99.13"}} (I’m in a loop from a paged search result.) Tried result.getValue(‘internalid’) ... get null result.getValue(‘altname’) ... get null result.getValue(‘formulacurrency’) ... get null And lots more - still null thanks
d
getValue({ name : 'internalid', join : 'customer', summary : search.Summary.GROUP }) etc.
if you have join/summary you need to explicitly specify those. or you can pass in a Search.Column object as well as a param (/app/help/helpcenter.nl?fid=section_46988464355.html)
s
I'd set the search with a custom label for the customer.altname (e.g 
customer
) and customer.internalid (e.g. 
internalid
) then write
Copy code
.map(nsSearchResult2obj()).forEach( result => {
 result.customer 
result.internalid
})
j
@dbarnett - Thank you - your suggestion worked. I read the documentation and as you can see in my code screenshot - I’m now struggling trying to access the SUM(formulacurrency) though. I’ll keep plugging away.
b
What does the column object for your formula column look like
Whatever parameters are used to create the column are the same needed to get the value
j
Oh boy… This is the column search structure. I was actually simplifying it for my previous post. Next issue is that when I run it like this the Debugger throws this error… "An nlobjSearchColumn contains an invalid column, or is not in proper syntax” I’m a mess here! Sorry folks.
d
@Jack R I think you are confusing name and label params there
I would recommend first creating this search in the UI and using the following extension (https://chrome.google.com/webstore/detail/netsuite-search-export/gglbgdfbkaelbjpjkiepdmfaihdokglp?hl=en-US) it has always been helpful for me when translating saved search to script equivalent.
j
lol @dbarnett thanks again… I have been using it although u wouldn’t know it here… I forgot I made that change. Switched it back error gone. * I was thinking ahead to this last hurdle of parsing 5 different values with the same name param of “formulacurrency” from the same result set to push into my arrObj.
d
@Jack R I believe the pattern when you have multiple formula-named columns is like formulacurrency, formulacurrency_1, ..., formulacurrency_n
not 100% sure on that though, I don't think that is documented anywhere
j
Search looks like it worked with *_n at the end. All these iterations to parse out the number from the object fail. I appreciate your insights.
d
I was referring to the name param of data retrieval portion. you still have to use the same syntax as was mentioned above. result.getValue({ name : 'formulacurrency', summary : search.Summary.SUM }) etc.
j
This still results in null. Tried every syntactical combination I can think of. I’ll revisit tomorrow.
b
Whatever parameters are used to create the column are the same needed to get the value If your column is
Copy code
search.createColumn({
  name: "formulacurrency",
  summary: "SUM",
  formula:
    "CASE WHEN {debitamount} > 0 THEN {amountremaining} ELSE - {amountremaining} END"
});
then your getValue is
Copy code
result.getValue({
  name: "formulacurrency",
  summary: "SUM",
  formula:
    "CASE WHEN {debitamount} > 0 THEN {amountremaining} ELSE - {amountremaining} END"
});
P.S. have your message include the actual text so I don't need to transcribe code
if you have supreme confidence in NetSuite's undocumented JSON behavior, then you can use
Copy code
result.toJSON().values["SUM(formulacurrency)_5"];
j
@battk The JSON worked but scares me. The other approach did not. I would be more comfortable pursuing that.
d
@Jack R what you have on lines 72-76 is the correct format, but the 'name' param is still wrong; when passing the individual options in this way name is just 'formulacurrency'
j
@dbarnett Thanks for all your help here. I attached the last iteration which works but for all 3 versions it just returns the last formulacurrency value I had in the search. I imagine it is related to the _1, _2, _3 but I tried many versions of that too - guess I’m stuck with the toJSON() solution for which I am very thankful to you for suggesting.
b
that sounds weird
try
Copy code
log.debug(
  "current",
  result.getValue(
    search.createColumn({
      name: "formulacurrency",
      summary: search.Summary.SUM,
      formula:
        "CASE WHEN trunc({today}) - NVL({duedate}, {trandate}) < = 0 THEN CASE WHEN {debitamount} > 0 THEN {amountremaining} ELSE -{amountremaining} END END"
    })
  )
);
log.debug(
  "thirty",
  result.getValue(
    search.createColumn({
      name: "formulacurrency",
      summary: search.Summary.SUM,
      formula:
        "CASE WHEN trunc({today}) - NVL({duedate}, {trandate}) BETWEEN 1 AND 30 THEN CASE WHEN {debitamount} > 0 THEN {amountremaining} ELSE - {amountremaining} END END"
    })
  )
);
log.debug(
  "sixty",
  result.getValue(
    search.createColumn({
      name: "formulacurrency",
      summary: "SUM",
      formula:
        "CASE WHEN trunc({today}) - NVL({duedate}, {trandate}) BETWEEN 31 AND 60 THEN CASE WHEN {debitamount} > 0 THEN {amountremaining} ELSE - {amountremaining} END END"
    })
  )
);
or use the original columns created by search.createColumn as the parameter to result.getValue
204 Views
Open in Slack
PreviousNext
Powered by