What is the quickest way to get "end of Day" or "end of month" inventory balance.

  • 30 October 2020
  • 10 replies
  • 3573 views
R
Rank
Userlevel 6
Badge +5

Hello,

     I know we are having “Inventory Balance” report (IN615000) and “Historical Inventory Balance” report (IN616000).

     However, Inventory balance report is something like real-time balance, it displays the balance at the time of the report running. History inventory balance can only display the beginning and ending balance of selected periods.

     If my requirement is to record and display the entire warehouse’s inventory balance of each day for the coming week, like from Monday to Sunday. What is the quickest/simplest way to do it.

       All I can think out of is to set a schedule that run “Inventory Balance” on 00:01 every day, and sent this record out as email. And in the final day, I merge them into one excel.

       This is a workable way, but could it be better?

10 replies

V
Rank
Userlevel 7
Badge +11

Hi Ray,

Can you clarify what should be on final day report in above scenario you explained? Generating Inventory balance report using a schedule should work fine as mentioned by you (daily report requirement), but did not quite get the final day part you mentioned. I would like to know that to see if you can have a custom report scheduled to run weekly that gives the number you are looking for. ​​​​​​ 

So, using one case for daily number and one for weekly is what I thought you could use. 

Regards

Vijay

VijayKumarU
R
Rank
Userlevel 6
Badge +5

Hi Ray,

Can you clarify what should be on final day report in above scenario you explained? Generating Inventory balance report using a schedule should work fine as mentioned by you (daily report requirement), but did not quite get the final day part you mentioned. I would like to know that to see if you can have a custom report scheduled to run weekly that gives the number you are looking for. ​​​​​​ 

So, using one case for daily number and one for weekly is what I thought you could use. 

Regards

Vijay

@vkumar 
Thank you Vijay,

The perfect picture in my mind is I can get something like
 

Inventory ID Monday Tuesday Wensday Thursday Friday Saturday
Item A 100 110 90 80 60 100
Item B 50 50 70 30 50 50
Item C 30 60 40 70 10 30

 

 

The value displays at end of each day’s qty on hand of that line item.

 

What I can think of is I run data into excel on each day, and merge the data at the weekend, then I could get the merged report as I needed.
But is it the quickest way?

V
Rank
Userlevel 7
Badge +11

Hi @ray20 

What will be the parameter you supply when you run the report ( or intend to supply)? Should the report generate week days based on business date of user session ?

 

Regards,

 

VijayKumarU
R
Rank
Userlevel 6
Badge +5

Hi @ray20 

What will be the parameter you supply when you run the report ( or intend to supply)? Should the report generate week days based on business date of user session ?

 

Regards,

 

 

@vkumar Hi, currently, in a single branch and warehouse setting, I can run this “Inventory Balance” report directly on end of each day.
if in a multiple branch and warehouse setting , I shall define the branch and the specific warehouse, that’s all.

The business need behind my question is the business owner would like to know the “end of day” balance. So if “weekdays” can be generated automatically, that would be better, or something like 11/03, 11/04, 11/05, also works fine. no big deal. 
The key of my question is is there a simplest way to log the end of day inventory balance down.

 

 

R
Rank
Userlevel 6
Badge +5

@vkumar 
 

From this post , it looks like I can get end of day balance  by using “INItemSiteHistD”.

I can use either BegQTy or ENDqty to get the end day inventory balance

Jeff96
Rank
Userlevel 6
Badge +4

@vkumar 
 

From this post , it looks like I can get end of day balance  by using “INItemSiteHistD”.

I can use either BegQTy or ENDqty to get the end day inventory balance

Hi, did you ever do this? Can you share an XML?

Jeff G.
R
Rank
Userlevel 6
Badge +5

@vkumar 
 

From this post , it looks like I can get end of day balance  by using “INItemSiteHistD”.

I can use either BegQTy or ENDqty to get the end day inventory balance

Hi, did you ever do this? Can you share an XML?

Sorry, Just notice your post.  What would you like to have?

It would be simple, you can just search “INItemSiteHistD”, for more details, you can find below
Share a little trick to show the begin and end inventory balance of a given period | Community (acumatica.com)

Jeff96
Rank
Userlevel 6
Badge +4

@vkumar 
 

From this post , it looks like I can get end of day balance  by using “INItemSiteHistD”.

I can use either BegQTy or ENDqty to get the end day inventory balance

Hi, did you ever do this? Can you share an XML?

Sorry, Just notice your post.  What would you like to have?

It would be simple, you can just search “INItemSiteHistD”, for more details, you can find below
Share a little trick to show the begin and end inventory balance of a given period | Community (acumatica.com)

I followed that thread and the others you made on this. But from the INItemSiteHistD I’m only able to get dates that had some movement. Dates that were out of stock, or no sales won’t show. 

Jeff G.
R
Rank
Userlevel 6
Badge +5

Yes, you are right, it only loges items with movement in your search period.  
What is your particular need? 
I did not pay special attention to it, because the item we care much about are frequently sales item.
And I suggest you can combine two reports, do a little combination or calculation.
Report 1 is the current item balance report, Report 2 is the INItemSiteHistD report.
For 1 item, If report 2 has data, then use report 2 data. If not, it means no movement in the search period, then you can use current balance instead, which is report 1 data.

Jeff96
Rank
Userlevel 6
Badge +4

Thanks. My need is to know how many days we were out of stock. 

Jeff G.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved