https://netsuiteprofessionals.com logo
Join Slack
Powered by
Anyone has an example of query getting data from t...
# suitescript
a
Anyone has an example of query getting data from the createdfrom -> createdfrom? two levels join?
c
You'd have to do a lookup on the 2nd one. You could get the 2nd ID in a field via a sourced field if you wanted but you can't go to that 2nd level of data just from sourcing
a
@creeceNot even with 
N/query
?
c
I mean you can do a search if you want
if you have the ID, a simple lookup will work
g
@alien4u you can try the PreviousTransactionLineLink and NextTransactionLineLink tables example from Tim's blog https://timdietrich.me/blog/netsuite-suiteql-related-transactions/ not sure if this is what you are looking for
👍 2
a
@creece I'm trying to access the createdfrom of the createdfrom data...
I don't want to do two searches if this can be done with one query...
c
Never used SuiteAnalytic Workbooks or N/query so no idea honestly
theoretically, if you aren't working with giant data sets, it really doesn't matter. Performance will be about the same if you do 2 searches or 1.
s
I'd recommend a SuiteQL query that has 'normalish' SQL join capability
a
@stalbert I want to use SuiteQL however this does not feel standard or normalized yet, no in my book, I may end up going two search route: `const sQuery = `SELECT PT.ID,PT.Type FROM PreviousTransactionLineLink AS PTLL INNER JOIN Transaction AS PT ON (PT.ID = PTLL.PreviousDoc) WHERE (PTLL.PreviousDoc = ${pCreatedFrom}`;`
c
@tdietrich is a wizard with it he can prob help if he's on
s
also glad to see use of 
const
and template strings 🙂
a
@stalbert Ohh I love 2.1 these days...
t
@alien4u Let me know if you need help.
a
@tdietrich Thanks, I'm wondering if there is a way to get data from the createdfrom of the createdfrom without diving into a super complex text query but using standard methods, meaning query.create(columns).autojoin...
For this specific scenario it would be getting data from a Cash Refund `createdfrom`(Cash Sale) and then from Cash Sale `createdfrom`(Sales Order).
t
@alien4u Last week in the #C01FBBZ8PQC channel, @jen was working on something similar, but on the purchasing / AP side of things.
Let me look at my notes.
j
Couple of different approaches:
Copy code
SELECT DISTINCT
grandparent.id AS grandparent_id,
BUILTIN.DF(grandparent.type) AS grandparent_type,
grandparent.tranid AS grandparent_tranid,
parent.tranid AS parent_id,
BUILTIN.DF(parent.type) AS parent_type,
parent.tranid AS parent_tranid,
child.tranid AS child_id,
BUILTIN.DF(child.type) AS child_type,
child.tranid AS child_tranid

FROM transaction child
JOIN transactionline child_parent_tl ON (child.id = child_parent_tl.transaction)
JOIN transaction parent ON (child_parent_tl.createdfrom = parent.id)
JOIN transactionline parent_grandparent_tl ON (parent.id = parent_grandparent_tl.transaction)
JOIN transaction grandparent ON (parent_grandparent_tl.createdfrom = grandparent.id)

WHERE child.id = 360403
or
Copy code
SELECT DISTINCT

grandparent.id AS grandparent_id,
BUILTIN.DF(grandparent.type) AS grandparent_type,
grandparent.tranid AS grandparent_tranid,
parent_grandparent_ptll.linktype AS grandparent_parent_linktype,
parent.tranid AS parent_id,
BUILTIN.DF(parent.type) AS parent_type,
parent.tranid AS parent_tranid,
child_parent_ptll.linktype AS child_parent_linktype,
child.tranid AS child_id,
BUILTIN.DF(child.type) AS child_type,
child.tranid AS child_tranid

FROM transaction child
JOIN PreviousTransactionLineLink child_parent_ptll ON (child.id = child_parent_ptll.NextDoc)
JOIN transaction parent ON (child_parent_ptll.PreviousDoc = parent.id)
JOIN PreviousTransactionLineLink parent_grandparent_ptll ON (parent.id = parent_grandparent_ptll.NextDoc)
JOIN transaction grandparent ON (parent_grandparent_ptll.PreviousDoc = grandparent.id)

WHERE child.id = 360403
sidenote: @jen is stil stuck on her previous issue, which is linking Bill Credits to Bill Payments. Sometimes we have a Bill Credit appearing in the Credits Applied on a Bill Payment, but the Bill it’s applied to does NOT appear in the Applied To list on that same Bill Payment.
t
@alien4u This might help (or not!)...
Copy code
SELECT
	Refund.TransactionNumber AS RefundTransactionNumber,
	BUILTIN.DF( Refund.Entity ) AS CustomerName,
	CustCredLink.NextDoc AS CustCredID,
	BUILTIN.DF( CustCredLink.NextDoc ) AS CustCredTranID,
	CustPymtLink.NextDoc AS CustPymtID,
	BUILTIN.DF( CustPymtLink.NextDoc ) AS CustPymtTranID
FROM
	Transaction AS Refund
	LEFT OUTER JOIN PreviousTransactionLineLink AS CustCredLink ON
		( CustCredLink.PreviousDoc = Refund.ID ) AND ( CustCredLink.NextType = 'CustCred' )
	LEFT OUTER JOIN PreviousTransactionLineLink AS CustPymtLink ON
		( CustPymtLink.PreviousDoc = Refund.ID ) AND ( CustPymtLink.NextType = 'CustPymt' )
WHERE
	( Refund.ID = 2287936 )
So essentially, given the NetSuite ID for a refund, this will give you the Credit Memo that it originated from, and (if applicable) the payment that was made.
In theory, you should be able to work back one step to get from the credit memo to the transaction it was created for.
I hope that helps.
b
for @alien4u question, the general approach is to make your dataset in the ui first, load it in script, then copy the columns (and conditions)
a
Uhmm @tdietrich @jen I see what you are doing thanks, however I will like to go for an API method I will try to see if I can get it going with @battk approach. I dont see this plain text PL SQL query as something easy to maintain, remember or being usable when you work with a team with more than 4 developers.
Thanks…
b
the column field ids tend to be more useful than the condition
there is some undocumented syntax that netsuite uses for its field ids that automatically joins
3 Views
Open in Slack
PreviousNext
Powered by