Question

Creating a BAQ to compare warehouse on hand balance with site balance

  • 3 November 2022
  • 7 replies
  • 243 views

Userlevel 1

I have a BAQ that displays all partnum, warehouse, and bin and current on hand balance if it is below 0. It is our negative balance report. I want to add a column that displays the total site quantity so the user can easily see if we are actually negative or if the inventory is just in the wrong warehouse. 

 

I cannot figure out how to get it to work to sum the site quantity and add that as an additional column into the original BAQ. Has anyone done something similar that can provide me some instruction? We are running the most recent version Kinetic 2022.2. 


7 replies

Userlevel 2

This is a little tricky. Do you used shared warehouses? If not, you should be able to equate a warehouse to a site pretty easily. I’ve done this with PartBin before where it gives you the warehouse but not the site. I just do a case statement that calculates the Site based on the warehouse. Is this what you mean?

 

case

  when PartBin.WarehseCode IN (‘MAIN’, ‘MAIN-SHIP’, ‘MAIN-QC’)

  then ‘HQ’

  when PartBin.WarehseCode IN (‘OFFSITENY’)

  then ‘NewYork’

  when PartBin.WarehseCode IN (‘ANOTHER’, ‘ANOTHER-QC’)

  then ‘Another’

end

I’ve done something like this by creating a subquery that summarizes the on-hand qty in PartWhse by site or by company.  Then join it back to your original query by part.  Let me know and I can put something together

Userlevel 1

Anytime I need to group by and sum I end up using a subquery. I know there are other ways. (As Dan noted above for shared warehouse you would have to decide how to group.)

I have several times done a subquery and group the on hand and the demand qty in PartWhse. Then I do add a calculated field to show “Available Qty.”

Not exactly what you asked for, but I have a BAQ that shows parts that are negative in a bin and all the other bins and the quantities in those bins for that part.  
 

If this would be useful, let me know. I can upload it Friday. 
 

Ayrin

Userlevel 3

@ayrin.hilbert 

Hi Ayrin.  Even if your BAQ isn’t useful to Michaela, it may be useful to other members/users.  If you’re willing to share it, I certainly would like to see it.

Regards,

Here you go

Userlevel 1

I have tried to do a subquery with an advanced group by to sum for the site but can’t get it to work. It either leaves it split by warehouse or it sums it all into 1 value rather than leaving it in the various sites. If you are willing @mdewey that would be great. 

Reply