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

is there an easy way to have a custom formula use SQL to display a field only once per unique tranID.  Im trying to display a sum of transaction total, but have it only display once per sales order so the sum is correct.

something like {transaction_total} WHEN unique({tranid}).  But i don't know proper syntax for this

I'm not sure if this will actually work, but you can try `CASE WHEN {linesequencenumber} = 1 THEN {total} END` assuming that you're bringing transaction lines into your results.

maybe all my join fields are messsing this up

but it almost works but the math is off on some of the transactions

like some are doubled, some are tripled etc.

is there like a sublistseq# field I could count and divide by

What transaction type do you want the {total} to appear for?

either way works really, its a search using sales order as criteria then join to IF and invoice

Are the issues you expressed here from the same search mentioned in your other comment about IF duplication? If so whatever is causing that duplication of lines is causing the {total} amount to not calculate correctly.

No, but that duplication has happened on every search ever made on this account

If it's not, then you can try `CASE WHEN ({type} = 'Sales Order' AND {linesequencenumber} = 1) THEN {total} END`

I thought it was just part of how netsuite worked

The information given in your other thread could apply to this and help eliminate the incorrect calculations.