Hello All,
Just a quick one anyone got a solution for the picture bellow we want to consolidate the BAQ so it shows 1 SKU with the combined amount of stock
If I’m understanding this correctly, you’ll just need to add a calculated field in the BAQ. It will need to be SUM(PartBin.OnHandQty) with a Group By on the other columns.
1 Like
i did that and grouped by part but didnt work
Create a BAQ with 2 tables: Part (for group PartNum) and PartBin (for get OnHandQty…)
- Pull Part.PartNum and tick Group by
- Create a calculated field with the formula: SUM(PartBin.OnHandQty)
If you want more detail, can pull PartBin.WarehouseCode / BinNum and remember tick Group by for them.
Hi,
Could you please share the baq?
as in the query phrase for it ?
*/select
[Part].[PartNum] as [Part_PartNum],
[PartBin].[OnhandQty] as [PartBin_OnhandQty],
(sum(PartBin.OnhandQty)) as [Calculated_count]
from Erp.Part as Part
inner join Erp.PartBin as PartBin on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
group by [Part].[PartNum]
sorry to be a pain im new to this so we now have this query we want to combine the stock
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[PartBin].[WarehouseCode] as [PartBin_WarehouseCode],
(Sum(PartBin.OnhandQty )) as [Calculated_OnHandQty]
from Erp.Part as Part
inner join Erp.PartBin as PartBin on
Part.Company = PartBin.Company
and Part.PartNum = PartBin.PartNum
and ( PartBin.WarehouseCode = 'MAIN' or PartBin.WarehouseCode = 'UNIT5' or PartBin.WarehouseCode = 'MALTBY' )
where (Part.Company = @CurrentCompany)
group by [Part].[Company],
[Part].[PartNum]
but this only pulls in the on hand stock from 3 warehouses which we want we then want combined stock attached to 1 sku ?
so basically 1 sku and combined stock from those three main warehouses
@pompe4040, This baq will give you one SKU from all the warehouses. If you require a warehouse wise then you can add warehouse column in this query.
so what i did was took your BAQ and then added the warehouse field with a sub query of selecting which fields I want so the three above what do i do then as it says the warehouse field either needs to be a group by or a aggravated comand
Navigate to Query Builder > Display Fields > Column Select. In the Display Columns(4) select the Group By checkbox for warehouse code column.
okay cool so now it only pulls in stock levels from those three main warehouses however is there a way it can still group the on hand stock together by sku from the values in those three warehouses
so the bellow to says AG2-POL-NICKEL 64
@pompe4040, If you don’t want the warehouse in your selection then remove it from the display columns list. It will give you a Part-wise total OnhandQty.
Don’t add the PartBin.OnhandQty field to the BAQ. That field should only be referenced in your calculated field.
@pompe4040 attached is the BAQ as you’re requesting it. Note, since you’re wanting Warehouse qty and All Qty, the “All Qty” is going to get duplicated every time the part exists in multiple warehouses. I removed Company from the screenshot as I didn’t want to list it but its on the BAQ.
The way it was added was via Subquery, try reverse engineer the attached BAQ for learning purposes. BAQ_Grouping.baq (28.3 KB)
Because you have different Warehouse so it groups by Part and Warehouse. If you want to group by Part only, remove column Warehouse. In case you can pull only PartBin table, no need Warehse table.
