Edet had built a dashboard portlet sometime back to identify transactions pending approval. Nothing fancy; a simple transaction saved search with the necessary criteria to determine approval state and to ensure that a single row is produced for each transaction. Everything seemed to be okay until a user recently notified her that some journal entries were not being captured by the search. Edet finally figured out what was wrong and learned an important distinction between the transaction Line IDs and Line Sequence Numbers which caused the problem. Her findings are the inspiration for this article.
If you need to produce a transaction saved search whose results might include Journal Entries (JEs) and you want to ensure that every transaction shows up exactly once, use criterion “Line Sequence Number = 0” instead of “Line = 0” or “Main Line = true”. This is because Line 0 is not guaranteed to exist on JEs.
To further understand the reason for the above advice, let’s consider a simple transaction saved search whose results are shown below. The search matches two transactions: A bill with two expense lines and one item line, and a JE with two lines.
A few things immediately stand out:
- Although the bill has three lines in total, there are four lines in the search results. This is unlike the JE which has only two lines (instead of three as per the bill logic).
- Whereas only the first line of the bill is marked as a main line, denoted by an asterisk (*) in NetSuite saved search results, all lines of the JE are marked with an asterisk.
The output for the bill is the typical pattern in NetSuite where an additional line is used to represent header information. This is evident from the fact that the bill line with an asterisk has the same value for
Memo (Main) which corresponds to the value we entered in the header-level memo field.
Note that the field denoted as
Memoin the search results corresponds to the memo field on transaction lines (sometimes also denoted as
Descriptione.g. on item lines) while
Memo (Main)corresponds to the header-level memo field.
Journal entries are a special beast in NetSuite with all lines marked as main line and no separate line to capture the header-level information! As such, JEs need additional care when creating saved searches.Unlike other transactions types in NetSuite, all JE lines are marked as main line and there is no separate line to capture the header-level info. This detail must be taken into account when creating saved searches that include JEs. Click To Tweet
It is thus no wonder that when we add the
Main Line is true condition to our saved search, we end up with a single line for the bill as desired but two lines for the JE:
Line ID vs. Line Sequence Number
To address the previously identified challenge, two fields come to mind:
Line ID) and
Line Sequence Number. However, these fields have different semantics and only
Line Sequence Number meets our specific needs.
Here’s a quick comparison of the fields. Note that we include the
Line Unique Key which, although not beneficial in our use case due to its semantics, is useful to bear in mind for other scenarios.
|Line ID||Line Sequence Number||Line Unique Key|
|Is guaranteed to be unique within a transaction?||Yes||Yes||Yes|
|Is guaranteed to be unique across transactions?||No||No||Yes|
|Is immutable (i.e. once assigned, does not change or get reassigned to another line)?||Yes||No||Yes|
|Is “guaranteed” to be sequential starting at 0? There is no real guarantee as this is an implementation detail that is nonetheless stable.||No||Yes||No|
As seen from the above comparison, while
Line ID and
Line Sequence Number are guaranteed to be unique per transaction, only
Line Sequence Number is guaranteed to be sequential starting from zero and counting up. This might seem confusing at first because, in most cases, both fields actually have the same values.
Revisiting our earlier example, the bill lines and JE lines initially both had sequential
Line IDs and
Line Sequence Numbers starting at zero:
But here’s where things get interesting: Upon deletion of lines,
Line IDs are static and never get reused on the same transaction whereas
Line Sequence Numbers are dynamic and always get recomputed to produce a sequential series of values. To illustrate this, we duplicated lines on both transactions, then deleted the original lines. See the results below.
While deleting transaction lines might not be so common, it is realistic. A typical use that I’ve seen is when a transaction is updated via CSV import. NetSuite actually deletes the old lines and creates new ones, thus producing the challenge under discussion.
Notice how the two fields now diverge; previously assigned Line IDs 2 and 3 which correspond to the deleted bill lines are not reused on the new lines. Instead, Line IDs 4 and 5 are assigned. However, the line sequence number has no gaps as it is dynamically recomputed based on the number of lines at a given time. The situation is similar for the JE line IDs 0 and 1 which got replaced by 2 and 3.
Line IDsare static and not reused on the same transaction.
Line Sequence Numbersare dynamic and recomputed based on the actual number of lines at any given time. Thus, there is no guarantee that Line 0 will exist on a given JE.
Applying Our Findings
Based on the discourse so far, I hope that is clear why we should not use
Line ID when trying to produce search results that capture a single line per transaction. Going back to our running example, suppose that we used
Line ID as a search condition, we would miss the JE because no line with that ID will be found after the delete-add operation.
Note that for non-JEs deleting line 0 is not a concern because line 0 always refers to the header which cannot get deleted without deleting the entire transaction.
Line Sequence Number instead, both transactions show up exactly once in our search results as desired:
Line ID is Still Useful!
Do not be misled to incorrectly conclude that
Line ID should be avoided at all times. On the contrary, there are situations where it is very valuable (typically in SuiteScript contexts). Here are a few:
- Divergence between
Line Sequence Numbercan be used to detect deletions.
Line ID(script ID:
Line Unique Key(script ID:
lineuniquekey) is useful to target a specific line via code as they are both guaranteed to always refer to the exact same line all the time. Think of these as a sort of internal ID of the line which by definition is unique and immutable. Line ID (script ID:
line) on the other hand, cannot be relied on in such cases as it is mutable.
We hope that you found this article useful and that you feel more confident working with the various transaction line numbering fields now that we have demystified them for you.
NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, check out how you can become an author/collaborator here.
Also, consider subscribing to our no-nonsense email list to get these insights delivered to your inbox as soon as they’re published. Sometimes, ignorance is a choice. Choose wisely!