How to get customer aging fields from a Netsuite restlet

Question

I'm running into problems getting aging information for a customer record via a Restlet. Specficially I'm looking to get the aging, aging1, aging2, aging3, and aging4 fields from the Customer form for a given customer.

In the UI those values are found on the Customer form under the "Financial" section and look something like:

Current    1-30    Days 31-60    Days 61-90 Days    Over 90 Days
1200.00    800.00  720.37        423.23             42.00

My Restlet code looks something like this:

…
cols[6] = new nlobjSearchColumn('aging');
var result = nlapiSearchRecord(data.record_type, null, filter, cols);
return result;

It works great with other fields such as "balance", but when I include the "aging" field and run my GET I see this error:

"error": {
        "code": "SSS_INVALID_SRCH_COL",
        "message": "An nlobjSearchColumn contains an invalid column, or is not in proper syntax: aging."
    }

Clearly I'm not doing something right. Are those fields special in some way? How do I retrieve those values?

You can use the NetSuite record/schema browser to see which fields are available for scripting and what their internal ID's are. As @RustyShackles suggested, it doesn't appear that those fields are exposed. https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2014_1/script/record/account.html — Mike Robbins, Aug 03 '15 at 15:17

score 3 · Answer 1 · answered Aug 01 '15 at 06:40

3

As far as I recall there is no search column for customers called 'aging'. This is the cause for the Invalid Search column error.

Also those fields might not be exposed either to the searches or suitescript which is why you are getting the error.

answered Aug 01 '15 at 06:40

Rusty Shackles

2,802
10
11

score 1 · Answer 2 · answered Aug 04 '15 at 16:50

1

I actually contacted Netsuite and the previous comments are correct, those fields are not exposed, so I simply can't use them.

There is currently an enhancement request #238854 to expose these fields.

answered Aug 04 '15 at 16:50

David Ranney

59
2
11

You can however derive the aging values very easily.See my post above. – bknights Aug 05 '15 at 17:46

score 0 · Accepted Answer · answered Aug 04 '15 at 19:55

Easy enough to add in a suitescript context. e.g.

var aging = nlapiSearchRecord('invoice', null, [
  new nlobjSearchFilter('daysoverdue', null, 'greaterthan', 0),
  new nlobjSearchFilter('mainline', null, 'is', 'T'),
  new nlobjSearchFilter('amountremaining', null, 'greaterthan', 0)
  //for your RESTLet maybe you need this: , new nlobjSearchFilter('entity', null, 'is', data.customer_id)
], [
  new nlobjSearchColumn('entity', null, 'group'),
  new nlobjSearchColumn('formulanumeric', null, 'sum').setFormula('case when {daysoverdue} < 31 then {amountremaining} else 0 end'),
  new nlobjSearchColumn('formulanumeric', null, 'sum').setFormula('case when {daysoverdue} between 31 and 60 then {amountremaining} else 0 end'),
  new nlobjSearchColumn('formulanumeric', null, 'sum').setFormula('case when {daysoverdue} between 61 and 90 then {amountremaining} else 0 end'),
  new nlobjSearchColumn('formulanumeric', null, 'sum').setFormula('case when {daysoverdue} > 90 then {amountremaining} else 0 end')
]);
aging.forEach(function (inv) {
  var cols = inv.getAllColumns();
  console.log(inv.getText('entity', null, 'group') +
      ' 0-30: ' + inv.getValue(cols[1]) +
      ' 31-60: ' + inv.getValue(cols[2]) +
      ' 61-90: ' + inv.getValue(cols[3]) +
      ' > 90: ' + inv.getValue(cols[4]));
});

Sorry about the formatting. Maybe this will be more readable. I put the code above in my suitescript and it only returned a single "formulanumeric" column. It's as if the actual column name is "formulanumeric" and it keeps overwriting it. — David Ranney, Aug 06 '15 at 00:18
Yes Netsuite is odd about the formulaX fields. Notice how I use the column index as the key for .getValue(). As far as I know that's the only reliable way to get search results when you have multiple formulaX. — bknights, Aug 06 '15 at 14:37
Also I wouldn't tend to just return the raw results of this search. You are much better off extracting the values into a plain javascript object and returning that: `return aging.map(function(inv){ var cols = ...; return { entity: inv.getText('entity', null, 'group'), underThirty:parseFloat(inv.getValue(cols[1])), thirtyTo60:parseFloat(inv.getValue(cols[2])),...}` — bknights, Aug 06 '15 at 14:49
Once I actually looked at *all* of your example code it worked great for me. Thanks so much for the help. — David Ranney, Aug 06 '15 at 22:49

score 0 · Answer 4 · answered Jun 19 '18 at 18:49

Just a followup note on this: After almost three years Netsuite has still not acted on that enhancement request even though it has been voted for 30 times. Also the solution above results in numbers that are usually, but not always, in line with what is displayed by Netsuite.

How to get customer aging fields from a Netsuite restlet

4 Answers4

Linked