This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Hi All, Anyone tried to get the Inventory lot Item transactions sum based on passing Item and lot number as a line level filters. I didn't see any field join which points to the serial/ lot number field in the transactions line. Thanks!

Thanks <@UH8L0UJAU>. Really helpful. I had a look into records catalog table but the inventory assignment join link was broken. Do you have any idea to Join the transactionLine with Inventory assignment

In our instance with the features we have enabled, I see the following columns in `inventoryassignment`:
```bin
id
inventorynumber
inventorystatus
packcarton
pickcarton
quantity
quantitystaged
transaction
transactionline```
You would use `transaction` and `transactionline` to join to your transaction lines

For what it's worth, I inspected the table using `SELECT * FROM inventoryassignment WHERE ROWNUM = 1` -- it's not a foolproof method, because I recall having some issues with `SELECT *` in specific tables, but it can help when other resources are incomplete or corrupted

Thanks for the details. I have already joined transaction with transactionLine. Now I need to get the Inventory number from the InventoryAssignment so here I need to join transactionLine with InventoryAssignment but I couldn't able to find the Join pairs in the records catalog.

Hmm I do see the :no_entry_sign: icon in your screenshot now - is that under the Available column?

I tried this in the sql editor. I can see the results SELECT * FROM inventoryassignment WHERE ROWNUM = 1

Great, maybe you can make it work! Good luck

Thanks for your help:pray:. Appreciate It

<@UH8L0UJAU> - I have tried joining with the Inventory assignment number to get the sum of transaction quantity but unfortunately the result is not coming as expected. I'm doing very simple thing but not sure where I'm doing wrong. Basically, I want to achieve, sum of the Item fullfillment quantities based on the given date, Item and Inventory number parameter. Below is the query, I have written but not sure where exactly I did wrong.`SELECT  transaction.Type, SUM(transactionLine.quantity)  from`
`transaction,`
`transactionLine,`
`inventoryAssignment`
`WHERE`
`transactionLine.Transaction = transaction.ID`
`AND` 
`inventoryAssignment.transactionLine = transactionLine.id`
`AND`
 `transactionLine.item IN ('13920')`
 `AND`
`BUILTIN.DF(inventoryAssignment.inventorynumber) = 'C6069106'`
`AND`
 `(transaction.Type IN ('ItemShip'))`
`AND`
 `TRUNC("TRANSACTION".trandate) &lt;= TO_DATE('09-01-2022', 'MM-DD-YYYY')`
`GROUP BY`
`transaction.Type`

Tried same thing in saved search and I can see the expected results.

• How do the results differ from what you expect?
• Do you have any differing Units of Measure (query returns base units)
• Can you try joining transactionline.transaction and inventoryassignment.transaction as well?