Solved

Can I directly get item's last sells date?

  • 23 December 2020
  • 8 replies
  • 635 views

Userlevel 6
Badge +5

Hello,    

 I need to get the infomation of such items:    

We have qty on hand>0  And We didn’t sell for a long time like 90 days.

    I can get this done by joining INsitestatus and ARTran table.

 

       The key of my solution is that Acumatica will by default get the maximum of trandate after I group by inventoyID

      And I can use a “second filter” on GI results like below to get the information I wanted.

 

It is a solution.
However, it is quite time consuming because ARtran table is quite huge.

Can I replace ARtran with another table (Where I can still get the last sell date infomation and join much less lines) ? 

Or is there a better way to fulfill my need.

 

icon

Best answer by Gabriel Michaud 23 December 2020, 23:02

View original

8 replies

Userlevel 7
Badge +10

Hi @ray20,

 

The INItemSiteHist and INItemSiteHistD tables will give you sales statistics per item/warehouse. The first one is aggregated by the financial period, the second one by day. This will be much faster than looking at individual transactions in ARTran.

 

Make sure to include InventoryID, SubItemID, and SiteID in your join condition for optimal performance.

 

Happy Holidays!

Userlevel 6
Badge +5

@Gabriel Michaud 
Nice, fantastic, it looks like a great way to go.
By the way ,can you also take a look at the below post



it looks like, by using “INItemSiteHistD”, I can use either BegQTy or ENDqty to get the end day inventory balance?
Am I right?

 

Most of all, Thank you and happy holiday

Userlevel 6
Badge +5

@Gabriel Michaud 
Hello, while I finished the replacement, I find a little issue.
Let Me explain,

“INItemSiteHistD” would be logged if 1 item on that day had any transactions like receipt, transfer, adjustment, sales, credit memo, So lt is not only designed for sales history.

 

Supposing ItemA never had any sales in System, then

If left join INsitestatus with ARTran table, the trandate would be NULL, NULL also indicated this item could be no sales for a long time(also it could be a short time, we don’t know)

Now, if left join INsitestatus with INItemSiteHistD table, It would certainly find a match, becausing, INItemSiteHistD is not only including sales data, it is including all qty in data. If INsitestatus  has record, then INItemSiteHistD would also have record.

 

In short, if I replace ARtran with INItemSiteHistD , I would miss the items that had no sales transactions ever. 

Userlevel 7
Badge +10

@ray20 could you export your current Generic Inquiry and attach here? I’ll take a look and provide a solution.

Userlevel 6
Badge +5

@Gabriel Michaud Hello, Merry Christmas. My inquiry is as attached.

Userlevel 7
Badge +10

Hi @ray20,

Please see attached the updated inquiry. I think it does what you’re looking for. The key was to only join the rows of INItemSiteHistDay that have QtySales>0. This needs to be done in the Relations tab rather than the Conditions tab (which was causing items that had never been sold to completely disappear from the results grid).

 

 

Note that I also re-enabled the 2nd condition in the “3-Month” filter you had setup in the inquiry.

Userlevel 6
Badge +5

@Gabriel Michaud 
Super ,Thank you so much.
I didn’t know that I can put  filters in relation tab before. Nice move, I learned, thank you.
Happy New Year, Best wishes.

Userlevel 6
Badge +5

@Gabriel Michaud 
Hello, Just to update,
I accidently find a table “INItemCustSalesStats ”

It is recording the item’s last sales date, last sales price and last sales qty
Note: the price and qty are recorded by base uom.

Reply


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