Solved

How to get the last sales price and qty using GI?

  • 1 March 2021
  • 5 replies
  • 129 views

Userlevel 5
Badge +2

Hello, 

         I am trying to write a GI to get the customer’s sold since information like the below screen.

 

         You can find my share in the below post:

 

    My solution almost works fine, but I find it difficult to get the “last sale qty” and “last sale price” by item using GI.

     The last sales date can be got because the default aggregation is MAX, the max date is exactly the last date.

      However, the max(orderqty) is not the last order qty, and also the max(unit price) is not the last unit price.

     so currently, I am using the AVG unit price and Sum Order qty as a workaround, and it can be accepted by user.

     But still, I am eager to learn how can I get the data using a single GI?
   Thank you.

 

icon

Best answer by Gabriel Michaud 1 March 2021, 16:32

Hi @ray20 

I’ve been faced with this problem more than once. It is easy enough to represent in pure SQL, but from a Generic Inquiry you have a few limitations to work around. I discovered a solution last year which involves doing a self-join (SOLine LEFT JOIN SOLine SOLinePrev), then only retaining the most recent row.

Your relations will look like that:

In the conditions tab, you need to add a condition so that only rows where nothing matches in SOLinePrev are returned (SOLinePrev.OrderNbr Is Empty does the job). You can also filter by order type if you only care about specific order types, like SO:

The sample inquiry is attached.

View original

5 replies

Userlevel 6
Badge +6

Hi @ray20 

I’ve been faced with this problem more than once. It is easy enough to represent in pure SQL, but from a Generic Inquiry you have a few limitations to work around. I discovered a solution last year which involves doing a self-join (SOLine LEFT JOIN SOLine SOLinePrev), then only retaining the most recent row.

Your relations will look like that:

In the conditions tab, you need to add a condition so that only rows where nothing matches in SOLinePrev are returned (SOLinePrev.OrderNbr Is Empty does the job). You can also filter by order type if you only care about specific order types, like SO:

The sample inquiry is attached.

Userlevel 5
Badge +2

@Gabriel Michaud 
   Thank you for sharing this genius idea, it is really smart.

I have only 1 little question, for the join relations below

 

If the orderNbr is not purely numbers, like with prefix ,  eg. so-123, so-124, so-999, will it still work?
or I have to use orderdate instead?

Badge

This was super helpful!!

Userlevel 6
Badge +6

@ray20 the ordering is done by SQL, and as long as you’re using a fixed-length (ex: SO-000123, SO-000009, SO-000934) numbering (this is what you get with the standard numbering sequences) it will work fine… If you had manual numbering with variable length numbers like SO9 and SO123 this wouldn’t work, because SO9 is greater than SO123...

Userlevel 5
Badge +2

@Gabriel Michaud @stacy16 

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 — 2020  Acumatica, Inc. All rights reserved