List Fields

The most complex part of CSV exporting is without doubt what you might refer to as "lists inside records".

By far the most common example of this are order lines. Both Sales Orders and Purchase Orders have one or more lines within the record. For example, a Sales Order might have 5 order lines. CSV export/import systems differ in how they treat these lines. Here's how Pakk does it.

You'll notice a field on all lines called exportLineType. For most lines in your CSV export, you'll see this is set to MAIN, which means that line represents the main (or top-level) record - i.e. the Sales Order or Purchase Order itself.

The order lines of the Sales or Purchase Order appear after the MAIN line and their line type is specified in the exportLineType field - so you'll see SalesOrderLines or PurchaseOrderLines. Lines like these 'belong' to the MAIN line directly above them - but you could also look at the ref field to tie each line to its transaction.

Another thing you'll notice is that each of these line records has a number in the exportLineNumberPath field. This number establishes the the order of the lines, i.e. "1" means this is the first line in the list. This will become important later when we look at editing via import.

Advanced List Concepts

Whilst you could stop at the above description of List Fields and you're understanding would be good, if you want to take your understanding to the next level, read on!

Inheritance

If you look carefully, you'll see that the non-MAIN line records on your export include the same set of fields as the MAIN lines (which is unavoidable really, given the limitations of the CSV format). But of course, an Order Line, for example, doesn't contain the same data fields as its parent Order, so what's going on? Essentially, the line replicates the fields from its parent.

It might not be immediately obvious as to why this is useful. The main reason is for ease of reporting. Let's continue with the example of Order Lines within Orders. Because of inheritance, each Order Line has the customer name inherited from the Order. This makes it easy to produce a report like "SKUs purchased by each customer". If the line didn't repeat the data from the parent, this would become very tricky.

Deeply Nested Lines

There's no getting round the fact that deeply nested lines are a nightmare! The main example of this is the hierarchy:

Order > Stock Movement > Product Movement

or more concretely:

Sales Order > Dispatches > Dispatch Lines or Purchase Order > Receipts > Receipt Lines

which can be read as "Sales Orders can have multiple dispatches, each of which can have multiple lines" or "Purchase orders can have multiple receipts, each of which can have multiple lines.

The CSV export (and import) system supports such deeply nested lines and they follow the same logic as singly-nested lines - they belong to the record above. So Dispatch Lines belong to the Dispatch above them, which in turn belongs to the Sales Order above. If you see deeply nested lines you'll also notice that the exportLineNumberPath is a "path" instead of a single number - so instead of just "2" (line 2), you might see "3.2", which means that this is the 2nd Dispatch Line inside the 3rd Dispatch.

You don't have to worry too much about these concepts when exporting - but they become much more important for imports.