NetSuite ID References for Generating Related Child Transactions

This article is relevant if you are working in SuiteScript and you are generating child (created from) transactions.

Background

In our client engagements, we often need to manage the creation of interconnected transactions within NetSuite. An illustrative case is detailed in my 2015 article on crafting targeted purchase orders from sales orders using SuiteScript 1.0. The essence here is our ability to steer the creation of NetSuite records rather than depending on the platform’s default automation features.

During recent efforts to automate vendor bill creation, we encountered sporadic issues. The objective was to accurately link each vendor bill line to the corresponding line on the original purchase order. This need for precise linkage is common in generating linked child transactions, like deriving an invoice from a sales order. We identified and resolved the underlying bug, and this article aims to guide SuiteScript developers facing similar challenges.

Exploring NetSuite Database Lookups and Line References for SuiteScript

As of 2024, our SuiteScript projects heavily utilize SuiteQL (SQL) for querying data. When accessing purchase order details, it’s crucial to distinguish among three key line identifiers. A common pitfall involves confusing the ID with the LineSequenceNumber, as these values frequently coincide.

To clarify this distinction, here’s a sample of SuiteQL and SuiteScript that demonstrates the concept:

/* Inspiration SuiteQL to gather purchase order
information to support creating a vendor
bill and applying the PO line references */

SELECT 
	po.id AS poid
	,pol.linesequencenumber
	,pol.id
	,pol.uniquekey
	,pol.mainline
	,pol.item AS itemid
	,pol.rate
	,pol.quantity - pol.quantitybilled AS qtytobill
FROM transaction po
JOIN transactionline pol ON pol.transaction = po.id
WHERE po.type = 'PurchOrd'
AND po.id = 216
ORDER BY pol.linesequencenumber
 
/* Inspiration SuiteScript to create a vendor bill
to manually craft the line references to a PO */ 
var bill = record.create({
	isDynamic : true,
	type : record.Type.VENDOR_BILL
})

bill.poLines.forEach(poLine => {	
	bill.selectNewLine('item');
	setItemLine('item', poLine.itemid);
	setItemLine('rate', poLine.rate);
	setItemLine('quantity', poLine.qtyToBill);
	/* here are cross references to the PO */
	setItemLine('orderdoc', poLine.poid);
	setItemLine('orderline', poLine.id);
	/* pol.linesequencenumber is only used in array
		reference work, it is not the linking key */	
	bill.commitLine('item');
	}
});
var id = bill.save(),

Here is our understanding of these line reference keys:

1. uniquekey: a database identifier that is for reference only.  It is not used in SuiteScript
2. id: the key the cross-reference work for SuiteScript orderline references
3. linesequencenumber: an array index if you need to work with the object in SuiteScript

Click on the image to see how the values come back from the database and why one may easily become confused as the values between ID and LineSequenceNumber are the same.

Deepen Your NetSuite Platform Development Skills

As a NetSuite Systems Integrator, our expertise lies in customizing the platform to deliver custom NetSuite solutions, empowering our clients to gain competitive advantages. Our Technology Services are designed to assist our clients in refining their NetSuite configurations, thereby optimizing their scalability and efficiency in business operations while minimizing labor costs.

Our firm embraces the mantra, “Always be Learning,” a principle that holds particular significance when it comes to mastering the NetSuite platform. In this spirit, I extend my gratitude to Sylvain M., Senior Technical Analyst, for sharing valuable insights that not only conserve time for our engineers but also enrich our collective understanding and confidence in devising effective NetSuite solutions. Such insights are crucial in deepening our appreciation for the technology and reinforcing our capability in solution development.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you would like to join a team of ambitious NetSuite practitioners recognized for their exemplary leadership, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube