Solved

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

  • 1 March 2021
  • 8 replies
  • 1485 views

Userlevel 6
Badge +5

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

View original

8 replies

Userlevel 7
Badge +10

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 2
Badge +1

@Gabriel Michaud - Brilliant approach! This saved me a bunch of headaches and workarounds!! GOLD STAR TO YOU!

Userlevel 6
Badge +5

@amit61 
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.

Userlevel 3
Badge

Just sharing another slightly different approach in case it’s useful for people.

This is built against ARTran (so I can see the MAX InvoiceDate of any given Inventory item).

I didn’t use the self table join, so just sharing an alternate approach.

The only table in the Inquiry is ARTran.

I delivered as few columns as possible for testing and used the Aggregate Function (in the column selector) to choose MAX for ARTran.TranDate:

 

Group by ARTran.InventoryID:

 

 

 

Conditions: ARTran.InventoryID is not null:

 

 

And then for convenience, sort by ARTran.TranDate DESC:

 

 

I ran into problems when I tried to then take THIS result set, and only return values where the TranDate is a certain number of days prior to today’s date (similar to the SQL subquery concept), but I think this functionality is meant to be available soon.

 

Just sharing in case this is helpful.

Userlevel 6
Badge +5

@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.

Userlevel 7
Badge +10

@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 6
Badge +5

@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?

Userlevel 4
Badge +1

This was super helpful!!

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