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

<@U9FNFCWRF> Would you happen to know how to run SuiteQL for getting Customer Payment record lines when all of the lines are JE's applied on that Payment?

I'm getting two lines for the totals for all of the applied JE's, one mainline and one is not.

Each with a reversing amount from the other.

off the top of my head... no... not even sure I'm following what your situation is.

JE lines applied TO a customer Payment?

do you mean JE lines applied to a customer invoice?  and acting like a customer payment?!

JE shows the 2 accounts hence duplicate I think

An actual Customer Payment record where all of the Apply lines are JE's.

I can't figure out the SQL, that's the problem.

oh thought youhad some not working version of the sql that was giving you dupes

If I do a select on transactionlines where transaction equals the customer payment internalid, I get two rows back.

Just not sure why all the JE's are stored in the transactionline table as consolidated lines.  Not sure how that makes any sense.

i remember there's another table but i'm blanking on the name

image.png

Dude, I absolutely loved the show Ted Lasso.  One of my all time fav TV shows.

there's some like accounting line table or something? that kinda works liek the applied TO appied joins you can do in saved searches

that might be what you need insted of just ran lines to not get the dupes

idk I'm not a suiteql expert, just know enough to be dangerous

How about this.  Here is the use case.  So the client has a bunch of these payments they want to unapply all the JE's.  They gave me a saved search with a bunch of JE's but none of them are linked to Payments.

I was going to try and reverse engineer those results into getting the payment records.

yeah, I don't know what the hell they are doing but that was the ask.

It's simple enough to do, only if I can get a list of the payment records.

Either a Map/Reduce to unapply or CSV Import/Update to unapply.  But I need the right population.

So not the ice cream cakes Carvel, but BPM Caravel.

I'm getting close, thanks to Sean Hayes:

```select
t.id,
t.type
from transaction t
inner join nexttransactionlinelink ntl on ntl.previousdoc = t.id
where t.type = 'CustPymt'```