Is there any shared key/field between invoice line...
# suitescriptc
Craig
05/08/2025, 3:26 PMIs there any shared key/field between invoice lines and sales order lines? I need to match the lines between transactions.
a
Anthony OConnor
05/08/2025, 3:30 PMnatively there is not, you'd either have to figure out your own composite key with a combination of item-qty-potentially-some-other-tran-line-fields
going forward you could add a new custcol field which sources the SO lineuniquekey onto the invoice line
c
Craig
05/08/2025, 3:31 PMWith the last option, how would that work if I'm not doing the record transform?
a
Anthony OConnor
05/08/2025, 3:32 PMhmm i'm not sure, how do you create invoices?
c
Craig
05/08/2025, 3:33 PMBilling operations schedule (standard feature)
a
Anthony OConnor
05/08/2025, 3:33 PMi'd just test it sandbox, its simple enough to setup and validate it works as expected
c
Craig
05/08/2025, 3:33 PMComing up with my own composite key feels risky - the planets might align and I might get a dupe
a
Anthony OConnor
05/08/2025, 3:37 PMyeah 2nd option is definitely better, I'm just not sure you'd be able to source directly like that depending how invoices are created... so if you have to use code to match the lines before you set the so lineuniquekey reference on the invoice... that code is going to have to use a composite key essentially, which effectively means you're back to solution 1 for the dupe risk
Anthony OConnor
05/08/2025, 3:38 PMyou know what, its early, and this seems like something that should be simple and risk free, so its possible I'm being incredibly dumb about this, hopefully someone else weighs and explains why this is super simple
c
Craig
05/08/2025, 3:39 PMNo shared unique key does make this tough, you're not wrong.
s
Stephanie Hughes
05/08/2025, 3:51 PMCould you go through NextTransactionLineLink? That matches the line number across linked documents.
c
Craig
05/08/2025, 3:51 PMI am not familiar with NextTransactionLineLink; what is that?
s
Stephanie Hughes
05/08/2025, 3:52 PMTim Dietrich has a better writeup of it than I could do: https://timdietrich.me/blog/netsuite-suiteql-related-transactions/
👀 1
c
Craig
05/08/2025, 3:53 PM"More specifically, it's a join between the lineitems on the source and destination transactions."
Craig
05/08/2025, 3:53 PMSounds promising - I can't believe it's necessary to go through SQL to get this though!
Craig
05/09/2025, 9:23 AMThanks, if this is reliable, it's incredible. This means I don't need another script to set a UUID on the sales orders lines to source into the invoice lines.
I'm still testing how reliable this is with line numbers if the ordering of SO items change, but so far it looks great
Craig
05/09/2025, 10:09 AMThe only constraint here is that this table data is only available after a transaction is saved so you can't use it in the context of CREATE and beforeSubmit.
This table gave me most of what I need though - thanks @Stephanie Hughes
🙌 1
s
Stephanie Hughes
05/09/2025, 2:49 PMGlad it helped! I use it in several queries that I just can't figure out how to get user-requested data any other way!
3 Views