https://netsuiteprofessionals.com logo
Join Slack
Powered by
I'm trying to put together a simple saved search t...
# general
m
I'm trying to put together a simple saved search that shows amount due by client and then grouped by custom fields. For amount, I am using Amount Remaining, however, that amount for credit memos is showing as a positive. Does anyone know how to work around this?
r
Don't you want their credits to offset their total balance due?
if so having the CM as a positive is needed
or if your report is already in positive and you need the CM to subtract you should be able to make a formula for this.
m
I do, however, it is not working like that for me. The credit memos are showing as positive. So if a client has an invoice for $100 and a credit for $50, the total is showing as $150 rather than $50
r
ok I made a custom currency formula to solve this same issue for me on a sales rep commission report we use
one sec while I look it up
can you share details of the saved search real quick so i can remake it
check out totals, amount remaining is incorrect, using net amount or amount shows correct values
if you are stuck on using amount remaining you can double subtract out the credits to turn them negative. formula (currency): {amountremaining} - NVL({creditamount}, 0) - NVL({creditamount},0)
like so
m
I ran into this and ended up using something like 
{amountremaining} * CASE WHEN {amount} > 0 THEN 1 ELSE -1 END
. Basically need to check if the original amount is positive or negative and change the amount remaining accordingly
@Rob Cady I don't think your method will work if a credit is partially applied
r
Ah I see what you mean. Yea it will get wrong credit amount.
There is a amount paid field you can subtract to get Cm value accurate for partial
I like your solution better tho! You just would have to use when {creditamount} > 0 for the logic @michoel
Oh it works for the amount too now I see cuz they negative , sweet
@michoel if I want to flip {amount} only on credit memos transactions that are positive would something like this work? {amount} * CASE WHEN {tranID} LIKE 'CM%' AND {amount} > 0 THEN -1 ELSE 1 END
m
I would use the actual transaction type field, but would recommend against that approach. That was my my first attempt to flip based on transaction type but ran into issues with journals that could be credit or debit
r
We have an issue where cm are inverted because they have only a discount item as line items
So I wanna flip when CM shows positive on amount
m
If you wanted to do something specific for credits I would use 
CASE WHEN {type.id} = 'CustCred'
.
r
Ty !
m
@Rob Cady @michoel Thanks!
Open in Slack
PreviousNext
Powered by