0

This is related to a question I asked previously:

How to get customer aging fields from a Netsuite restlet

The technique described in the answer to that question works great, but it doesn't handle credit memos. I'm having problems figuring out how to take credit memos into account.

For example, I have a customer record that displays the following values:

BALANCE
1950.00

OVERDUE BALANCE
2000.00

CURRENT     1-30 DAYS     31-60 DAYS     61-90 DAYS     OVER 90 DAYS
0.00        -50.00        2,000.00       0.00           0.00

I can pull the 2000 out just fine, but I can't seem to get the -50 from the credit memo.

I tried adjusting the invoice query to do this:

new nlobjSearchFilter('amountremaining', null, 'notequalto', 0),

I also tried doing a separate query for credit memos:

var agingcmemo = nlapiSearchRecord(
    'creditmemo',
    null,
    [
        new nlobjSearchFilter('daysoverdue', null, 'greaterthan', 0),
        new nlobjSearchFilter('mainline', null, 'is', 'T'),
        new nlobjSearchFilter('amountremaining', null, 'notequalto', 0),
        new nlobjSearchFilter('entity', null, 'is', result[0].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')                    
    ]
);

That query returns no rows. If I remove all conditions except for the entity it returns two rows, neither of which is the actual credit memo.

Anyone have any ideas on how to get those right numbers including the credit memos?

quarks
  • 33,478
  • 73
  • 290
  • 513
David Ranney
  • 59
  • 2
  • 11

3 Answers3

1

there's no concept of daysoverdue with a credit memo. If you run the code below does that sum to $50? You should be able to use that to get your open credit memo value and combine that with your open invoice amounts.

The other than that is it possible that you have an unapplied payment or customer deposit?

var agingcmemo = nlapiSearchRecord(
    'creditmemo',
    null,
    [
        new nlobjSearchFilter('mainline', null, 'is', 'T'),
        new nlobjSearchFilter('status', null, 'anyof', ['CustCred:A']),
        new nlobjSearchFilter('entity', null, 'is', 996)
    ],
    [
        new nlobjSearchColumn('entity', null, 'group'),
        new nlobjSearchColumn('amountremaining', null, 'sum')                 
    ]
);
agingcmemo.forEach(function(c){
var cols = c.getAllColumns();
cols.forEach(function(col, idx){ console.log(idx +': '+c.getValue(col));});
console.log('');
});
bknights
  • 14,408
  • 2
  • 18
  • 31
  • It looks like I can use the same formulae as before, but using "daysopen" instead of "daysoverdue". Am I on the right track? – David Ranney Aug 25 '15 at 17:48
  • I tried using columns like `new nlobjSearchColumn('formulanumeric', null, 'sum').setFormula('case when {daysopen} < 31 then {amountremaining} else 0 end'),` and it seems to work fine. Thanks again for the great help! – David Ranney Aug 25 '15 at 18:16
0

I've used a different approach to building an aging report in a Saved Search that might work better for you as it takes credit memos into account as well. (The groups below age by months rather than 30 day increments but you can replace them with the formula you were using before):

Criteria

Results

michoel
  • 3,725
  • 2
  • 16
  • 19
  • Are you querying against "invoice" or "credit memo" there? – David Ranney Aug 25 '15 at 18:17
  • @DavidRanney This will search for all transactions that post to the A/P GL account including invoices, credit notes, journals etc.. – michoel Aug 25 '15 at 23:41
  • I guess my question was when you do Reports > New Search what do you choose as the search type? I'm assuming "Transaction" but I want to make sure I'm not doing it wrong. – David Ranney Aug 28 '15 at 00:08
  • Also, your example works in terms of months. Is there a way to somehow convert that to days? Months is a fairly non-standard unit of measure. I want it to match up with the aging fields in the "Financial" section of the Customer screen which is in Days. – David Ranney Aug 28 '15 at 00:10
  • Is there a really good reference somewhere that explains what you can do with formulae in searches? It would be nice to work in terms of an interval such as {today} - {trandate} instead of having to convert to months and figure in years separately. – David Ranney Aug 28 '15 at 00:13
  • @DavidRanney Correct, I have selected 'Transaction' as the search type. The equivalent in SuiteScript would be `nlapiSearchRecord('transaction', ...`. – michoel Aug 28 '15 at 03:27
  • I've set the aging by months as that is how we do it here in Australia. You can modify the formula to suit your needs. The formula is Oracle PL/SQL so most of what's valid there should work. – michoel Aug 28 '15 at 03:38
  • An additional question on this: I don't see an "Entity" search criterion. How do you construct this query for a single entity? Can I query against this saved search via the API and hand it the entity I want to search against? – David Ranney Aug 28 '15 at 21:24
  • @DavidRanney In the UI it is labeled 'Name'. In SuiteScript you would add a filter for the 'entity' column. – michoel Aug 31 '15 at 01:33
0

You can define "Transaction Type" in the search criteria, this way you can create saved searches for each specific transaction types. Alternately you can add transaction type as a filter even color code it by the transaction types. In my opinion transaction search is the mother of all searches in NS because majority of objects and fields are exposed to this search type.

Sam Azad
  • 139
  • 3