Hi! Im having a problem with a query, im trying to get the quantity on hand but the results are different from my query vs the UI. If anyone have any ideas they more than welcome. Thanks in advance

Can you please post your query?

Sure, sorry

SELECT top 100

	BUILTIN.DF(Item),
    SUM(quantity),
    BUILTIN.DF(inventoryreportinglocation)

FROM transactionline 

	LEFT JOIN transaction ON transaction.id = transactionline.transaction

where BUILTIN.DF(item)='P554004' AND	isinventoryaffecting = 'T'

GROUP BY 

	BUILTIN.DF(Item),
	BUILTIN.DF(inventoryreportinglocation)

It looks like your query is look at quantity on order not quantity on hand, right? If you wanted quantity on hand you'd need to either refactor to an item query I think

Yes, u are right, but im trying to get the amount of stock that was available on an x moment of time. The user will set a date value and the query should gave them the stock at that moment. Thats why im running on the transaction line table.

Do you manufacture? or just buy?

We buy, but we have some item that are kit/package, don't know if it counts as manufacturing.

my next question was kitting. I'm not positive how suiteql handles reporting kits. if P554004 is part of a kit you sell and it may not be counted by your current query

I just tested it with a kit item and it gives me the correct result for the items that make it up. Just in case im adding the query

SELECT top 100

	BUILTIN.DF(Item),
SUM(quantity),

BUILTIN.DF(inventoryreportinglocation)
 FROM transactionline 

	LEFT JOIN transaction ON transaction.id = transactionline.transaction

where BUILTIN.DF(item)='Lucas ART 2' AND	isinventoryaffecting = 'T'

GROUP BY 

	BUILTIN.DF(Item),
	BUILTIN.DF(inventoryreportinglocation)

but it doesn't show you the components of the kit. I think that you need to join your first query to the member table to give you the usage at the component level of the kits + the transaction lines

okay, will try!