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.
Page 1 / 1
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!
@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
@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 INItemSiteHistDtable, 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.
@ray20 could you export your current Generic Inquiry and attach here? I’ll take a look and provide a solution.
@Gabriel Michaud Hello, Merry Christmas. My inquiry is as attached.
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.
@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.
@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.