Hi everyone! I am struggling to align invoice (CustInvc) line items with their respective delivery (ItemShip) line items through previoustransactionlinelink. They connect through the sales order (SalesOrd), but do not connect directly. This is complicated by a sales order having multiple deliveries, and multiple invoices. Does anyone know a way to connect these? My current approach is creating duplicates

We have a similar issue, where we might have more than one Item Fulfillment and/or Invoice per Sales Order line item. We ended up creating a custom field on Item Fulfillments that links to the Invoice associated with that IF to circumvent this limitation of Netsuite's design.

You made this custom field in NetSuite? Our issue is that we are building this to be used with other client's NetSuite instances and want to avoid having extra fields that the client would have to create. Might there be a way to do this in SQL after pulling the requisite tables?

Yes, a custom transaction body field in Netsuite. There might be, but as far as I've seen, there isn't. Because the Sales Order is separately transformed into an IF and into an Invoice, and because there's no transformation between an IF and an Invoice, there doesn't appear to be a definite link in the database between an IF and an Invoice. From what I understand, the previous/next transaction link and transaction line link tables reference pairs of transformed transactions. I am operating from a fairly limited set of experience though, so someone more knowledgeable may be able to shed better light here.

Thank you for your response! It's also helpful to know that someone else has had this issue, and that I am not just going crazy

One "good enough" approach you might consider if you can't uncover an explicit link via SuiteQL is to consider that an Invoice and IF linked to the same line are themselves linked if they share some other common criteria such as date. This of course only works if the team ships and bills on the same date, and if multiple shipments from the same line wouldn't occur on the same date. Alternatively, depending on how your document numbering is setup (assuming a chronological incrementing), when encountering duplicates you can group based on the pair of documents that has the lowest doc number, then the next highest, etc

I absolutely will if I do find a way!