This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

<@U5L945PD5>
I did this a while back, and it did verify back to the Aging buckets on the customer record. But I cannot remember if the test data included CMs (its been a while since i did this last).

Case When {daysoverdue} &lt;31 then {fxamountremaining} else 0 end
Case When {daysoverdue} &gt; 30 and {daysoverdue} &lt; 61 then {fxamountremaining} else 0 end
Case When {daysoverdue} &gt; 60 and {daysoverdue} &lt; 91 then {fxamountremaining} else 0 end
Case When {daysoverdue} &gt; 90  then {fxamountremaining} else 0 end

Hey <@UHG6BMGQ3> thanks! I have something similar with amount and remainingamount, I see you are using the foreign currency there, I will try to play with that.

For credit memos, the amount remaining will be a credit, but it's not as simple as checking transaction type either. - for journals, it could be either. Best way I've found is checking if original amount is credit/debit ( `* CASE WHEN {amount} &gt; 0 THEN 1 ELSE -1 END` )

Thanks <@U29QCJQF3> I agree is not as simple. This is a very complex calculation using formulas.

CM don't have a "Due Date", so natively always appear in the CURRENT bucket.

I guess, the logic could be something like

CASE WHEN {account.type} = 'Income' AND {type} != 'Invoice' THEN {amount} WHEN {type} = 'Invoice' AND {daysoverdue} &lt;31 THEN {fxamountremaining} END

But not sure that posting journals to the AR and AP is wise - they don't have a status value, so annoyingly stubborn when trying to view only OPEN transactions.

Yeah {fxamount}, because then its in the customer currency, not converted to Base currency.

ta

Thanks <@U29QCJQF3> and <@U05DK75B126> both provided me good points to get a better understanding on the formulas/calculations. Sharing below how I get the 1-30 age period :slightly_smiling_face: the others are the same just the period changes.

`CASE WHEN ({recordtype} &lt;&gt; 'invoice' AND {recordtype} &lt;&gt; 'customerpayment' AND {recordtype} &lt;&gt; 'creditmemo' AND TRUNC({today})-{trandate} BETWEEN 1 AND 30) THEN {amount} WHEN ({recordtype} = 'creditmemo' AND {duedate} IS NULL AND TRUNC({today})-{trandate} BETWEEN 1 AND 30) THEN -({amountremaining}) WHEN ({recordtype} = 'invoice' AND {duedate} IS NOT NULL AND TRUNC({today})-{duedate} BETWEEN 1 AND 30) THEN {amountremaining} WHEN ({recordtype} = 'invoice' AND {duedate} IS NULL AND TRUNC({today})-{trandate}BETWEEN 1 AND 30) THEN {amountremaining} WHEN ({recordtype} = 'customerpayment' AND {duedate} IS NULL AND TRUNC({today})-{trandate} BETWEEN 1 AND 30) THEN -({amountremaining}) END`

<@U5L945PD5> nice, pleased to help in some small way :slightly_smiling_face: