Has anyone written a query to join to item bins that are currently associated with the item? I have the following, but it is showing all bins that have had transaction history for that item (I believe). The actual sublist on the item record is much smaller. When I retrieve this information via saved search, it only shows the bins in the bin sublist on the item record. Wondering if there is WHERE clause I am missing or have the wrong table?

SELECT

Item.ID,

Item.ItemID,

BUILTIN.DF( Item.ItemType ) AS Type,

ItemBinQuantity.bin,

BUILTIN.DF(ItemBinQuantity.bin) as binName,

itemBinQuantity.onHand AS Qty,

bin,

BUILTIN.DF(location.id) AS Location,

FROM

Item

JOIN

ItemBinQuantity ON

(Item.ID = itemBinQuantity.Item)

JOIN

bin ON

(ItemBinQuantity.bin = bin.id)

JOIN

Location ON

(bin.location = Location.id)

WHERE

Location.isInactive = 'F'

AND

Item.id = 7

AND

Location.id = 16

Do you want the bins that currently have inventory or the associated bins?

I am looking for just the associated bins. Basically, I am trying to find bins with on hand quantity that are not associated to the item.

For the current inventory balance - which included bins - try the table

inventorybalance

Yea, I got that piece ok. Just can't seem to get only the item bin sublist.