Hello everyone,
We manage inventory on a bin basis. So, it is necessary to manage the change in the daily bin stock quantity.
I created Bin’s on-hand Qty. But, I want to make it so that the quantity by day can be managed like the picture below.
Is there any way to do it? I need the help of BAQ experts.
Are you wanting to see what HAS been in the bins in the past, day by day?
Or are you embarking on an ambitious “Plan for every part” project, where you want to add some UD fields to the DB to say, “this is what you people SHOULD be stocking each day”?
I see dates in the past in your mockup, so I assume it’s the first thing I said.
That, wow, would be quite the BAQ. I think you’d have to concoct a subquery on PartTran to backtrack into the running total for a part-bin combo and then pivot off of it.
And realize that there will be scenarios where the PartBin record does not exist today, but it would have yesterday, so you can’t do a left join on PartBin exclusively.
You might just try to run a recurring BAQ nightly of the PartBin on-hands and dump it to a UD table and then build off of that.
This is quickly getting over my head, but I thought I’d try to help the conversation.
Yes. I just want to see how much stock was in the bin by date in the past (by part).
From what you said, it seems like a fairly difficult function for me as a beginner in BAQ.
@pilio.lee
try this BAQ, will give you what you want but per entered parameter day, see if you can modify it to a pivot one instead
That’s a good way to look at it. If @pilio.lee only needs the last 5 days or a specific day, that seems doable (speaking from my own moderate skill level).
I needed a specific date and your sharing has been a big help to me.
That’s a good way.
I’m sorry to bother you, but may I ask you two questions?
if you want it to be Bin specific, then yes you need to add this to each one of the three subqueries as a field to group in by, then added it as a dynamic filter same as the cutoff date, (i.e. remove it from the top level), however this will only count the stock within each Bin not as total, e.g. you could have zero on Bin-1 but 100-off in Bin-2, thereby your BAQ want show it when Bin-1 selected.
What is the work you said “added it as a dynamic filter same as the cutoff date(i.e. remove it from the top level)”?
I followed your advice. And one more question arose.
I want to add Warehouse/Bin field in Query Results.
What should I do?
sorry to bother you
no problem at all mate, very easy to do, just add it on the top level query from any subquery, all of them have them
Thank you very much for your continued help.
As per your advice, the fields are complete, but the data doesn’t match. The result is an entirely different number.
I don’t know what’s wrong. I have attached my BAQ screen, can you please check if there is any problem?
Yes. I added these two new criteria to all 3 subqueries.
it is working in my environment, but without an example of the wrong data it is hard to determine, and to do this investigation select a part that you know it goes to different bins and calculate the running total on part transactions history up to specific date, specific bin, specific warehouse, then compare to be able to say
are you running it for multi company ? if yes, again you need to add it the same way as other parameters
I don’t use multiple company. Some parts have matching quantities.
I’ll analyze the matching part again.
