Need help with BILL OF Material Query. I got following requirements

[former_member197302]

create a query with the Inventory for xyx. What are the considerations?

• The items with Property OITM .[QryGroup22] ='Y' will be the ones marked for xyz.
• We need to show the Stock available we have in 01
• We need to show the Stock available we have in SS
• There is Bill Of Materials: that is the challenge. The components of each BOM will need to be the ones we need to show the stock of.
• Now, the other challenge, is that user wants to see, from the components what is the lesser quantity they have, and then show that is the final quantity. This can be in another column. As long as user can see what is the component that has the less quantity.

I figured it out first 3 steps Need help on 4th and 5th

[former_member419594]

Hello,

maybe... you have the same made:

select T0.Code,T2.ItemName, T1.Code, T1.Quantity, T1.Warehouse, T3.OnHand as 'Onhand 01', T4.OnHand as 'onhand SS'
from OITT T0
Inner join Itt1 T1 on T0.code = T1.Father
inner join oitm T2 on T0.Code = T2.ItemCode
LEFT JOIN OITW T3 on T1.Code = T3.ItemCode and T3.WhsCode = '01'
LEFT JOIN OITW T4 on T1.Code = T4.ItemCode and T4.WhsCode = 'SS'
Where T2.QryGroup2 = 'Y' and T0.Code = '[%1]'

... you can calculate with the fields T1.Quantity + T3.Onhand + T4.Onhand....

Hope that helps

Best regards

Markus