Hello Gurus,
I'm trying to get the locationaveragecost & lastpurchaseprice for each item's location in this SuiteQL query. For example, this can be easily done in the Saved Search like this:
search.createColumn({
name: "locationaveragecost",
join: "item",
summary: "MAX",
label: "Location Average Cost"
}),
search.createColumn({
name: "lastpurchaseprice",
join: "item",
summary: "MAX",
label: "Last Purchase Price"
})
But I'm unable to do it in the SuiteQL query, here is my SuiteQL Query in which I want to add locationaveragecost & lastpurchaseprice :
SELECT
Transaction.ID,
Item.ItemId,
TransactionLine.Location,
SUM(TransactionLine.Quantity) AS quantity,
SUM(TransactionAccountingLine.Amount) as amount,
Item.custitem_commodity_code,
Item.stockunit,
TransactionAccountingLine.accountingbook,
FROM
Transaction
INNER JOIN TransactionAccountingLine ON (
TransactionAccountingLine.Transaction = Transaction.ID
)
LEFT OUTER JOIN TransactionLine ON (
TransactionLine.Transaction = TransactionAccountingLine.Transaction
)
AND (
TransactionLine.ID = TransactionAccountingLine.TransactionLine
)
INNER JOIN Item ON (TransactionLine.Item = Item.ID)
INNER JOIN InventoryItemLocations ON (TransactionLine.Item = InventoryItemLocations.Item)
WHERE
TransactionLine.Subsidiary = 5
AND Transaction.Posting = 'T'
GROUP BY Transaction.ID, Item.ItemId, TransactionLine.Location, Item.custitem_commodity_code, Item.stockunit, TransactionAccountingLine.accountingbook
